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.