Google Finance Stock Quotes in Excel

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.

stock quote parameters

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

stock quotes in Excel

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


44 thoughts on “Google Finance Stock Quotes in Excel”

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

    Reply

    • Hank, I cannot get your code to work, i also want to get bulkquote data from google finance, wonder if this can be added or modify to the yahoo one, thanks

      Reply

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

    ———————-

    Reply

  4. When I try to use this sheet to fetch CPH:VWS (Vestas Wind Power), I get an error message from the system, informing me, that the data cannot be found. I got the code to debug.print the URL, which displays
    http://finance.google.com/finance/historical?q=CPH:VWS&startdate=jan+1+2011&enddate=jun+1+2012&output=csv

    When I enter this in a browser, I ger similar error msg., but if I remove the last part “&output=csv”, the URL Works.

    Can anybody explain why, and how I can overcome this problem?

    Reply

  6. I downloaded your workbook Download Stock Quotes from Google. It works well for stocks, but does not work for mutual funds. Is there a way for me to modify it to get mutual funds.

    Thank you

    PS I did make a small donation 3 British Pounds

    Reply

  7. Samir-

    Very helpful stuff here. I’ve been using your Yahoo download for some months now, but it seems to be giving a run-time error 1004 at the following line-

    .Refresh BackgroundQuery:=False

    I was wondering if you had the same problem. Also, I figured I’d try your Google download spreadsheet instead, but I don’t see an adjusted close column to account for splits/dividends. Is there no such adjustment with Google?

    Thanks for your help.

    Regards,
    Steve

    Reply

    • Excel (and all Office) use Internet Explorer to access internet. The error has nothing to do with Yahoo or Google.
      Your code blows up on BackgroundQuery probably because it’s the last line in QueryDef – switch it around with .Destination and see what happens.

      To fix, try clearing IE temp files, increase Temp Files size to 50MB and reboot. Also make sure that you don’t have a uninitialized instance of Excel floating in memory (use Process Explorer from SysInternals)
      Good luck

      Reply

  8. Hi,

    thank you for providing such amazing stuff.

    When using this spreadsheet I am able to get data for Nasdaq stocks but when I enter 3 letters stock tickers (e.g. NYSE stocks) I get a message that VBA Excel is unable to open the web page.

    Is there something particular that I need to write when entering the ticker symbol?

    Thank you for your help,

    Cordially,

    Alan

    Reply

  10. Samir.

    Great model, I love it. However, I was trying to download the historical data for Index (eg DJUSAL & DJUSCL) but I get a error message. Could you please advise.

    Regards

    Adrian

    Reply

  11. Hi Samir,
    I’ve been using your Multiple Stock Quote Downloader and love it. Before this I used a program called “GetQuote” which had a “GetHistory” feature where I could take a column of dates and a column of stock symbols, and it would give me the open, high, low, volume, and adjusted closing prices for each of the stocks for it’s specified date. It was a simpler program for what I needed. It however no longer works in the “GetHistory” mode. What would it cost me to have you develop such a spreadsheet? I need to have variable dates with the stock symbols and your present program doesn’t allow this.
    Thanks,
    Merle

    Reply

  12. Hi,

    Im trying to get multiple Finnish Quotes (For instance SAA1V and LEM1S) from Google (not available in Yahoo) and it keeps giving me error. If I commet the “date” section of the VBA code (“qurl”) I get the data but it is unedited. How can I fix this and is there anyway for me to get multiple quotes at the same time from Google Finance?

    Thank you a lot for your answer!

    Reply

  14. Hello,

    You have done some really good job here. I have a problem though.

    I was using this spreadsheet of yours (Google Finance Stock Quotes in Excel); however, when I specify a much older Start Date, it does not give quotes as far as that date. For example: If I specify a Start Date as 1/1/1985 for KO (Coca Cola), I get quotes only from 3/3/98 onwards.

    Could you please look into this? Or am I doing something wrong here?

    Thanks,
    Akshat

    Reply

  15. Jesus guys, so much trouble for such easy tasks. Just switch to Google spreadsheets and use: (1) functions like GOOGLEFINANCE, IMPORTHTML, etc and (2) Google apps script, which is javascript based and way easier than VBA.

    Reply

  16. Hi!
    how do I modify the code to show me the daily historical data of the last 10 years? I don’t mind about hours and minutes.

    Tks,

    Reply

  17. Hi,

    Nice work. Your spreadsheet works smoothly for individual stocks, however when I tried to use it to download VFTSE index quotes I got a message saying “Run-time error 1004: Unable to open http://finance.google.com/finance/historical?q=VFTSE&startdate=Jan+,
    Cannot download the information you requested.”

    Also, the following line was highlighted in yellow: .Refresh BackgroundQuery:=False

    I used both the VFTSE ticker and the INDEXEURO:VFTSE ticker. Do you know what’s wrong?

    Thanks,
    Paul

    Reply

  18. Hi,

    Thanks, is there a way around that? Alternatively, is there another website from which I can get VFTSE and VSTOXX data into Excel using VBA?

    Best regards,
    Paul

    Reply

  19. Hello, Samir Khan.
    I downloaded this spreadsheet at 06.12.2014, and it don’t react on end’s and start’s dates. Whatever date i input in both cells – I always get data from 09.12.2013 to 05.12.2014. I even deleted all dates – same result…
    Do you know what’s wrong?

    Thanks,
    Viktor

    Reply

  20. Hi Samir
    I have tried using your VBA sheet to download historical stock data from Google but I cant get it to work with foreign exchanges – Australia for example – I’ve fiddled with the code but cant figure it out?? Can you modify your Google sheet to include Foreign exchanges such as Australia? I currently use your Yahoo downloader which works fine with all exchanges but Yahoo are very slow in uploading previous days data – sometimes the next days trading is done before they release previous day and this makes it poor to use – I’m hoping Google will be quicker with previous days data.

    Also do you have a VBA sheet that will download stock data from a website by using the source code and “getByElements” method. Excell works ok with tabular data but if its not in tables its hard to get at – Would appreciate any assistance

    Reply

  21. Hi Samir,

    Thank you for “Multiple Stock Quote Downloader for Excel” and it is very useful. However since yahoo provide only 2 decimal place for closing prices, I do have issue analyzing stock which trade below 10c.

    http://www.tradingroom.com.au (Australian website) provides closing price with more than 2 decimal places.

    I was wondering if you could modify the below code in your “Multiple Yahoo stock quotes” model. I have limited knowledge in VBA, I couldn’t modify myself.

    With ActiveSheet.QueryTables.Add(Connection:= _
    “URL;http://www.tradingroom.com.au/apps/qt/quote.ac?code=” & sASXCode & “&section=pricehist&submit=Go%21” _
    , Destination:=Range(“a1”))
    .Name = “quote.ac?code=” & sASXCode & “&section=pricehist&submit=Go%21”

    Please let me know if you could assist me with this.

    Thank you
    Adrian

    Reply

  23. Hi Samir,

    question regarding your Stock Quote Tool for Google Finance, which is perfect.

    I need quotes for Indices. For TADAWUL:TASI your tool is working but why not for Major Indices like S&P, DAX, Eurostoxx (here the google finance ticker (INDEXDB:DAX INDEXSTOXX:SXXP INDEXSP:.INX INDEXHANGSENG:HSI). All these ticker are working on google finance page but not with your tool. Thanks for your help.

    Reply

  25. Hello Samir Sir, Thanks for your great effort.

    I would like to ask 1 technical problem, apart from downloading the historical quote price from google finance, can we get the information of income statement, cash flow etc by using excel vba?

    Now I am using the special tag function from yahoo finance, but I found that some data is incorrect.

    Thank you very much.

    BR,
    Jason

    Reply

  26. This is super helpful!

    I can quickly put the column with closing price OR closing index value in a chart with a linear Y-axis and get what I need.

    Many of the online charting sites have a button you can click that converts the chart to a linear (not log) vertical percent axis so you can see the growth in percent over time.

    Do you have another chart that does this? (I tried to build a formula in Cell 3 that could then be copied down but my results appear to be incorrect).

    Scott

    Reply

  27. I tried to get quote on Excel for TSX (Toronto Exchange) but It doesn’t work.
    I would appreciate to help me out for this issue.

    Thanks,

    Reply

  31. Since Yahoo no longer allows download of historical data, my vba code doesn’t work anymore. I tried to use your program and it works fine for stocks but not for Mutual Funds. I want to download mutual fund historical data to track my retirement funds.

    What am I doing wrong? Do you know of a way to download Mutual Fund historical data using vba?

    Your help would be appreciated.

    Thanks

    Reply

  32. Hi , Can you please help in finding indian stocks historical data? I have tried giving ticker :NSE:TATAMOTORS for TataMotors but not able to find historical data.

    Thanks,

    Reply

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.