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.

31 thoughts on “Downloading Stock Prices into Excel Spreadsheet

  1. Pingback: Downloading Stock Prices into Google Spreadsheet | Amour Tan @ Singapore

  2. 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.

  3. Pingback: Downloading Stock Prices into OpenOffice Calc | Amour Tan @ Singapore

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

  5. 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.

    Thanks…

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

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

  7. 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?

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

  9. 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

Leave a Reply