• Australian (ASX) Stock Market Forum

Hello and welcome to Aussie Stock Forums!

To gain full access you must register. Registration is free and takes only a few seconds to complete.

Already a member? Log in here.

Full ASX Excel Spreadsheet

Discussion in 'Trading/Investing Resources' started by WillyWonka, Jan 14, 2014.

sentifi.com

Aussie Stock Forum Sentifi Top themes and market attention on:

  1. deandob

    deandob

    Posts:
    2
    Likes Received:
    2
    Joined:
    Nov 4, 2017
    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.
     
  2. Trendnomics

    Trendnomics

    Posts:
    202
    Likes Received:
    66
    Joined:
    Jan 6, 2016
    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):

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

    deandob

    Posts:
    2
    Likes Received:
    2
    Joined:
    Nov 4, 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 and Habakkuk like this.
  4. Trendnomics

    Trendnomics

    Posts:
    202
    Likes Received:
    66
    Joined:
    Jan 6, 2016
    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.
     
  5. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    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.
     

    Attached Files:

  6. El Dufus

    El Dufus

    Posts:
    3
    Likes Received:
    0
    Joined:
    Jul 7, 2015
    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.
     
  7. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    No problem, mate.
    The Market was becoming boring anyway :p

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

    Cheers, Pixel.
     

    Attached Files:

    Tpbal and hallph like this.
  8. El Dufus

    El Dufus

    Posts:
    3
    Likes Received:
    0
    Joined:
    Jul 7, 2015
    Thanks Pixel,
    I will try to update my spreadsheet over the weekend.
    If all goes well, you earn my great appreciation.
    El Dufus
     
  9. El Dufus

    El Dufus

    Posts:
    3
    Likes Received:
    0
    Joined:
    Jul 7, 2015
    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
     
  10. twjholmes

    twjholmes

    Posts:
    1
    Likes Received:
    0
    Joined:
    Jan 4, 2018
    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
     
  11. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    Sorry, Tom
    the macro can only extract what's displayed on the Bigcharts website.
     
  12. POR930

    POR930

    Posts:
    8
    Likes Received:
    5
    Joined:
    Apr 5, 2012
    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.
     
  13. Newt

    Newt

    Posts:
    381
    Likes Received:
    351
    Joined:
    Oct 17, 2012
  14. MarketMatters

    MarketMatters

    Posts:
    73
    Likes Received:
    36
    Joined:
    Aug 14, 2018
Loading...

Share This Page