Free Intraday Stock Data in Excel

This spreadsheet downloads realtime intraday backfill from Google.

You can choose the interval, the number of trading days, the ticker symbol and the exchange. The VBA is open, and not password protected – you can view, edit and learn from the code.

Many websites offers end-of-day historical quotes – this can often be downloaded into a spreadsheet via a programmable web API. The Bulk Stock Quote Downloader, for example, retrieves stock quotes from Yahoo Finance.

Intraday historical stock data is harder to find; you usually have to pay to find accurate data with no omissions. However, Google Finance offers an API that lets you download intraday backfill data in a CSV file.

An Excel spreadsheet that downloads historical intraday stock quotes from Google

This Excel spreadsheet employs this API to download intraday stock quotes for the past fifteen days. Just enter

  • a ticker and the exchange
  • number of past trading days (from 1 to 15)
  • and the time interval  (you can choose between 1 min, 2 min, 5 min, 10 min, 15 min, 30 min or 1 hour from a drop-down menu)

drop down

After you click a button, the spreadsheet connects to Google Finance, and imports the historic intraday data. You get the Time, Open, Low, High, Close and Volume Traded.

intraday quotes for IBM on the NASDAQ exchange in a spreadsheet

Column A contains encoded date and time data; You see a time zone offset, time stamp (which is repeated if the backfill crosses more than one day) and a number.

  • The time stamp is of the form a1404826200 (the numbers after the “a” differ). This is a Unix time stamp
  • The time zone offset is a constant offset from the time stamp
  • The numbers below the time stamp (in column A) are integer multiples of the backfill interval

Converting this data to a meaningful date and time takes a few steps.

  • In Unix time, the start of every trading day is timeStamp + timeZoneOffset * 60
  • In Unix time, every subsequent time interval is timeStamp + timeZoneOffset * 60 + backfill interval * integer multiple

You then convert to an Excel time stamp by

  • transforming to an Excel time stamp with this equation: (Unix time) / 86400 + 25569
  • formatting the Excel time stamp to a date and time (e.g. by changing the number format to NumberFormat = “d mmm yyyy h:mm;@”)

These steps are automated by the VBA in the spreadsheet. The VBA can be viewed and edited.

With the data provided by this tool, you can backtest trading strategies, plot technical indicators and generate buy-sell signals. All of this can be performed inside Excel, and mechanized with VBA.

Download Excel Spreadsheet to Get Free Intraday Data from Google


