Live Intraday Stock Data in Excel Sheet: Free Download

Most of the traders prefer Excel sheet for Stock Market analysis. But the real challenge comes when data needs to be manually copied from exchange website or some other sources to Excel sheet. It is really a time consuming process and that’s the reason we have tried to automate it using Excel macros and Google Finance data. The automated Excel sheet can be downloaded from the end of this post. Live Intraday Stock Data in Excel Sheet can be very useful to traders who already have a buy-sell system created, or who wants to create one.

You can also refer some of our very popular Excel based trading systems in the below link:

Excel Based Trading Systems

How to use this Excel Sheet

Step 1: Download the Excel file from the end of this post.

Step 2: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.

Step 3: Input the Symbol Name, Exchange Name, Interval and Number of Days.

Step 4: Click on Get Data button. The data would be automatically downloaded and chart would be refreshed. This data can be downloaded for max 15 days and the minimum interval is 1 minute.

Live Intraday Stock Data in Excel Sheet: Screenshot

Live Intraday Stock Data in Excel Sheet

Chart Screenshot

Macro Definition

This Excel sheet fetches live intraday data from Google Finance using Excel macros. Find the macro definition below:

Option Explicit

Sub LiveData()
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.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set ParameterSheet = Sheets("GetData")
Set DataSheet = Sheets("Data")

DataSheet.Cells.Clear
Range("A2:E10000").Select
Selection.ClearContents
ticker = ParameterSheet.Range("symbol").Value
exchange = ParameterSheet.Range("exchange").Value
interval = ParameterSheet.Range("interval").Value * 60
numPastTradingDays = ParameterSheet.Range("periods").Value

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

QueryQuote:
With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

DataSheet.Columns("A:G").ColumnWidth = 12

'===Convert Google timestamp to Excel timestamp (only for Windows)
Dim timeStamp As Double
Dim timeStampRaw As String
Dim timeZoneOffsetRaw As String
Dim timeZoneOffset As Variant
Dim numRows As Integer
Dim i As Integer
numRows = DataSheet.UsedRange.Rows.Count - 1

timeZoneOffsetRaw = DataSheet.Range("a7")
timeZoneOffset = (Mid(timeZoneOffsetRaw, InStr(timeZoneOffsetRaw, "=") + 1, 10))

For i = 8 To numRows

If Not IsNumeric(DataSheet.Range("a" & i)) Then

timeStampRaw = DataSheet.Range("a" & i)
timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) - 1))
timeStamp = (timeStamp + timeZoneOffset * 60)
DataSheet.Range("g" & i) = timeStamp / 86400 + 25569

Else

DataSheet.Range("g" & i).FormulaR1C1 = "=(RC[-6]*" & interval & "+" & timeStamp & ")/86400+25569"

End If

Next

DataSheet.Range("g8:g" & numRows).NumberFormat = "d mmm yyyy h:mm;@"
DataSheet.Range("G:G").Columns.AutoFit

'''''''''''''''''''''''''''''''''
Dim lrA As Integer

lrA = DataSheet.Range("B" & Rows.Count).End(xlUp).Row

DataSheet.Range("G8:G" & lrA).Copy
Sheet3.Range("A2").PasteSpecial Paste:=xlPasteValues
Sheet3.Range("A2:A" & lrA).NumberFormat = "d mmm yyyy h:mm;@"
Sheet3.Range("A:A").Columns.AutoFit

DataSheet.Range("E8:E" & lrA).Copy
Sheet3.Range("B2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("C8:C" & lrA).Copy
Sheet3.Range("C2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("D8:E" & lrA).Copy
Sheet3.Range("D2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("B8:B" & lrA).Copy
Sheet3.Range("E2").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = xlCopy

'''''''''''''''''''''''''''''''''

Application.Calculation = xlCalculationAutomatic

End Sub




Download link for Live Intraday Stock Data in Excel Sheet

Please see the below link to download Live Intraday Stock Data in Excel Sheet. Mostly everything is self explanatory, but please let us know if you have any queries. Also, send us your feedback on how to improve this sheet.

Live Updating Excel: Download Link

9 Comments

  1. Many thanks for sharing this excellent tool. Please post an article explaining how to get live OHLC data of any given stock in excel similar to the Intraday Open High Low Strategy -Live Signals you have posted.

  2. Thanks for the post.
    Sir can you provide AFL code with backtest data based on GUPPY’s strategy? Please.

  3. Hi,
    Thanks for the post and excel file.
    I tried for Indonesian stocks (BMRI, BBNI) of Indonesia stock exchange (IDX), but a small Microsoft Visual Basic window came out, mentioning
    “Run-time error ’13’: Type mismatch.
    Clicking the debug shows
    “timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) – 1)) being highlighted.
    Can the codes be modified so that the excel file can get Indonesian stock data?
    Thank you.

    • Hi,

      This excel sheet is designed to work for Indian stock markets only. Sorry for the inconvenience.

Leave a Reply

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