Download historical stock quotes from Google Finance straight into Excel with this free spreadsheet. Everything is automated with some clever VBA (which you can view and extend.)
Once you have the stock data, you can analyze it, conduct historical backtests and more. As an example, the VBA in the spreadsheet calculates the mean daily return, standard deviation and the variance.
This VBA can be extended to calculate other things. For example, you can calculate and plot the relative strength indicator, average true range, or the historical volatility.
The spreadsheet is simple to use. Just supply the ticker symbol, start date and end date. After you click a button, Excel uses a VBA macro to connect to Google Finance to import the data.
The spreadsheet imports the trading date, open price, high price, low price, close price, trading value and the adjusted closed. The data is downloaded into a worksheet called “Data”.
The VBA can be viewed and modified. In Excel 2010, go to Developer > Visual Basic and then find Module 1 in the Project Explorer.
Other spreadsheets to download finance data from the web can be found here.
Get Excel Spreadsheet to get Historical Stock Quotes from Google Finance
Beautiful, simple, perfect…
Is there a way to add multiple symbols with the data organizing in one separate tab?
If you’ll excuse the late reply, check out the spreadsheet at http://investexcel.net/3878/multiple-stock-quote-downloader-for-excel/
It lets you download multiple historical stock quotes (from Yahoo, not google), with data from each ticker on separate tabs
This doesn’t do it in one tab, but paste this code into the worksheet module and re-assign the button “Get Data from Google” to the “RunTickers” routine. Then, start a ROW of tickers anywhere and leave your cursor on the first (left most) ticker.
Click the button to get data and you will have a tab per ticker with historical data.
Change the Start date to =workday(today(), -1) and the end date to 01/01/1990 and you’ll always end up with a refreshed bit of historical data for each ticker.
[code]
Sub RunTickers()
Dim i As Integer
With ActiveSheet
For i = 0 To 100
If Len(.Cells(ActiveCell.Row, ActiveCell.Column + i).Value) > 0 Then
Range("B6").Value = .Cells(ActiveCell.Row, ActiveCell.Column + i).Value
GetData
Add_Symnbol_Column
Worksheets("Sheet1").Activate
Else
Exit Sub
End If
Next i
End With
End Sub
Sub Add_Symnbol_Column()
Dim numrow As Integer
Dim lastrow As Integer
'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Symbol"
Range("A2").Select
lastrow = ActiveSheet.UsedRange.Rows.Count
With Application
For numrow = 2 To lastrow
.Cells(numrow, 1).Value = ActiveSheet.Name
Next numrow
End With
End Sub
[/code]
Just FYI: I get an error when opening this spreadsheet — but I don’t with your other ones.
I like the nice clean format of your site and spreadsheets.
Thanks!
Johnny
(Error details are:
———————-
Removed Records: Sorting from /xl/worksheets/sheet2.xml part
-
error024440_02.xml
Errors were detected in file ‘C:\Staging\Google Finance Stock Quotes.xlsm’
-
Removed Records: Sorting from /xl/worksheets/sheet2.xml part
———————-
I think I’ve resolved this error. I added “.SortFields.Clear” to the sorting code. I’ve uploaded the corrected spreadsheet above