Search code examples
ajaxvbareal-timegoogle-finance

crawl realtime google finance price

I want to create a small excel sheet which sort of like Bloomberg's launchpad for me to monitor live stock market price. So far, out of all the available free data source, I only found Google finance provides real time price for a list of exchanges I need. The issue with Google finance is they have already closed down their finance API. I am looking for a way to help me to programmatically retrieve the real price that I circled in chart below to have it update live in my excel.

enter image description here

I have been searching around and to no avail as of now. I read some post here: How does Google Finance update stock prices? but the method suggested in the answer points to retrieving a time series of data in the chart, instead of the live updating price part I need. I have been examining the network communication of the web page in chrome's inspection and didn't find any request that returns the part of real time price I need. Any help is greatly appreciated. some sample codes (can be in other languages other than VBA) would be very beneficial. Thanks everyone !

Solution

  • There are so many way ways to do this: VBA, VB, C# R, Python, etc. Below is a way to download statistics from Yahoo finance.

    Sub DownloadData()

Set ie = CreateObject("InternetExplorer.application")

With ie
    .Visible = True
    .navigate "https://finance.yahoo.com/quote/AAPL/key-statistics?p=AAPL"

    ' Wait for the page to fully load; you can't do anything if the page is not fully loaded
    Do While .Busy Or _
        .readyState <> 4
        DoEvents
    Loop

    ' Set a reference to the data elements that will be downloaded. We can download either 'td' data elements or 'tr' data elements.  This site happens to use 'tr' data elements.
    Set Links = ie.document.getElementsByTagName("tr")
    RowCount = 1

    ' Scrape out the innertext of each 'tr' element.
    With Sheets("DataSheet")
        For Each lnk In Links
            .Range("A" & RowCount) = lnk.innerText
            RowCount = RowCount + 1
        Next
    End With
End With
MsgBox ("Done!!")

End Sub

    I will leave it up to you to find other technologies that do the same. Thing, for instance, R and Prthon can do exactly the same thing, although, the scripts will be a bit different than the VBA scripts that do this kind of work.