Note: see this post for instructions on using the new stock quotes system in Excel.
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:
WEB 1 http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=["QUOTE","Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas."]
Selection=EntirePage Formatting=All PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False
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)
WEB 1 http://finance.yahoo.com/quotes/["QUOTE","Enter stock, fund or other symbols separated by commas."] Selection=2 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False
Alternatively, if you wanted to return all tables, you could use:
WEB 1 http://finance.yahoo.com/quotes/["QUOTE","Enter stock, fund or other symbols separated by commas."] Selection=AllTables Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False
Yahoo Finance API
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:
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.
AUGUST 2017 UPDATE
It seems as if Microsoft is working on an alternative way to retrieve stock quotes. The legacy iqy files do not appear to work any longer. I’m keeping my fingers crossed for an update to this terrific capability of Excel! I’ll post back when I know more 🙂
NOVEMBER 2018 UPDATE
Well, it is here … yay! The new system for retrieving stock quotes relies on the Stocks data type. I wrote a blog post about it here. It uses a new Stocks data type to enable you to quickly pull stock quotes, but, much more. You can retrieve tons of related company data, including employees, CEO, ticker, prior close, and much more. To read more, check out the Stock Data Type – Stock Quotes and More post.