importing data from google finance to excel

i use excel to track my investments from stocks to mutual funds. here's a short tutorial on how to import data from google finance (or any other web-based source) into an excel spreadsheet.

the method i use:

• avoids the use of external web connection files (*.iqy),
• allows the stock symbol to be changed and autoamtically refreshes the result with new data from google.
• is excellent for scripting, since the inputs can be changed.

here are the step-by-step, blow-by-blow instructions, written for excel 2007 (earlier versions have different menus).

1. in the excel worksheet Sheet1, add the following data:
• b1 is the stock symbol to query, e.g. "appl",
• b2 is the start date to query formatted as "mmm dd, yyyy", e.g. "jun 26, 2009",
• b3 is the end date.
2. next, select the data ribbon > from web.
3. in the "new web query" window, type the address http://www.google.com/finance/historical?q=["SYMBOL", "Stock symbol"]&startdate=["START", "Start date"]&enddate=["END", "End date"]&num=200
4. press go, and google will return with "bad request" page, since the parameters q, startdate and enddate are not valid. however, the format is valid for excel, which knows to prompt for the correct data a little further on.
   
5. just press import, and excel will show this "import data" dialog. you can select new worksheet, although selecting the same sheet also works fine (but be careful with the formulas).
   
6. excel then prompts for the correct data. first, for the "stock symbol", which is the data required in the url parameter q.
7. select the worksheet with the input data, and select the correct field, in my case, Sheet1!$B$2.
   
8. check use this value/reference for future refreshes and refresh automatically when cell value changes so that everything becomes automatic the next time values are changed. go ahead and click ok.
9. repeat step 7 and 8 for "start date" and "end date".


10. when the last step is done, excel will automatically connect to google, get the data, and display it in the new sheet.
11. go back to Sheet1 and change the symbol, start date or end date, and you'll see that excel re-connects to google for new data.

of course, limitations exist (what would the world be without limitations?):

1. google only provides 200 records at a time, the rest is on subsequent page. it's possible to get the second page by appending &start=200 to the url, &start=400 for page 3, and so on.
2. with this method, it's not possible to select the html <table> for excel to extract, but it does not affect my formulas, for example:
   
   
   
   • to get the start date of the range, use the formula =min(a:a) and make sure the number format (in the home ribbon) is "short date" or "long date".
   • to get the end date use =max(a:a). why bother with this when the data is also input in sheet 1? because the dates you entered might not be dates when the market is trading, e.g. weekends and holidays - using min and max checks the real data..
   • to count the number of records returned, =count(a:a), which surprisingly only counts the rows with real data and ignores the headers and footers. don't really know why, but it works for me!

now you have the data, do what you want with it - massage it, display it, chart it - but that's another story for a different day...