Keeping a record of automatically updated price quotes from web using VBA and Excel

Question

I want to write a program using Visual Basic that can automatically import data from yahoo finance (price quotes) to an Excel sheet and update every minute. I know how to do this, though,the thing that I’m struggling with is that when excel updates the prices, the previous prices will be erased as Excel re-writes the new prices on the same cells; in other words, I cannot keep a record of quoted prices as time goes by. I wonder if it is possible that the program can write the new prices in different cells as it updates. I am trying to get prices from this source: https://au.finance.yahoo.com/q?s=AUDUSD=X

Answer 1

I assume you are using the front end, currently, to pull data from the web. Instead consider using the Internet Explorer object to fetch the HTML from the page:

'Declare IE object
Dim IE As Object

'Using late binding (instead of tools>>references so you don't get stuck on a version)
'Open IE
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False

'Navigate to your web page so we can get the html
IE.Navigate "https://au.finance.yahoo.com/q?s=AUDUSD=X"

'Wait until its ready to go
Do While IE.busy
    'Update the statusbar that we are waiting
    Application.StatusBar = "Fetching stock price from yahoo"
    DoEvents
Loop

'reset the statusbar
application.statusbar = False

And then use the Microsoft HTML Object Library (in Tools>>References) to parse the html results.

'Declare an HTML document
Dim html As HTMLDocument

'Drop html from IE to the HTMLDoc
Set html = IE.document

'Kill IE, we don't need it anymore
Set ie = Nothing

'Parse the HTML doc...
Dim objElement As HTMLObjectElement
Set objElement = html.getElementById("yfs_l10_audusd=x")

Sheet1.Range("A1").Value = objElement.innerText

Once you get the value you need from the web page you can stick it where ever you like. I have stuck it in Range A1 of Sheet1