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.

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:
http://www.jarloo.com/yahoo_finance/

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.

 

This article was written by Jeff Lenning

96 comments:

  1. Zongyong Zou
    Reply

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

  2. Andy
    Reply

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

  3. Jon Brophy
    Reply

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

    1. jefflenning Post author
      Reply

      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
    Reply

    Thank you, needed these 🙂

  5. chris
    Reply

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

    1. jefflenning Post author
      Reply

      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
    Reply

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

    1. jefflenning Post author
      Reply

      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

      1. jefflenning Post author
        Reply

        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
    Reply

    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?

    1. jefflenning Post author
      Reply

      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

      1. Taylor
        Reply

        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.

      2. Michael
        Reply

        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.

        1. jefflenning Post author
          Reply

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

  8. Dennis
    Reply

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

  9. Raphael
    Reply

    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
    Reply

    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

    1. jefflenning Post author
      Reply

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

  11. Charlie
    Reply

    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.

    1. Mark
      Reply

      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
    Reply

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

  13. Lisa
    Reply

    Jeff – Thanks!!! I can work again.

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

  14. Ron
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

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

    1. jefflenning Post author
      Reply

      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
    Reply

    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
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

    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!

    1. jefflenning Post author
      Reply

      Welcome! Glad it helped!
      Thanks
      Jeff

      1. jefflenning Post author
        Reply

        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.

        1. Brian
          Reply

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

          1. jefflenning Post author

            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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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?

    1. jefflenning Post author
      Reply

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

  21. Brian
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

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

  23. Bailey
    Reply

    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?

    1. jefflenning Post author
      Reply

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

  24. Bailey
    Reply

    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.

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

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

  26. JBAswoll
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    it seems the total amount of quotes is restricted now ?

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    What about exchange rates ?

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

    1. jefflenning Post author
      Reply

      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

      1. Maggy
        Reply

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

        Maggy

  31. rijk
    Reply

    “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

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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 …

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      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

      1. SomeGuy
        Reply

        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
    Reply

    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
    Reply

    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!

    1. Kurt LeBlanc
      Reply

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

  41. Pedro Reis
    Reply

    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.

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      Welcome 🙂

  44. Guillaume
    Reply

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

    1. jefflenning Post author
      Reply

      🙂

  45. Sean Cody
    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
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      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

  47. David
    Reply

    You sir, are a legend. Take a bow

    1. jefflenning Post author
      Reply

      🙂

  48. Luca
    Reply

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

Leave a Reply

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

By submitting this form, you accept the Mollom privacy policy.