50 thoughts on “Free Intraday Stock Data in Excel”

  1. Great example. I’ll be converting downloading so it is assigned to variables and then manipulating it there so I can just write out a final array of dohlcv data for charting. Something like:

    Dim oHTML As New HTMLDocument
    Dim oDoc As Object
    Dim lines As Variant

    Set oDoc = oHTML.createDocumentFromUrl(url, vbNullString)
    Do: DoEvents: Loop Until oDoc.ReadyState = “complete”
    lines = Split(oDoc.DocumentElement.outerHTML, vbNewLine)
    ‘and so forth.

    One minor issue in the code is that data connections created by the .QueryTables.Add accumulate if not deleted. This has caused me problems in the past when thousands of old connections slowed everything down mysteriously.

    Thanks.

    Reply
  2. How do I make sure that the data gets updated automatically every minute or every 5 minutes without me clicking on the get data button?

    Reply
    • Sir… can u provide with the code for automatic update after 1 min .. and also want to ask if its possible to get the data of two stocks in same excel or same page (that would b grt) since,, Im trying for the spread trading so I can plot the spread between two stocks for intraday buy sell signal…
      Thanks do reply Im excited with this new strategy .. or any xpert who can help me with improvising this

      Reply
  3. Hi,
    thanks for a great tool,
    do you know if i will run a loop (under C#) and ask Google for many tickers info, let say 100 at a time, he may block me from his services ? do you think i need any sleep ?
    thanks

    Reply
  4. The excel sheet is very useful. Thanks a lot!!
    But it gives an error when I try to fetch data beyond past 4 days. Please help me with this error.

    Reply
  5. I have the same issue. Cannot retrieve data for the days before the 9th of March 🙁
    Do you know what would be the problem with the following message that I receive?

    “Run-time error 13:
    Type mismatch”

    Thanks

    Reply
  6. Dear Samir:
    This Excel Tool is so helpful. Thank you for sharing it online.
    I had the same question as Simon mentioned, how can we get the historical data longer than 15 days?
    Looking forward for your reply.

    Thanks,
    Nathan

    Reply
  7. Thanks for posting a excel sheet “Realtime Intraday Stock Quotes”.
    Is it possible to add autorfresh & heikin ashi, bolinger bands, macd, psar this excel sheet.

    Reply
  8. This is a great tool! thanks for sharing! Do you know if anything like this exists for intraday oil prices? I’ve been trying to find historic intraday data, and it’s proving to be a difficult task.
    Thanks!

    Reply
  9. Thanks for the file Samir, this saved me a lot of time of having to write the code myself!

    Anyway, when I downloaded it, the data was not downloading and so I delved into the code to find out why. A quick check found that the download url seems to have changed. If the code does not work for you, try changing the qurl code as shown below:

    From:
    qurl = “http://www.google.com/finance/getprices?” & _
    “q=” & ticker & _
    “&i=” & interval & _
    “&p=” & numPastTradingDays & “d” & _
    “&f=d,o,h,l,c,v”

    To:
    qurl = “http://www.google.com/finance/getprices?” & _
    “i=” & interval & _
    “&p=” & numPastTradingDays & “d” & _
    “&f=d,o,h,l,c,v&df=cpct” & _
    “&q=” & UCase(ticker)

    UCase function gets rid of the error if the user inputs the ticker symbol in lower case letters.

    Also, as a point of interest, Samir, your “exchange” variable is unused, perhaps it was necessary in a previous version?

    Thanks again for your work!

    Reply
  10. hi sir i want free-intraday-stock -data for more then one stock at a time i mean i want intraday stock data for multiple quotes please can u help me.

    Reply
  11. although good This was almost a solution for me but not exactly what i need for my purposes. I have been trying to find out if its possible to get all the intraday data from either google or yahoo finance for a single security i.e say the DOW. By this i mean every every tick change in price, instead of the OHLC for a 1 minute time frame. There must be a way to record this from the data stream using code or by downloading at the end of day to excell. Any ideas on how to do this?

    Reply
  12. HI , Im curious what it would take to persuade you to make a intra day quote downloader that could do more than 1 stock at a time.
    Love your spreadsheets, thanks alot. I havent donated yet but I plan too.

    Reply
  13. can you please write a script for uploading some 50 scrips data from excel to metatrader after importing from google finance? I understand it might take your time, but I’d be willing to compensate if its reasonable. thanks.

    Reply
  14. I think I got the answer and it seems google doesn’t allow more than 15 days of data! Appreciate your effort with this great worksheet. Can you suggest how I can get data for past 2 years.

    Reply
  15. Will it work on all indian stocks (NSE)
    when i used LT (larson and tubro) and some major stocks it shows
    “Run-time error 13:
    Type mismatch”
    but works good for other mid cap stock
    pls suggest

    Reply
  16. Hey guys I am from South Africa and was struggling to get it to work with JSE (Johannesburg Stock Exchange) stocks – I think I worked it out… the code for the exchange info was left out in VBA. See below to put it in. hint: hit ALT F11 to open VBA from the spreadsheet 🙂
    In VBA find this area and add in “&x=” & exchange & _ below the “q=” & ticker & _ line of code.
    Eg:
    qurl = “http://www.google.com/finance/getprices?” & _
    “q=” & ticker & _
    “&x=” & exchange & _
    “&i=” & interval & _
    “&p=” & numPastTradingDays & “d” & _
    “&f=d,o,h,l,c,v”

    Now you should be able to get stocks using your local exchange code, such as the JSE in my case.

    Also, if you would like to add an auto-refresh function for the sheet, add this line Application.OnTime Now + TimeValue(“00:00:15”) above Application.ScreenUpdating = False
    Eg: (near top of the module)

    Sub GetData()
    Dim ParameterSheet As Worksheet
    Dim DataSheet As Worksheet
    Dim ticker As String
    Dim exchange As String
    Dim interval As Integer
    Dim numPastTradingDays As Integer
    Dim qurl As String

    Application.OnTime Now + TimeValue(“00:00:15”)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Not sure if this is the best way to do it, but it works 🙂

    Now, if someone could please help me to record the data into another sheet as it comes through keeping all the data stacked and not just a certain time period, that would be wonderful.

    Have a great day from sunny South Africa!

    Chris

    Reply
  17. It looks like the Google API doesn’t support mutual fund pricing (tickers ending in ‘X’, e.g. ‘POLIX’.
    Can you confirm this? Is there a workaround?

    Reply
  18. this doesn’t work for mutual funds: ticker ends with an ‘X’, e.g. ‘POLIX’
    Is there a workaround, or is it a limitation of the API?

    Reply
  19. Hi, thanks for the great excel, found it very good source for intraday data.

    came across a small issues

    some stocks data is coming as below, NSE stock exchange and scripts like CEATLTD, TATACOMM.

    248 1844 1844 1842.6 1842.95 535 20 Jul 2017 13:24
    2,491,844,184,418,440,000,000 #######
    2,501,844,184,518,440,000,000 #######
    251 1845 1845.2 1844.05 1844.05 720 20 Jul 2017 13:27
    2,521,845,184,518,430,000,000 #######
    253 1844.95 1844.95 1844.95 1844.95 10 20 Jul 2017 13:29

    i am not sure where is the issues, this is happening only for some scripts. others are fine.

    Reply
  20. Hi. Love this tool but recently (as of this week) I’ve been having trouble pulling 15 days worth of data. It seems to fall back to Aug 25th. Probably something to do with google finance but thought i’d ask if you knew what was wrong. Thanks!

    Reply
  21. Hello,

    First off, thanks for your spreadsheet that downloads price and volume data from Google. It has proven to be a useful tool.

    I am traveling in Europe while using the spreadsheet, and have had no problem with it until today. When I try to download minutely data for a company, it only downloads 20 minutes from a previous month. It’s strange because before, I was able to download up to 15 days worth of minutely quotes and volumes. Could it be because there isn’t access to the data from certain countries?

    If you have any ideas, let me know!

    Best,

    L

    Reply
  22. Hello,

    Did google finance change something? Seems to only provide a few data points from August 25th. Perhaps intraday data isn’t available anymore from them?

    Thanks

    Reply
  23. Hi Samir,

    FYI it seems like google changed their URL from http://www.google.com to finance.google.com

    If anyone else has the problem of this spreadsheet not working you can just edit the vba and replace www with finance in the url as above and it will work.

    Cheers

    Paul

    Reply
  24. Hello Samir, I hope all is well. I would like to know why excel runs very slow when we run google intraday sheet with 50 scrips and try to download 1 min data for last 15 days. I came to know about a python script that does the same job in just few seconds. Why does excel take so much of time? Is there any way that we can make this excel also to run quickly?

    Reply

Leave a Comment

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