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


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

  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?

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

  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

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 10 + 3 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)