61

Live Intraday Stock Data in Excel Sheet: Free Download

Posted on by admin

Note: Google finance has deprecated their API’s and hence this excel utility does not pull data anymore. Please find another similar utility based on Alphavantage API here.

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://finance.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

Spreadsheets for Success

Related Posts

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

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

  6. how do i convert it to “day” interval? also how to get key index like “NIFTY” & “NIFTY50” etc.. ??

  7. Wow, i searched the whole internet for free minutely data, thanks a lot and God bless you!

    • Hi Deepu,

      Google recently stopped providing API access to the live data. We are looking for alternate free methods to update this sheet.

  9. Sir, The sheet has stopped working now, after serving for a good period of time, what may be the possible reason of error

    • Hi Manoj,

      Google recently stopped providing API access to the live data. We are looking for alternate free methods to update this sheet.

  13. Awesome website i was looking for could you please let me know the formula to get high and low values at 9:30 am exactly for all futures stocks

    • Hi Tejas,

      This cannot be achieved through a formula. I guess some Excel macro programming would be required.

  14. Hi Admin,,

    I need the sheet only date wise daystart-open,dayend-high,dayend-low not on minutes base, pls advice

    • Hi Simhan,

      We are working to develop similar EOD sheet. Will update you once done

    • Hi Suhas,

      The sheet is available for free download from the link provided in this post

  17. HI.. i need an real time excel for fetching all nse scrips in columns / bt should hv limitations to add and update only my watch list stocks which migh be 100 or 60 etc i have tried but not getting any clue

  18. Very nice work.
    I need just current quotes only – scrip wise.Say a format like this
    SCRIP HIGH LOW CURRENT
    ACC 1800 1750 1760
    IVP 195 185 188
    Is this possible ?
    I can make some modification in your macro by adding a Loop
    but that can be too lengthy as it will need to copy paste data from
    the columns what we get right now. I think there should be some direct way out.
    Request you help,
    Thanks & Regards,
    H A Vakharia

  20. Hi,
    I am a stock trader for Swing trading, Positional trading as well as Intraday trading. I have seen your all documents. Now I need one excel sheet where gets following data as a live update:
    1. Volume Gainer – Also get difference as per previous day volume.
    2. Price Gainer – Also get difference as per previous day price.
    3. Open Interest – Get data for difference as per previous day OI.
    4. Open = Low and Open = High
    5. Delivery Percentage Value on trading day for particular stock.
    6. Stock is trade Above 200 DMA or 50 DMA level
    Also if you think you can add other criteria for screen good stock
    All these information gets from only Nifty 50 stocks.
    How Shall I make this excel sheet or google sheet?
    Please advise me.

    • Hi Joydeep,

      We will try to create an excel sheet for the same and post it soon.

  22. hi,
    excellent sheet. Could you put up one with the volume data as well? and is it possible tohave 51 worksheets, one for each scrip. And without the chart.
    thanks

  23. Great work. Can we get the price updated automatically not having to push the get data bbutton

    • Hi Venugopal,

      Yes, the button click can be automated easily. Will add this request to the enhancement list

  24. Hi Admin,
    is there any way to change numPastTradingDays to just last minute. I mean i just require previous min OHLC data for multiple scripts.. Is it possible. please let me know..

      • Thanks for quick turnaround… Is there any paid service offered by you guys.. If yes is it possible to send me price quotation for the above request.. Share quotation to tradematic @ gmail.com

        • Hi Sudheer,

          Unfortunately we do not offer any paid service as of now. But I have added your request to our enhancement list and will prioritize it soon.

  25. Hi Admin, Thanks for efforts. I request you to help me to know how to get past 7 days data in day range not in minutes.

    • Hi Vikranth,

      We received this request from multiple folks. Our team is working on the same, will keep you posted

  26. Need some advice. The spreadsheet was running fine until a few days ago. It prompted the following message:
    Run-time error ’13’:
    Type mismatch
    When debug is clicked, it prompted the following statement:
    timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) – 1))
    Could anyone kindly advice me how I could get around this?
    Thank you very much.

    • Hi Steven,

      Unfortunately Google finance has decommissioned their API which is causing this issue. We are yet to find a alternate solution.

  27. Noticed this as well, have the exact same problem as Steven..
    Have they decomissioned the whole API entirely ?

    Thanks in advance!

    • Yes it looks like that Joseph. An there was no notification sent. Many industry leading softwares who were dependent on that API are affected due to this.

  28. Hi Sir
    The intraday stock excel sheet is not workings actually while clicking on get data the file displays visual basic error mesage
    It is requested to update the same

    • Hi Dheeraj,

      Unfortunately google has discontinued its intraday data API since Aug 1st, due to which the excel sheet is unable to fetch realtime data. We are working on a alternate solution and will keep you updated. Thanks for your patience.

  30. Sir, your strategies and screenedrs are awesome. Please guide , real time data on intraday , can we fetch 1/5/15 minute period volume in different cells for comparison and volume rise for price volume movement.

  31. Hi, this is a very nice tool. But when I tried it today I got errors saying “cannot download the information that you requested”.
    Have they changed the URL?
    Thanks.

  33. Getting an error on the below line.
    .Refresh BackgroundQuery:=False

    Please help

    • Hi Abhijit,

      Google finance APIs have deprecated, hence the sheet is unable to fetch the prices

  35. Is it possible to include previous day or week data in the table? If so please make an article on that. Thank You

  36. Is it possible to include previous day or week data in this table? If so please make an article on that. Thank You.

  37. HI. plz tell me how can we get delivery presentage of share(nse india)
    one Google sheet (=GOOGLEFINANCE(“NSE:RELIANCE”,”?”)

Leave a Reply

Your email address will not be published.