Downloading Stock Prices into Excel Spreadsheet

With the advent of fast and affordable computing power on the one hand and the availability of powerful, feature-complete spreadsheet on the other hand, financial calculation and modeling have increasingly been performed on Microsoft Excel, the industry-leading spreadsheet of choice.

Investors and investment professionals alike now massage investment-related information on Excel. A function of Excel is to import stock information. There are chiefly 2 ways to retrieve stock/ETF/mutual fund prices into your spreadsheet. One way is via MSN MoneyCentral database connection. The other is through Yahoo! Finance Excel macro.

You can view the MSN MoneyCentral way through this hyperlink.

However, the MSN MoneyCentral approach might not be suitable for some people as it doesn’t list a number of foreign stocks outside USA. For example, Singapore stocks are not included by MSN MoneyCentral. Fortunately, Yahoo! Finance has most of such data and thus the Yahoo! Finance Excel macro is my preferred tool to import stock quotes. You can download the sample Excel spreadsheet here (if you’ve MS Office 2007 or above, get this spreadsheet instead).

The spreadsheet contains sample macros and stock tickers which you can modify to suit your needs. You’ve to remember to enable macros in Excel in order for it to work. As the data source is from Yahoo! Finance, you’ve to check with Yahoo! Finance for the stock ticker information, it might differ from MSN MoneyCentral. For advance users, the Yahoo! Finance Excel macro is the preferred way as Excel macro in infinitely customisable.

You can browse the author’s documentation page at this link.

If you want to import historical quotes, you can download a sample Excel macro spreadsheet for viewing here (if you’ve MS Office 2007 or above, get this spreadsheet instead).  As always, remember to enable the macros.  This particular macro uses Yahoo! Finance symbols and data.

58 thoughts on “Downloading Stock Prices into Excel Spreadsheet”

  1. Regarding the yahoo4-1.xls spread sheet. I did a bit of work on it to provide the following extensions…

    Solve the embedded commas in the stock expanded name field.
    Allow calculation to be interspersed among the columns retrieved from Yahoo.
    To provide room at the start of the sheet for user defined data.
    Make it easier to add columns of Yahoo data to the sheet.
    Added a row containing the yahoo download field ids.

  2. On some quotes I get a return for the current price that is alph. For instance the return for PDLI is ln

  3. Hi Roger, I got the correct current price for PDLI, ie 7.83, which is what the Yahoo! Finance website reports.

  4. Your Excel stock quote function looks very useful. I’ve been searching for something like this for a while. Not pleased with the Microsoft imbedded DATA/CONNECTIONS function.

    Also like your blog site. Very nice pics.

    Have many fond memories of Singapore from many years ago when I lived in Tokyo and traveled throughout Asia extensively.


    Richard, from Reno, Nevada, USA and La Cruz de Huanacaxtle, Nayarit, Mexico.

  5. Just tried the ‘download stock prices’ spreadsheet on some Australian stocks and it worked perfectly – and very fast. Many thanks. Regards, Don.

  6. Hi

    Thanks, very helpful spreadsheet. Is it possible to modify it to become a watchlist of sorts by adding in target prices on the up and downside?

  7. Nice article. In the excel spreadsheet (Yahoo download) is it possible to pull data for weekly quotes (instead of the default daily) ?

  8. Hi. Just found this and it seems great.. I’m not familiar with macro’s.. i tried to modify the yahoo download string.. but it did not work. I am also getting a popup to log into yahoo finance… Any help is appreciated

  9. I am getting the MsgBox “error loading Google Finance XML stream” error message. I am running Excel 2010. What are mix fixes?

      1. I see the problem. I cut and pasted a VBA routine from one of the respondents above and now realize that this is not your code which is where the confusion lies.

        If I just want to get a price from Yahoo, will your code update it automatically or do I need to set a timer to refresh the cell?

          1. Interestingly, I ended up taking a different approach. I created a portfolio on Yahoo so it kept refreshing with updated prices and then used the “from Web” function on the Data tab on Excel to pull directly from that web page. You can then go to the “connections” button in the same tab and configure that to auto refresh every minute. Works fine but is probably slower over all. This does not require blazing speed so all is good.

  10. Hi Amour Tan,
    thank you so much for this spreadsheet!!

    In the Yahoo5 file, for some tickers (ie FB) I get “Facebook” in the name and than “Inc.” in the Price. Did you, by any chance, adjusted this little flaw? If yes, that would save me a lot of time, I try to adjust it with some macros but I’m not good enough..

    Thanks anyhow!!!!!!

  11. Hi Amour Tan,
    Thank you very much for this macro…this is much better than using MSN quotes in Excel. I have found that the dividend/share data isn’t always up to date matching what is published on yahoo finance. An example is APU is showing $3.48 using the macro but $3.52 on yahoo finance. Any ideas on how to get the most recently available data?

    Thank you in advance!

  12. Hi Amour Tan,
    using historical2 do not understand why it can not download the quotes of the DJ index (^DJI).
    Thank you in advance!

  13. THANK YOU! Works much better than MSN stock quotes used to. Microsoft has now disabled MSN quotes (at least from Excel 2011 for Mac).

  14. Hi Armour, thanks for this. It’s exactly what I’ve been looking for, but when I run it on Excel 2011 it results in run time errors. Is it meant to be compatible with mac Excel 2011?

    If it’s not, do you know of anything similar for fetching Yahoo finance data?

    Thanks for your kind help

  15. Great spreadsheet. How can I stop sheet from putting stocks in alphabetical order. I want to keep them in the order I enter them?

      1. thanks, that worked. great spreadsheet. Would it be possible to have a cells to the right available to make notes about each stock. I tried doing that, and when I hit the download button the notes off to the right are erased. I’d like to be able to write just a few points about each stock on my watch list and maybe a “buy Price” with conditional formatting that would turn colour when a stock price hits my “buy price”.

  16. Hello,

    Love this spread sheet!
    Ive given it a few tweaks to colour code values like price increase green, price decrease red and change the sort etc.. However how do I go about extending the number of columns? as im after more than 10 pieces of data, but I cannot seem to find how to extend the form.

    Probably a simple setting I am over looking.

    1. Those “pieces of data” are called data points.
      1) Modify the Yahoo parameter “nl1rr5dp6m8m6j6s7” in cell B2 to whatever you need.
      2) Learn conditional formatting (for colors)
      3) Learn VBA (for colors and much else)

  17. Hi, Amourtan. Nice file. Quirky VBA.

    You do realize that Yahoo provides FREE intraday data with a 15 minutes delay? I use it in some of my spreadsheets, but only where delay is irrelevant. Otherwise I use a much more cumbersome Google Finance, which provides correct real-time data.

    I could have modified the Yahoo parameters in you spreadsheet, since most of your values are irrelevant to me, but than I realized that my own spreadsheets and db do the job cleaner and and with more options.

    I AM VERY grateful for the demonstration that Yahoo can return 2300 stocks (in my case). Before I only tried it with 100.

    I corrected split names with Left(InStrRev(strName,”,”)). You can also replace comma with a space.

Leave a Reply