MSN MoneyCentral IQY Web Query

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.

MSN MoneyCentral Investor Stock Quote

 

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.

Existing Connections

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

My Data Sources

 

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:

MSNMoneyCentralIQY

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)

YahooFinance

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

YahooFinance

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:

http://finance.yahoo.com/d/quotes.csv?s=["QUOTE","Enter stocks"]&f=sb2b3jk

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:

Using the Yahoo Finance API for CSV

WEBSERVICE Function

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):

=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1")

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

=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=p")

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:

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=

This now redirects to:

http://www.msn.com/en-us/money/quoteslookup?SYMBOL=

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.

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

106 Comments

  1. Zongyong Zou on May 20, 2013 at 9:30 am

    the MSN MoenyCentral iqy files are what I looked for, thanks

  2. Andy on February 27, 2014 at 4:27 pm

    Thank you so much! I was looking for this for awhile

  3. Jon Brophy on April 29, 2014 at 5:18 am

    I try to put the files into a spreadsheet and it is all jumbled with characters with nothing working. Tips?

    • jefflenning on May 2, 2014 at 6:51 am

      Jon,
      The IQY files are web query files, so instead of opening them inside of Excel what you want to do is:
      -unzip the zip file
      -save the iqy files to your computer, probably to the default query location: My Documents\My Data Sources
      -then, access them by going opening a blank workbook in Excel and going to: Data > Existing Connections
      Hope this is the information you were seeking…hit me back if needed…I’m happy to help!
      Thanks,
      Jeff

  4. Laura on June 5, 2014 at 10:04 pm

    Thank you, needed these 🙂

  5. chris on October 8, 2014 at 9:38 am

    Is there a way to get historical data based on an inputed date somewhere?

    • jefflenning on October 9, 2014 at 11:40 am

      Chris,
      Although I’m unaware of a built-in MSN MoneyCentral option to retrieve historical data, one alternative is to use Excel’s web-browser (Data > From Web) to retrieve historical quotes from say Yahoo Finance or Google Finance. This will retrieve many rows and then you could use a VLOOKUP function to return a specific date’s closing price into a cell. For example, head to Data > From Web and then paste in the link for Microsoft:
      http://finance.yahoo.com/q/hp?s=MSFT+Historical+Prices

      You can also navigate there within Excel’s web browser as needed.

      Hope it helps!

      Thanks
      Jeff

  6. Steven on November 20, 2014 at 11:21 am

    Thanks, this works well for stocks on US exchanges. How do you code the symbols for non-US exchanges, say Toronto?

    • jefflenning on December 22, 2014 at 4:20 pm

      Steve,

      Hmmm…I’m not sure about that. If the built-in MSN query doesn’t support other exchanges, perhaps use Excel’s built-in web browser to retrieve them from another source such as Google finance. Use the Data > From Web command icon. Here is an article that can get you started:

      http://www.excel-university.com/articles/cal-cpa/excel-on-the-web/

      Thanks
      Jeff

      • jefflenning on December 30, 2014 at 2:58 pm

        Steve,
        I believe the yahoo finance api works with other exchanges. Here is a link with all of the exchange codes…scroll down the page a bit.

        http://www.jarloo.com/yahoo_finance/

        Hope it helps!

        Thanks
        Jeff

  7. Michael on December 22, 2014 at 12:29 pm

    I have been using the wonderful built in .iqy feature with Excel 2010 (which I still run) for several years now. It is a really helpful way to calculate portfolio construction.

    However, today the system seems to have broken. Without making any changes to the query it no longer is working for me. Instead the sheet that previously held the stock data is now a text representation of the MSN money homepage. Do you know how to fix it? It seem Microsoft changed something, perhaps with MSN money, that is preventing the iqy.

    Is there a simple way to pull the data from Google Finance instead?

    • jefflenning on December 22, 2014 at 3:52 pm

      Michael – I just ran the MSN query…and it broke for me as well. It appears that Microsoft changed the query URL so it now redirects to the msn page. I’m hoping they will restore the original URL so that it will soon function as it did before.

      Until then, we have a couple of options. We can use Excel’s built-in web browser to grab the quotes from another service, such as Google finance. To do this, sse the Data > From Web command icon. More info in my CalCPA article: http://www.excel-university.com/articles/cal-cpa/excel-on-the-web/

      Another option that allows us to continue to specify the tickers is to create a new iqy file that uses a different provider, such as Yahoo finance. For example, if you open this page (http://finance.yahoo.com/quotes/GE,YHOO,MSFT,GM) in a browser, you can see it returns the desired quotes. This page supports multiple tickers separated by a comma. You can create a new IQY file that uses this page pretty easily. Just go to My Documents/My Data Sources and save a copy of the MSN MoneyCentral Investor Stock Quotes.iqy file as a new file such as YahooFinance.iqy. Then, using Notepad, edit the new iqy file to work with Yahoo Finance by changing the original text “moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=” to “finance.yahoo.com/quotes/”. You can also modify the Selection parameter of the iqy file. The MSN setting is Selection=EntirePage, however, you can change this. For example, in our new YahooFinance IQY file we can change Selection=AllTables and that will strip out a bunch of the other page data and links. Or, you can change it to Selection=2 to return the second table which is currently the stock quotes table (but may change in the future if Yahoo changes the page layout).

      The updated IQY file:

      WEB
      1
      http://finance.yahoo.com/quotes/%5B“QUOTE”,”Enter stock, fund or other symbols separated by commas.”]
      Selection=2
      Formatting=All
      PreFormattedTextToColumns=True
      ConsecutiveDelimitersAsOne=True
      SingleBlockTextImport=False

      Then, you can use Data > Existing Connections and then select your newly created iqy file (YahooFinance) and you should be good.

      Hopefully, this will be a short term workaround and Microsoft will restore the original URL http://moneycentral.msn.com/investor/external/excel/quotes.asp to work again.

      Thanks
      Jeff

      • Taylor on December 22, 2014 at 7:05 pm

        I really hope they fix this soon as well. The yahoo work-around does not appear to be very flexible and doesn’t seem to allow as many Stock Tickers to be passed on as a parameter at one time.

        I am so glad I happened to find your comments about this issue. I thought I was going crazy for the past few hours trying to get this to work.

      • Michael on January 14, 2015 at 6:55 pm

        Jeff,

        I just wanted to drop a note to say thanks. Your solution saved me hours of work. I’ve been using the IQY tool in as a reporting and shortcut for years now. It broke just as some major year-end reports were due. (I posted on 12/22 and was leaving town 12/23. Without the reports done I would have had to change holiday plans)

        Thank you for the quick, accurate response. Hours saved, vacation with family happened as planned.

        • jefflenning on January 14, 2015 at 7:22 pm

          Very welcome…glad it worked out! And, thanks for taking the time to comment back 🙂

  8. Dennis on December 22, 2014 at 7:20 pm

    Ditto….let’s hope for the original URL….

  9. Raphael on December 23, 2014 at 5:55 pm

    Hi,

    I just called up an tech support company and after 1.5 hours we finally found this page and am now using the yahoo query link. I should mention I paid $110.00 for information I just couldn’t find. I wish there was an easier way to figure this stuff out. I lost 2 days of not being able to update my stock charts and lost even more! What is the best way for me to become proficient in Excel so if anything awkward like this shows up I can quickly assess what the problem is?

  10. Ron on December 23, 2014 at 7:17 pm

    Jeff
    Just wanted to say thanks for the update regarding the broken MSN query. I was also going crazy the last few days trying to figure out what was wrong. I created a new IQY file using Yahoo Finance per your instructions and it worked perfectly. As Taylor mentioned, it doesn’t allow as many stock symbols. Only 26 as compared to MSN’s 50 or so. One work-around for this is to skip down a few rows past all the news headlines and enter another set of stock symbols, then set up another query from that. Not the best esthetic solution, but I use the quotes in another tab using a vlookup, so I don’t care what it looks like. My only worry is that some future refresh will overwrite my second (or third) set of stock symbols.
    Thanks,
    Ron

    • jefflenning on December 23, 2014 at 8:41 pm

      Nice…thanks for sharing your solution to the ticker limit!

  11. Charlie on December 23, 2014 at 10:26 pm

    I agree. Microsoft needs to fix the link. I don’t want to have to revamp all my excel spreadsheets. Google sheets has a really easy way to obtain stock quotes. Maybe I’ll just make the switch.

    • Mark on December 25, 2014 at 1:39 am

      Hi Charlie,

      I was wondering how you would get Google Sheets working with the stock quotes? I was thinking of using the Yahoo workaround but seems to me that some people aren’t completely happy with that. Could you elaborate?

      Thanks!

  12. wayne on December 24, 2014 at 9:29 am

    Thanks – That worked great. Nice to have a work around

  13. Lisa on December 27, 2014 at 5:43 am

    Jeff – Thanks!!! I can work again.

    I too implemented a workaround for > 24 ticker symbols, similar to Ron’s.

  14. Ron on December 28, 2014 at 7:22 pm

    I’m a different Ron. I too suffered the sudden failure of the MSN MoneyCentral link. I acquired the above .iqy file for Yahoo Finance, made four copies tailored to my portfolio, and wrote the VBA code to make them work in the MSN-based Excel 2013 spreadsheet in which I track my wealth (or lack thereof). It works well, albeit not as effectively as the MSN link. Hopefully MSN will resurrect that link in the near future. How will we know when it’s available?

    • jefflenning on December 29, 2014 at 3:22 pm

      Ron – I’m monitoring the IQY, basically just trying it periodically, so if you opted to receive post comments you should get an email when I post a comment when it is fixed.
      Hoping it is soon!
      Thanks
      Jeff

  15. Emily on December 29, 2014 at 3:18 pm

    Is there a way to get Name and Previous Close?
    Thanks!

    • jefflenning on December 30, 2014 at 2:49 pm

      Emily,

      It appears that the Yahoo finance API offers many options. Here is the link I found that provides the various options:
      http://www.jarloo.com/yahoo_finance/

      I see options for previous close (p) and name (n).

      I hope this helps!

      Thanks
      Jeff

  16. Pam on December 29, 2014 at 9:12 pm

    I appreciate the confirmation that the problem is on MSN’s end not something I did. I’ve used the stock prices to update my Excel spreadsheet for years and this has been quite frustrating. I’m attempting some of your suggestions but so far not making good progress. I’ll keep trying.

  17. Bailey on December 30, 2014 at 2:45 pm

    Thank you very much for the information. The Yahoo work around is helpful. I do a lot with options. The format for options using the old MSN connection was 126.2.WFC7AKC045000 for a Wells Fargo January 2017 Call Option with a 45 strike price. Do you know if the option information can be accessed through the yahoo connection, and if so, what is the format?

    • jefflenning on December 30, 2014 at 2:51 pm

      Bailey,
      It seems like the Yahoo Finance API offers lots of settings that customize the data that is returned.
      I found this link that provides a clean summary of the codes:

      http://www.jarloo.com/yahoo_finance/

      Hope it helps!

      Thanks
      Jeff

  18. Chad Ferrell on December 31, 2014 at 9:06 am

    Thank you, Jeff. This was a real life saver. Looked for several hours for possible fixes for this and couldn’t get any of the other ones to work. This works great!

    • jefflenning on December 31, 2014 at 9:08 am

      Welcome! Glad it helped!
      Thanks
      Jeff

      • jefflenning on January 5, 2015 at 8:49 am

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

        • Brian on January 5, 2015 at 11:37 am

          It doesn’t seem to be working for me. I just tried a refresh and am getting the same “no data returned” error message.

          • jefflenning on January 6, 2015 at 8:26 am

            Brian – I’ve noticed intermittent errors as well. I will try it and it works, and then another refresh doesn’t work. I’ve got to imagine that the service will stabilize shortly.
            Thanks
            Jeff



  19. Mark Sendelbach on January 2, 2015 at 8:40 am

    Jeff,

    Thank you very much for teaching us how to the the .IQY Excel queries to pull down Yahoo Finance data. I thought those queries were only used for MS Money. It works great except for one hurdle I’m trying to overcome. It keeps giving me an error message saying “Make sure the file/path name doesn’t contain more than 218 characters”. But I only have 19 mutual fund symbols. At 5 characters each it doesn’t exceed 218 characters. So I have to create two separate queries.

    Any ideas on how to get around this issue? It’s not a huge problem but it just doesn’t make sense.

    Thanks again for your post. I look forward to visiting your website in the future.

    Sincerely,
    Mark

    • jefflenning on January 5, 2015 at 8:53 am

      I believe that limit includes the full path, including the http://www.yahoo.com, and all of the commas and other characters.
      I think that splitting it into two queries is a good workaround.
      Thanks
      Jeff

  20. Mike Bak on January 5, 2015 at 7:36 am

    Jeff – thanks for the info. Is there anyway to modify the Yahoo query to prevent it from bringing in all the news and headlines detail that appears below the quote information?

    • jefflenning on January 5, 2015 at 8:51 am

      Yep…you can change the line in the IQY file from Selection=EntirePage to Selection=2.

  21. Brian on January 5, 2015 at 7:39 am

    I was finally able to get the Yahoo quotes function to return the last trade quote using the “WEBSERVICE” function, but am unable to figure out how to keep it updated. It doesn’t automatically update the quote and I cannot find a “refresh” function. Even if the workbook is closed and then reopened it does not refresh either. Any suggestions?

    • jefflenning on January 6, 2015 at 8:35 am

      Brian,
      The WEBSERVICE function is non-volatile, meaning the calculation won’t update unless the referenced cell is updated or the entire workbook is recalculated. You can use CTRL+ALT+F9 to recalculate the workbook, and that should refresh the quotes retrieved with the WEBSERVICE function.
      Thanks
      Jeff

  22. George Bradley on January 5, 2015 at 1:49 pm

    Still not working for mutual fund quotes but something is better than nothing.

  23. Bailey on January 6, 2015 at 6:48 am

    Jeff, thanks for the list of codes, I don’t see anything option related, do you know if there is a way to download the option contract information?

    • jefflenning on January 6, 2015 at 8:19 am

      Bailey – I’m not sure about that…but if you figure it out, please post back!

  24. Bailey on January 6, 2015 at 7:39 am

    My connections still aren’t working……what am I missing? I use the connections under data to pull down quotes and other information on a separate sheet and then pull over the data I want to use to my “primary” worksheet. The connections under data are still not pulling the correct information over from MSN.

    • jefflenning on January 6, 2015 at 8:07 am

      To see what is happening behind the scenes, I would try to pull up the page in the browser. Here’s how. Open your web browser, and then enter the original URL from the IQY file with a symbol, such as:
      http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=GOOG

      If your web browser displays the desired result, then, it should work in Excel as well. If not, your browser should display an informative error message which you can use to see if it is something on the web server side.

      Also, please note that Microsoft didn’t actually restore the original IQY URL, rather, it set up a redirect.
      The original IQY URL redirects to:
      http://www.msn.com/en-us/money/quoteslookup

      So, looking up GOOG in Excel now actually uses the following:
      http://www.msn.com/en-us/money/quoteslookup?SYMBOL=GOOG

      I’ll post up a new IQY file that uses the new URL. Using the new file is not necessary because the original URL redirects, but, it the new file goes directly to the new source.

  25. Rich B on January 6, 2015 at 1:47 pm

    Hi Jeff,
    Thanks for the update MSN link. Seems to work well for equities, however, the link doesn’t seem to work for all ETF’s. Have you found this to be the case?
    I tried the updated .iqy file from within Excel and it won’t return a value for the ETF. Subsequently, I copied and pasted the URL into a browser and manually typed in the ETF ticker and again, no value returned. I checked my typing and used the URL with a standard ticker “GE” and a value was returned.
    Any thoughts?
    Thanks,
    Rich

    • Kurt LeBlanc on June 30, 2016 at 2:51 pm

      This works for me every time. What ticker are you trying?

  26. JBAswoll on January 6, 2015 at 6:15 pm

    Jeff,
    I would also like to thank you for all your efforts and quick responses. When MSN went down I had to resort to Google Sheets on Google Drive until I found your post. Thanks a bunch! Since MSN is back up, I now keep my Yahoo queries as a backup in the same spreadsheet, in case MSN decides to quit working again. I track over 400 stocks so I absolutely need to have queries that can retrieve all that information in a flash.

    I also tried your suggestion with the webpage, http://www.jarloo.com/yahoo_finance/ and it was also very useful since you can choose specific data that is in your interest. Once the data is in .csv format I was able to write some macros to automatically retrieve & copy/paste the data to my stock spreadsheet.

    If you have any books with Microsoft Excel on MAC, I would be very interested. I have to run VMware for Windows every time I choose to use my stock spreadsheet. I do have Office 2008 on MAC but it’s useless for me b/c it doesn’t run macros. However, I’ve read Office 2011 is capable of it but excel on the mac is a lot different and harder to navigate in my option.
    Thanks again Jeff

    • jefflenning on January 7, 2015 at 8:26 am

      Thanks…glad it helped!
      Regarding Excel for Mac, I’m sincerely hoping that Microsoft brings the two versions together, so that Excel for Mac provides the same experience, capabilities, and interface as Excel for Windows. I too own a Mac laptop, but, have loaded Windows in a VM in order to run Excel for Windows. Keeping my fingers crossed and hoping for a more unified product across platforms!
      Thanks
      Jeff

  27. maggy on January 7, 2015 at 12:51 am

    it seems the total amount of quotes is restricted now ?

    • jefflenning on January 7, 2015 at 8:29 am

      I’m aware that the Yahoo URL has a limit to the number of symbols. Are you hitting a limit using the Yahoo URL or the MSN URL?
      Thanks
      Jeff

  28. rijk on January 7, 2015 at 4:45 am

    updated the msn iqy file with the new url

    works fine in my web browser

    still getting an error message in excel, looks like excel is still going to the old url

    please post if there are updates

    regards
    rijk

    • jefflenning on January 7, 2015 at 8:28 am

      After you updated the IQY file, did you create a new worksheet query? Or, refresh an existing query in an existing workbook? Try to create a new worksheet query after updating the IQY file and I think you should be good.
      Post back with results!
      Thanks
      Jeff

  29. Joe DeMeo on January 7, 2015 at 8:08 am

    Hi Jeff,
    Thanks for all this info. I tried your January 2015 update but it doesn’t work or sometimes work. I use excel 2003 but have never had a problem with stock updates/look ups. Could you recheck your file. Yahoo works perfect but prefer to get MSN look up to work properly, consistently, and all the time.

    Joe

    • jefflenning on January 7, 2015 at 8:31 am

      Joe,
      I too have noticed that the new MSN URL works intermittently. I’m hoping that the service will stabilize shortly and work consistently once again.
      Thanks
      Jeff

  30. maggy on January 7, 2015 at 11:31 pm

    What about exchange rates ?

    before i just could put “EURUSD” as a stock symbol to get the rates , no it doesn’t work anymore

    • jefflenning on January 8, 2015 at 8:24 am

      Maggy,
      We are still waiting for Microsoft to redirect the original MSN MoneyCentral Investor Currency Rates URL.
      The original IQY URL is:
      http://moneycentral.msn.com/investor/external/excel/rates.asp
      This currently redirects to:
      http://www.msn.com/en-us/money

      But, I’m hoping that they update the redirect to restore the original functionality as they did with the stock quotes query.

      Hoping it is soon!

      Thanks
      Jeff

      • Maggy on January 10, 2015 at 1:42 am

        I’m hoping it with you
        many thanks for l this site

        Maggy

  31. rijk on January 10, 2015 at 1:46 am

    “Try to create a new worksheet query after updating the IQY file and I think you should be good.”

    trying this once a day, results still the same

    when entering just one single stock symbol to look up, the iqy query works

    when entering more than one stock symbol, data not available error message appears

    do you not have the same experience?

    regards
    rijk

    • jefflenning on January 15, 2015 at 8:38 am

      When I store all of the symbols in one cell, and separate the symbols with commas, it works as expected.
      For example, I just now ran it with msft,goog,rht in a cell, and the query returned info for all three stocks.
      Thanks
      Jeff

  32. Gary Douglas on January 10, 2015 at 7:28 am

    Hi Jeff –

    What a terrific service you are providing for us. Thank you very much. Clearly many people depend upon these data connections to update their investment tracking worksheets. I think it’s inconsiderate of Microsoft and or MSN to alter their format and not change it back once they realize the firestorm unleashed.

    I tried the WEBSERVICE route on my sheet, and I found it worked really well once I found a list of the available symbols for this query. It works terrific, but it seems that the results are coming back as plain text and the spreadsheet doesn’t recognize them as numbers. I get results I can read, but I can’t do anything with them except look at them. I wanted to use the returns to populate throughout the workbook for my various holdings.

    I’m not a programmer so my knowledge of these things is pretty limited, but it seems to me there is some way to format the returned values as currency rather than text. This might reside is some WEBSERVICE XML file, but again I would have no idea.

    Am I one the right track and could you provide any guidance? I’d sure appreciate it. I’m woefully behind on my updates.

    Thanks again.

    Gary

    • jefflenning on January 11, 2015 at 1:19 pm

      Gary, please try to use the VALUE function to convert the returned text string to a number. For example =VALUE(WEBSERVICE(…))
      That should do the trick.
      Thanks
      Jeff

  33. maggy on January 12, 2015 at 2:01 am

    Hello Jeff,

    I used your iqy file and i get the stock quotes ( except the currency ones 🙂 ) as shown in the first picture on this site . I can’t see on your picture what you have under the “% change” column , but i get very big numbers there like 174% or -1250% that aren’t right …
    do you get that too ?

    thanks

    Maggy

    • Kurt LeBlanc on June 30, 2016 at 3:03 pm

      You have the correct numbers. The formatting got mixed up from the web to Excel. The percent change is originally given in whole numbers with a percent sign and Excel converts this to a percent and adds its own percent sign.

  34. Randy Hudgens on January 14, 2015 at 5:28 pm

    Previously, the MSN quotes would work if a range of cells was specified. Now I have only been able to get it to work with a single cell. For multiple quotes, the cell must contain each symbol separated by commas with no spaces. Using a range was convenient. Converting a range of values to a single string in the correct format is quite inconvenient. Any easy workarounds?

    • jefflenning on January 15, 2015 at 8:36 am

      Randy,
      That is interesting, I didn’t know that you used to be able to provide a range of cells for the symbols. Anyhow, I tested the new URL here, and I agree that it does not support a parameter with a range of multiple cells. One possible approach would be to write a formula that combines all of the tickers you have with the concatenation operator (&). Assuming your first symbol was in A1, you would write a formula in B1 that is: =A1. Then, the next symbol is in A2, and your formula in B2 would be: =B1&”,”&A2. Then, you can fill this formula down for as many rows as you have tickers. The final formula row can be used as the parameter cell for the query. I just tested it here, and it worked just fine.
      Hope it helps!
      Thanks
      Jeff

  35. J.J. Trost on January 18, 2015 at 9:22 am

    I am still having difficulties on this from my Mac. It appears that the website is now treating the query run from excel 2011 as a mobile device and pushing back data that is unreadable.

    Could be I’m missing something here but the link works from my browser but not from excel. Any ideas?

    • jefflenning on January 20, 2015 at 3:57 pm

      J.J.,
      Ah…unfortunately, I don’t have the Mac version…so I don’t have a good way to troubleshoot this issue for you. I’m so sorry…some day, I’ll need to buy a copy of Excel for Mac!
      Hopefully, another reader may have a suggestion…I’ll watch for any related comments and approve.
      Thanks
      Jeff

  36. Joe on January 19, 2015 at 2:19 pm

    It works as long as I separate each stock symbol with a comma or put a string of comma separated symbols in a single cell. I used to submit a column of stock symbols. This no longer works. Any thoughts? Thanks. And thanks for all the work and comments. – Joe

    • jefflenning on January 19, 2015 at 2:23 pm

      Joe,
      The new URL, at least for now, doesn’t seem to support a range of multiple cells. One possible approach would be to write a formula that combines all of the tickers you have with the concatenation operator (&). Assuming your first symbol was in A1, you would write a formula in B1 that is: =A1. Then, the next symbol is in A2, and your formula in B2 would be: =B1&”,”&A2. Then, you can fill this formula down for as many rows as you have tickers. The final formula row can be used as the parameter cell for the query.
      Hope it helps!
      Thanks
      Jeff

  37. maggy on January 20, 2015 at 9:41 am

    Hello Jeff, this is how i used to do it :
    be:ags,nl:apam,fr:CS,be:bekb,be:CMB,be:ezbg,be:eurn,be:eurn,be:evs,be:exm,FSLR,nl:glpg,goog,be:gblb,Hmy,nl:ibzl,be:kbc,BE:nyr,BE:nyr,BE:nyr,BE:ONTEX,be:opti,nl:rdsa,be:rtl,fr:sgo,fr:sgo,nl:SBMO,be:tig,be:tig,

    but now it gives only 16 stock prices . sometimes, if we’re Lucky, it gives all the stockprices …

    • jefflenning on January 20, 2015 at 4:03 pm

      Maggy,

      The new URL seems to not be quite as functional as the old one, including the number of quotes it can return. We are hoping that the original functionality is restored in full because it was such a capable and cool feature!

      Thanks
      Jeff

  38. SomeGuy on January 20, 2015 at 10:33 pm

    Hello Jeff,

    Its great that you are so diligently trying to help in this situation.
    I need to import the fx quotes into Excel and with the old link, it work nicely
    and I could just update the table with the new fx rates from the server.
    It was one table with all relevant currency pairs with two columns “in USD” and “per USD”.
    My excel calculations are drawing from those cells to make other calculations.
    Now, they have updated the link and its giving me a bunch of garbage news into this table.
    I have tried to use your suggestion to update the .iqy file which I managed to do.
    But now, there is a prompt asking me to input the symbols. That alone I don’t want,
    instead I want the table to be updated with the latest rates.
    Then, I type USD,EUR and it gives me something on USD only.
    Yahoo finance also doesn’t work.
    What can I do?

    Thank you for your help. It is really a frustrating and unnecessary excercise
    that Microsoft is putting on its users here.

    • jefflenning on January 21, 2015 at 2:11 pm

      To address the issue of the prompt asking for the symbols, you can right-click any cell returned by the query and select the Parameters menu item. This will allow you to control where Excel should obtain the symbols. In the resulting Parameters dialog box, you can instruct Excel to get the value from a specific cell.
      Hope this helps!
      Thanks
      Jeff

      • SomeGuy on January 22, 2015 at 10:01 pm

        Hello Jeff,

        I am looking to draw fx rates of currency pairs into the excel sheet.
        I have seen the Symbols in the context menu from the time when my file
        was still working and it is something like USDEUR or USDSGD
        If I type this into the query prompt (or select a range where this is in) it only
        returns a empty sheet saying Stock quotes at the top. I seem to be trying
        to draw from the wrong source. Do you know how to modify the .iqy table
        so that it does a lookup in the fx rates?
        Any other suggestions?

        Thank you!

  39. Back-Broker on January 24, 2015 at 4:38 am

    Hello to everyone from Europe,

    Yes, we also have these Problems over here^^ Jeff, also from my site a big “thank you” for your work, althrough it did work out for me so far 🙁 I am not certain, where exactly the problem is, but as it seems it is a mix that a) the number of symbols for the webask may not be anymore as large as before and b) it makes more problems with International Securities Identification Numbers (ISIN)… They are not just the tool I used so far basically, I also didn’t read anything from anybody who mentioned them as well 🙁
    Can it be that the “new” function are optimized for the US-World? :-/ ISIN’s are here much more common that Ticker-Symbols… and further, it is necessary for me to use ISIN, so that I am able to see also US-Stocks in EUR-Currency. That was before easily possible just by typing a “DE:” before the ISIN (for example DE:US02209S1033 for MO in EUR).
    Beside that, you had before access to much more stock-quotes, also from European Small Caps and Fonds, which seems as a consequence to be reduced as well 🙁

    Best regards

  40. GN on February 6, 2015 at 1:54 pm

    Hi Jeff,

    The symbol for Facebook is FB. However, if FB is used as symbol, MSN returned something different. Another example is STB, I would like to get Student Transportation Inc listed in US. However, MSN always returned a foreign company called Storebrand ASA as result.

    Please advice.

    Many thanks in advance!

    • Kurt LeBlanc on July 1, 2016 at 4:22 am

      Mine works every time. I’m not sure. Maybe you just don’t have the updated file?

  41. Pedro Reis on February 9, 2015 at 7:42 am

    Hi. I have a question. When returning the data from any stock, is it possible to return de date of that quote, using MSN?
    My excel file depends on getting the date also.
    Thank you.

    • jefflenning on April 13, 2015 at 2:34 pm

      Pedro,
      I’m not sure how to do that with MSN, but, with the Yahoo query you can. Just use the d2 variable as described here:
      http://www.jarloo.com/yahoo_finance/
      Thanks
      Jeff

  42. John Rico on March 8, 2015 at 2:53 am

    Hi Jeff,

    thank you for your work. It was actually the first “hot trail” I found, in ref. to the problem MSN gave us all-_-

    I am writing you from Europe (Germany) and I have still the Problem that the “new” Excel-Formular or Web-Query doesn’t work anymore, if I want US-Stockfiles for example in EUR instead of $. Last year it worked well.
    Back then, it was just necessary to put a “DE:” in front of the ISIN, to get the US-Stock in EUR currency (for example: DE:US88579Y1010 for 3M in EUR… unfortunatly there is no difference, if I just type “US88579Y1010” in…). Funnily enough it works still for some ISIN, but not for all -_- (for FB, AT&T, but also Barrick (Canadian) it’s still working. And for some asian (Panasonic, Sony) as well). I can also add now the Tickersymbol as well (as far as there is an “european” Symbol avaiable, for example JNJ.DE for Johnson&Johnson in EUR), but same issue: I can’t see a pattern, which will work and which won’t :/

    Another question: I think the Query form Yahoo is an useful addition (better do get datas from 2 different sources, then just from one), but is there an option, that it takes more than the “standard”-datas, which means to add Dividend-Yld, Ex-Div-Date, Div-Pay-Date, PER/PPR?

    Thank you in advance

    Best regards from Germany

    JR

    • jefflenning on April 13, 2015 at 2:22 pm

      Howdy John!

      Hmmmm….I’m not sure. I approved your comment hoping another reader may know the answer and post a response….thanks!

      Thanks
      Jeff

  43. Rak on April 2, 2015 at 3:16 pm

    I was missing these IQY files. I’ve searched and searched and could not find the answer I was looking for then I came upon your site and this page. It solved my entire problem and my frustration.

    Thank you, thank you, thank you.

    • jefflenning on April 2, 2015 at 8:19 pm

      Welcome 🙂

  44. Guillaume on May 19, 2015 at 2:56 am

    @ John Rico
    Thanks for the tip!
    It works for France, just place FR: before the ISIN code!

    • jefflenning on May 20, 2015 at 6:59 am

      🙂

  45. Sean Cody on September 4, 2015 at 1:41 am

    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.

    • jefflenning on September 17, 2015 at 11:07 am

      Sean,

      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!

      Thanks
      Jeff

  46. Justin on September 8, 2015 at 7:17 am

    Jeff,

    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?

    Thanks,
    Justin

    • jefflenning on September 17, 2015 at 10:48 am

      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

  47. David on September 23, 2015 at 12:29 am

    You sir, are a legend. Take a bow

    • jefflenning on September 24, 2015 at 12:00 pm

      🙂

  48. Luca on March 8, 2016 at 1:52 pm

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

  49. Jaydog on April 17, 2017 at 11:25 am

    I can’t get it to work on the single letter tickers. F, T, V, etc… it works great on the others, but for some reason when it is trying to look up a single letter ticker I get different results. F = Fiore, Fidelit, etc… but not Ford…

    Anyone else come across this error that knows how to help, I would greatly appreciate it.

  50. qupola on April 30, 2017 at 6:02 am

    Jaydog, the reporting of some the single letter tickers on US exchanges has indeed been problematic of late. I reported it the other day via the feedback link at the bottom of the msn.com/en/money web page. Might be a crack in the data feed from Morningstar that MSN uses as well. Yesterday my single ticker iqy stock queries started pulling up the stocks I expected to see, but searches of the same ticker on the MSN site were still yielding unexpected results.

  51. qupola on April 30, 2017 at 6:27 am

    Also, a heads up to the authors of the above article in regard to yahoo.

    Yahoo finance recently changed their site layout, which now seems to have broken the Excel iqy web query feature. I’m now trying out a comma delimited file approach of grabbing stock data from Yahoo finance via an Excel text connection. If one finds here is exploring other means to connect, they might find a search of their forums on the topic of connecting with Excel worthwhile.

    How about a Google finance IQY Web Query approach? Is that a viable alternative to Yahoo (whose days seem numbered) and MSN (whose data feeds don’t work quite as well)?

  52. qupola on April 30, 2017 at 8:03 am

    Update for Jaydog. My MSN money based spreadsheet with the iqy method described in this article reported quotes for single letter tickers like F as Ford and T for AT&T correctly for awhile yesterday evening, But this morning the problem is back. I’ve been getting the same results when doing a quote search at msn.com/en-us/money/markets for some of the single ticker symbols on US based exchanges F (Ford) is currently pointing to FB (Facebook), T (AT&T) to Thai Industrial & Engineering Service PCL. X (US Steel) was pointing to another company other day, but at the moment it’s pointing back at US Steel.

    Seems like a problem with MSN’s data feed implementation.

  53. Dean McKaig on May 2, 2017 at 5:16 am

    My Yahoo .iqy query file(s) are not returning any stock quote data. Is someone working on the issue? Is there a target date when it will be fixed before a convert make to an alternate stock quote data supplier. To qupola, can you shed any light on the Yahoo finance site layout change? Is there a someone we can contact to get the latest status?

  54. Doug S Rice on May 2, 2017 at 3:41 pm

    There was once a way to get index values from MSN Moneycentral…any help would be appreciated

  55. Thierry on November 5, 2017 at 2:35 pm

    Indeed Microsoft has a new version of the Smartlookup, based on the entity concept (introduced with Excel combined with MS Dynamics) – See the second part of my article:

    https://www.intermotion.biz/single-post/2017/10/23/Spoiler-Alert-Excel-2016-becomes-full-BI-tool

    The feature should appear this year in the 365 version of Excel (2016).

  56. John on February 22, 2018 at 5:20 pm
    • Jeff Lenning on February 22, 2018 at 5:23 pm

      Thanks John … great find!

  57. Carol Scotti on July 2, 2019 at 8:26 pm

    The add-in from Gaier Software worked great. Thank you to John for posting and Jeff for validating via reply.

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.