Microsoft Excel Tutorial Series: Importing Market Data from Yahoo Finance

This article is the next article in the Microsoft Excel tutorial series. If you are not familiar with Microsoft Excel or have not read the previous article data entry and navigation I would suggest you read it before continuing. There are many ways in which Yahoo Finance data can be imported into Microsoft Excel, however we will be focusing on the most simple. Yahoo Finance provides a good range of data.

Quotes
Which include: Summary, Options , Historical Prices and Charts.

News & Info
Which includes: Headlines, Financial Blogs, Company Events and Message Board.

Company
Which includes: Profile, Key Statistics, SEC Filings, Competitors, Industry and Components.

Analyst Coverage
Which includes: Analyst Opinion, Analyst Estimates, Research Reports and Star Analysts.

Ownership
Which includes: Major Holders, Insider Transactions, Insider Roster.

Financial’s
Which include: Income Statement, Balance Sheet and Cash Flow.

 Step One

The first step in the process is to visit the Yahoo Finance website, or you may wish to use your localised version of Yahoo Finance. For the purpose of this tutorial we will look up the historical data for the Yahoo stock YHOO.

Next we will enter the ticker code for the stock we wish to import into Microsoft Excel. To do this it is simply a matter of clicking in the "Get Quote" field and entering the ticker symbol. In this case it is YHOO, you will notice in the image below that Yahoo Finance has an auto complete box that pops up this is helpful if the stock you are looking up is listed on multiple exchanges or if you forget the ticker symbol.

Step Two

You should now see a screen similar to the one shown below. This is Yahoo Finances main quote screen for the given stock. From this screen you can quickly see the stocks key metrics. We are interested in importing the historical data for the stock so simply click on Historical Prices and you will be taken to a new page.

When you arrive at the historical quotes page a default date range will be selected. It is a very simple process to now get this historical data ready for importing into Microsoft Excel.

1. Select a date in which you want to start your analysis from.
2. Select a date that you wish to analyse to.
3. Choose which timeframe you want your data organized. This can be daily, weekly, monthly or can show dividends only.
4. Click on the "Get Prices" button to modify the date range.
5. After clicking "Get Prices" a new window will open up with the requested data in a table format. Scroll down to the very bottom and click "Download Spreadsheet".

This process is highlighted graphically below

NOTE: At this point you will be prompted weather you want to download or save table.csv. I would recommend that you save the file to refer to later.

The file you have just downloaded is not a Microsoft Excel file it is a Comma Separated Value file or a CSV. This just means the file is really just a plain text file with numbers and fields separated by commas. This is a standardised format and can be read by many programs not just Microsoft Excel. Below is a image of the data we have just downloaded  opened in Notepad:

Step Three

This is the final and most simple step, simply double click on the data file you have just downloaded. Now Microsoft Excel 2007 will open and automatically insert the data into a spreadsheet. You will have to adjust the size of the columns or you will see # symbols for the date field as shown below. After you are happy with this it is important to go to the office button and choose "Save As" and select Excel Workbook document. This will ensure your formatting has been saved along with the data.

Conclusion

This has been a very simple introduction to importing stock data from Yahoo Finance into Microsoft Excel. This is not the best or most efficient way to import data to Microsoft Excel and in the coming articles I will be exploring my robust means for importing data. In the next article we will be working with Yahoo Finance stock data for charting and basic trade system development.

Homework

As I believe that when it comes to learning it is important to reinforce knowledge by doing. Before you move on here is some quick homework to make sure you know how to import data and to ensure you are ready for the next part of the tutorial series. This will take less then five minutes.

1. Go to Yahoo Finance and go to the main quote screen for the company Microsoft MSFT.
2. Download the Historical end of day price data for Microsoft for the period: Jan 11 2008 to Feb 11 2008.
3. Open the file and fix any formatting issues and re save the file as an Excel Workbook named MSFTEOD.

About the author

Mark - Is a 28 year old form Australia. He has a Bachelor in Economics and a Major in CS. He is currently working towards his CFP. His interests include algorithmic trading systems, artificial intelligence, game theory and poker.