Full ASX Excel Spreadsheet

deandob · 4 November 2017

ahly92 said:
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.

Trendnomics · 4 November 2017

deandob said:
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've spent some time today, modifying my trade tracking sheets to correctly interface with an alternative free API data service provider (https://www.alphavantage.co). My VBA function to obtain current share prices as follows (it's a little crude, could be refined using JSON parsing):

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

Notes:

XXXXXXXXXXXX to be replaced by your own API key (this can be obtained for free)
Function to be used in Excel as follows: StockQuote("XXX.AX")

deandob · 5 November 2017

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))

Trendnomics · 5 November 2017

deandob said:
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))

The WEBSERVICE function is only available in Excel 2013 & 2016. To ensure backwards compatibility and the fact that my trading partner is using Excel 2007, I've opted for a VBA function instead.

pixel · 5 November 2017

deandob said:
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.

El Dufus · 8 November 2017

pixel said:
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.

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.

pixel · 8 November 2017

El Dufus said:
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.

No problem, mate.
The Market was becoming boring anyway

I've added a few more columns while I was at it.

Cheers, Pixel.

El Dufus · 10 November 2017

pixel said:
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

El Dufus · 13 November 2017

El Dufus said:
Thanks Pixel,
I will try to update my spreadsheet over the weekend.
If all goes well, you earn my great appreciation.
El Dufus

All done (after a while!), and it works well, if a bit slow.

Thanks indeed for your assistance, and let's hope your current provider does not do the same as Yahoo.

El Dufus

twjholmes · 4 January 2018

pixel said:
No problem, mate.
The Market was becoming boring anyway

I've added a few more columns while I was at it.

Cheers, Pixel.

Hi 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

pixel · 4 January 2018

twjholmes said:
Hi 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

Sorry, Tom
the macro can only extract what's displayed on the Bigcharts website.

POR930 · 7 March 2019

pixel said:
No problem, mate.
The Market was becoming boring anyway

I've added a few more columns while I was at it.

Cheers, Pixel.

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

Newt · 7 March 2019

Sadly pixel is no longer with us

https://www.aussiestockforums.com/threads/vale-pixel.33977/

MarketMatters · 8 March 2019

Following Pixel's valuable contributions will no longer be provided there are various other models using Google Finance, which can be found with a simple web search. Example https://office-watch.com/2016/excel-stock-prices-from-google-finance/

* In the event you become stuck and there is no one to assist your model.

Joshhhy · 31 March 2020

Sorry to gatecrash this thread. I'm new to Excel & stock portfolio management. I have Excel 2016 and use the Stock Connector add in to track U.S. stocks. Is there a similar way to keep track of Aussie shares and have them auto-update every 15 mins ?

Pixel's spreadsheet doesn't seem to work anymore (BigCharts error)

kamakaze · 19 April 2020

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

Linehonours · 25 April 2021

kamakaze said:
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

Sorry to hear of Pixel's passing, and how time is such a finite resource.

Happy to report that the Excel file at https://www.aussiestockforums.com/threads/full-asx-excel-spreadsheet.27908/post-964617 works beautifully for me - Thanks Pixel.

The Yahoo API and the GoogleFinance() function in Sheets have both been unreliable over the last year or so, and other API providers are either not Free, or provide partly inaccurate ASX data. Not sure what the wrong prices are about, possibly they are aggregating Chi-X data or something?

Anyway GLTA, stay safe
lh

Full ASX Excel Spreadsheet

deandob

Trendnomics

deandob

Trendnomics

pixel

DIY Trader

Attachments

El Dufus

pixel

DIY Trader

Attachments

El Dufus

El Dufus

twjholmes

pixel

DIY Trader

POR930

Newt

MarketMatters

Joshhhy

kamakaze

Linehonours

Similar threads

Connect with us