• 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. skc

    skc Goldmember

    Posts:
    8,277
    Likes Received:
    301
    Joined:
    Aug 12, 2008
    Reminds me of this
    http://dilbert.com/strip/1996-08-20
     
  2. Newt

    Newt

    Posts:
    380
    Likes Received:
    351
    Joined:
    Oct 17, 2012
    Good one skc :)
    Gotta love Dilbert.

    :jump:
     
  3. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    Fixed........... Updated to V1.3

    Damm those bean counters and their comma cutting :D

    Next thing to do is update the company list, the current list is getting a little old now and probably contains a few company codes that no longer exist as well as any new ones that has been added over the last year.

    Right now I have a serious case of CBF'd. If anyone has the energy please feel free to update and upload to this thread :xyxthumbs
     

    Attached Files:

    pixel likes this.
  4. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    Ok I found some motivation and made some improvements.....................Updated to V1.4

    * As of 28/03/15 there are now 2173 companies listed on the ASX which are now all included
    * I have also added a 50 day moving average column to the sheet.
    * Finally I have added a button to unhide all the tabs at once which was a massive pain in the rear to
    do manually each time I make changes.
     

    Attached Files:

  5. Newt

    Newt

    Posts:
    380
    Likes Received:
    351
    Joined:
    Oct 17, 2012
  6. Tadas

    Tadas

    Posts:
    1
    Likes Received:
    0
    Joined:
    Apr 13, 2015
    Hello everyone!

    I am student from Lithuania and I am writing a bachelor thesis about creating investment portfolio from ASX securities. I faced a problem with data and your excel spread would help me a lot. Could someone download me a spread to awKrekas@yahoo.com ?
     
  7. kamakaze

    kamakaze

    Posts:
    1
    Likes Received:
    0
    Joined:
    Feb 21, 2014
    Hi Newt,

    The above link does not appear to be valid anymore, anyone have any ideas what the correct link is now. I tried searching for Share table on web page but found no results.
     
  8. systematic

    systematic

    Posts:
    650
    Likes Received:
    161
    Joined:
    Sep 30, 2012
    I could be wrong, but from memory they ceased producing them?
    Oops - see Ves' post, below. I could've sworn they were cancelling these.
     
  9. Ves

    Ves Beyond Good and Evil

    Posts:
    2,467
    Likes Received:
    51
    Joined:
    Apr 18, 2011
    All of AFR's various daily / weekly share tables can be found here.

    http://www.afr.com/share_tables/
     
  10. jimmywinvests

    jimmywinvests

    Posts:
    2
    Likes Received:
    0
    Joined:
    Jan 11, 2016
    Hi OP
    for some reason this document is a bit patchy and most of the companies have blank fields. Would you happen to have another copy without blank fields? also there are new companies on the ASX so i wanted to update the list.
    (using for educational purposes as I am an stock student)

    Thanks!
     
  11. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014

    The holes in the data are due to a few reasons. The data is sourced from Yahoo finance for free so there are a few glitches in their data from time to time. If say a company dose not have any dividends paid then obviously there will be no data for any of the dividend data types. Like you said the company list is a little old, it was updated in march 2015 so there are going to be old codes of companies that have gone bust and newly listed companies.

    I dont have the time right now to update this sheet. The latest code list is available from the ASX web page here: http://www.asx.com.au/asx/research/ASXListedCompanies.csv

    Updating this list is quite easy all you need to do is click the "unhide all" button and update the code list in each of the hidden sheets. Remember that Yahoo wont allow you to retrieve more than 200 quotes at a time. That is why there are 11 hidden sheets with 200 codes on each.

    Please upload any changes you make to the board for everyone.

    Thanks.
     
  12. Aussiesteve

    Aussiesteve

    Posts:
    38
    Likes Received:
    0
    Joined:
    Jan 22, 2009
    THis is terrific- thanks WW! I am going to use this as a way of making sense of all the ASX data. I'm also adding a "commentary" column for my own use to note key observations on my filtered stocks. Top stuff!:)
     
  13. Aussiesteve

    Aussiesteve

    Posts:
    38
    Likes Received:
    0
    Joined:
    Jan 22, 2009
    One suggested addition: could add company category column:
    Automobiles & Components
    Banks
    Capital Goods
    Class Pend
    Commercial Services & Supplies
    Consumer Durables & Apparel
    Consumer Services
    Diversified Financials
    Energy
    Food, Beverage & Tobacco
    Health Care Equipment & Services
    Household & Personal Products
    Insurance
    Materials
    Media
    Not Applic
    Pharmaceuticals & Biotechnology
    Real Estate
    Retailing
    Semiconductors & Semiconductor Equipment
    Software & Services
    Technology Hardware & Equipment
    Telecommunication Services
    Transportation
    Utilities
     
  14. Djemba

    Djemba

    Posts:
    2
    Likes Received:
    0
    Joined:
    Jan 27, 2017
    Hello,

    I would like the full asx v1.4 to sort companies accordingly, such that the results of subtraction of last from 52 week low are listed in ascending order. Any help will be appreciated. Thank you
     
  15. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    =ABS((E2-P2)/P2*100)
    This is a very simple formula to show the percentage difference between the last price and the 52 week low. Just paste at the top of a free column and then "fill down".
     
  16. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    your list looks pretty much like the GICS. If you download the complete list of ASX stocks from http://www.asx.com.au/asx/research/ASXListedCompanies.csv
    you will get that for every code.
     
  17. Djemba

    Djemba

    Posts:
    2
    Likes Received:
    0
    Joined:
    Jan 27, 2017
    Thanks for your assistance. By any chance, do you have the file with recent companies entered?
     
  18. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    All ASX codes with names and GICS
     

    Attached Files:

  19. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    It was pointed out to me that the sheet was no longer working. Yahoo had updated the URL I was using to download the data. The sheet is now fixed and also has an updated company list.
     

    Attached Files:

  20. ahly92

    ahly92

    Posts:
    1
    Likes Received:
    0
    Joined:
    Oct 31, 2017
    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 :)
     
Loading...

Share This Page