MSN MoneyCentral IQY Web Query #capital #finance


#msn finance

#

MSN MoneyCentral IQY Web Query

I happened to notice that the MSN MoneyCentral Investor Stock Quotes IQY file did not install automatically with Excel 2013. I m not sure if this just occurred on my system, or, if this is by design. In any event, I d like to talk briefly about what iqy files are, and then, provide a download link to them in case you are running Excel 2013 and need to get a copy.

Excel does an amazing job of importing data from a wide variety of data sources. I guess I first began using this feature back in about 1997 or so to retrieve data from a database. I thought the external data feature was just great, and it saved me a ton of time. Over the years, it has been fun to see how Microsoft has continued to invest in this feature, and has greatly expanded the types of data sources from which Excel can retrieve data.

One of the most fun external data sources is the web. Excel can pull data from the web in a variety of different ways. You can use Excel s built-in web-browser, which is launched when you click the Data From Web command icon. You can also pull data from the web through .iqy web query files, and, with the rollout of Excel 2013, through formulas such as WEBSERVICE and FILTERXML.

The IQY files are web query files, and they contain specific settings needed by Excel to retrieve data from a specific web service. The MSN MoneyCentral Stock Quote IQY file allows you to retrieve stock quotes right into your worksheet, and, you can provide one or many ticker symbols in a cell, as shown below.

Once this is set up, you can update the quotes by clicking the refresh button in the Data ribbon.

This is a totally cool feature, and, when I installed Excel 2013, I noticed that the needed .iqy files were gone! So, I copied them from my laptop, and have attached them to this blog post in case you also find them missing one day.

To use them, simply click Data Existing Connections, and in the Existing Connections dialog box shown below, use the Browse for More button to browse to the iqy files.

By default, they are stored in the My Documents\My Data Sources folder, shown below.

The iqy files are simply text files saved with an .iqy file extension. For example, here are the contents of the MSN MoneyCentral Investor Stock Quote iqy file:

If you want to download the three MSN MoneyCentral iqy files that were provided with previous versions of Excel, here they are, all zipped up for you:

Well, hope this helps!

DECEMBER 2014 UPDATE

Microsoft updated the URL used by the original MSN MoneyCentral Investor Stock quotes IQY file, and it now redirects to MSN. This update has effectively broken this built-in capability of Excel. We are hoping that the original URL is restored soon, but until then, I have prepared a replacement IQY file that uses Yahoo Finance instead.

You can create your own IQY file, or, download the attached below. Typically, IQY files are stored in My Documents\My Data Sources.

Download ZIP. (extract the iqy file from the zip file and save to My Documents\My Data Sources)

Download TXT. (download and change the file extension from .txt to .iqy and save to My Documents\My Data Sources)

If you want to create an IQY file on your own, simply open Notepad and enter the text below. Save the file as an IQY file (be sure to change the file extension from .txt to .iqy)

Alternatively, if you wanted to return all tables, you could use:

Additionally, you can try the more flexible Yahoo Finance API URL. Modify the URL in the IQY file to:

Then, you can pick different field (f) codes. The URL above retrieves symbol (s), ask (b2), bid (b3), 52 week low (j), 52 week high (k).
If you wanted the name, you would add n to the f string (f=sb2b3jkn).
If you also wanted the previous close, you would add p to the f string (f=sb2b3jknp).
Use the f string to identify which fields you want returned.
Here is a link that has the f codes:
http://www.jarloo.com/yahoo_finance/

If you have Excel 2013, you can also use the new WEBSERVICE function.

For example, if you stored the stock symbol in A1, you could write the following formula in cell B1 to return the Last Trade (l1):

If you wanted the Previous Close, you would use the following formula:

JANUARY 2015 UPDATE

And we are back! It appears the MSN MoneyCentral Stock Quotes functionality has been restored, and the built-in IQY file is working once again.

As a note, the original URL in the IQY file is now set up as a redirect. That means that any queries are automatically redirected to a new URL.

The original IQY URL was:

This now redirects to:

If you wanted to update the IQY file to use the new source directly and bypass the redirect, you can update the IQY file (using Notepad) and replace the original URL with the new one.

Here is a text version of the updated IQY file.. Download and change the extension from .txt to .iqy if desired. Please note that using this file isn t necessary since the original URL redirects just fine, and I m just providing it in case you want to bypass the redirect.

Share this:

May 20, 2015 at 6:59 am Reply

September 4, 2015 at 1:41 am Reply

Great site Jeff many thanks for setting up. I hope I haven t missed something along the way but when I try to Get External Data from Existing connections the MSN MoneyCentral Investor Currency Rates I have downloaded from you link above dosen t give me a table of exchange rates. Instead I get what looks like text dump of the entire MSN Money homepage. Can you describe how I can re-direct the query so I get exchange rates in a table format I can then link to my working files using lookups? Sean.

  1. jefflenning Post author

September 17, 2015 at 11:07 am Reply

That is interesting, and I hadn t noticed it before. Essentially, in Dec 2014 Microsoft made a change to the URL that was providing Excel with the data. This change disrupted many workbooks, and in January 2015 Microsoft restored the functionality. At least I thought. I can now see that they restored the functionality of the Investor Stock Quotes query, but, apparently not for the Currency Rates query. Unfortunately, I m unaware of a workaround at this time but if you find one I d love if you would share by posting a comment thanks!

September 8, 2015 at 7:17 am Reply

While using the WEBSERVICE() function to pull stock quotes from Yahoo finance, the function returns a string which I have been unable to convert to a number. I tried using the VALUE() and TEXT() function with no success. Are you aware of any other workaround to convert the string to a number?

  1. jefflenning Post author

September 17, 2015 at 10:48 am Reply

Justin,
Ah yes you can convert the text string returned from the WEBSERVICE function into a number by nesting the CLEAN and VALUE functions together. See, there is some extra stuff returned which is why using VALUE alone doesn t work. Something like this should do the trick: =VALUE(CLEAN(WEBSERVICE( ))).
Hope it helps!
Thanks
Jeff

September 23, 2015 at 12:29 am Reply

You sir, are a legend. Take a bow

  1. jefflenning Post author

September 24, 2015 at 12:00 pm Reply

March 8, 2016 at 1:52 pm Reply

Jeff, thanks a lot for sharing! This is just amazing
Thumbs up
Luca


Leave a Comment

Your email address will not be published. Required fields are marked *