Looks like the Yahoo API for quotes is offline, possibly forever. I have a spreadsheet that manages my portfolio that relies on the Yahoo Finance feeds which is a problem.Hi Willy...seems to not work now. Comes with the following massege:
"<html><head><title>Yahoo! - 403 Forbidden -- error 403</title></head><body>It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such the service is being discontinued. For all future markets and equities data research please refer to finance.yahoo.com.</body></html>"
Cheers
Looks like the Yahoo API for quotes is offline, possibly forever. I have a spreadsheet that manages my portfolio that relies on the Yahoo Finance feeds which is a problem.
Does anyone else know of a free or cheap ASX data feed that I could use instead? There are a number for the US markets but none that I can find for ASX.
Function StockQuote(strTicker As String)
Dim strURL As String, strCSV As String
' Compile the request URL
strURL = "https://www.alphavantage.co/query?f...AILY&apikey=XXXXXXXXXXXX&datatype=csv&symbol=" & strTicker
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText
Dim rawArray() As String
Dim varArray() As Variant
rawArray = Split(strCSV, ",")
ReDim varArray(LBound(rawArray) To UBound(rawArray))
Dim index As Long
For index = LBound(rawArray) To UBound(rawArray)
varArray(index) = rawArray(index)
Next index
StockQuote = Val(rawArray(9))
Set http = Nothing
End Function
Beautiful, thanks. I did check this provider earlier but was trying .AU and .ASX as the suffix for the ASX not .AX as I didn't see any documentation about the exchange codes - but should have tried .AX as Yahoo Finance uses this.
Also FYI its not necessary to use a macro, Excel has a WEBSERVICES function which is easier to use than a macro as you can extract the value from the datafeed in each Excel cell. Below is an example to extract NAB EoD value (replace <yourAPIKey> with your API key):
=NUMBERVALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&apikey=<yourAPIKEY>&datatype=csv&symbol=NAB.AX&outputsize=compact"),49,6))
I have just updated my Portfolio Manager, gone to Bigcharts. Attached is the essential macro code, complete with URL. No API needed.Looks like the Yahoo API for quotes is offline, possibly forever. I have a spreadsheet that manages my portfolio that relies on the Yahoo Finance feeds which is a problem.
Does anyone else know of a free or cheap ASX data feed that I could use instead? There are a number for the US markets but none that I can find for ASX.
I have just updated my Portfolio Manager, gone to Bigcharts. Attached is the essential macro code, complete with URL. No API needed.
Note: It's set up for Australian FPO shares only. No options or warrants.
No problem, mate.Hi Pixel,
I was horrified when Yahoo ceased its service, so your post has been a Godsend to me. I am also a DIY (super fund), and trade reasonably frequently. I really need to have a price feed into my Excel worksheet, but not instantaneous updating.
I am pushing 70yo, and my computer ability is next to nil, so could I ask a favour, or at the very least some advice?
Is it possible to include the day's share movement into your spreadsheet?
If you instruct me, I am happy to code the macro, but would you be able to revise your spreadsheet to include the day's price movement?
If not, I accept that decision, as I know how these requests can grow to be inconvenient.
Thanks for a very helpful post.
Thanks Pixel,No problem, mate.
The Market was becoming boring anyway
I've added a few more columns while I was at it.
Cheers, Pixel.
Thanks Pixel,
I will try to update my spreadsheet over the weekend.
If all goes well, you earn my great appreciation.
El Dufus
No problem, mate.
The Market was becoming boring anyway
I've added a few more columns while I was at it.
Cheers, Pixel.
Sorry, TomHi Pixel,
Fantastic spreadsheet and thanks for posting! I too was left in the lurch with the shut down of the Yahoo finance...
One request - any chance of adding a 200 day moving average column?
Thanks again!
-Tom
Hi Pixel, What a great piece of code to extract the prices from the ASX. The ability to add/remove codes as required, and able to link the results to my portfolio in Excel, made life so much easier.No problem, mate.
The Market was becoming boring anyway
I've added a few more columns while I was at it.
Cheers, Pixel.
Sorry to hear of Pixel's passing, and how time is such a finite resource.Hi Joshhhy,
Use link: https://office-watch.com/2016/excel-stock-prices-from-google-finance/ from MarketMatters post of 08/03/2019 to get the google sheet to enable you to get financial data on ASX stocks
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?