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

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