Full ASX Excel Spreadsheet

[skc]

Cost cutting? Maybe they were told to conserve commas?
Is a PITA though and hard to see what the advantage is - perhaps makes import to some applications easier. Certainly not for VBA.

Reminds me of this
http://dilbert.com/strip/1996-08-20

 

[Newt]

Good one skc
Gotta love Dilbert.

:jump:

 

[WillyWonka]

Fixed........... Updated to V1.3

Damm those bean counters and their comma cutting

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

 

[WillyWonka]

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.

 

[Newt]

Found this site today while hunting dividend info, if of use preparing stock lists in the future.....

http://www.afrsmartinvestor.com.au//share_table/

 

[Tadas]

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 ?

 

[kamakaze]

Found this site today while hunting dividend info, if of use preparing stock lists in the future.....

http://www.afrsmartinvestor.com.au//share_table/

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.

 

[systematic]

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.

I could be wrong, but from memory they ceased producing them?
Oops - see Ves' post, below. I could've sworn they were cancelling these.

 

[Ves]

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.

All of AFR's various daily / weekly share tables can be found here.

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

 

[jimmywinvests]

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!

 

[WillyWonka]

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!

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.

 

[Aussiesteve]

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!

 

[Aussiesteve]

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

 

[Djemba]

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

 

[WillyWonka]

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

=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".

 

[pixel]

One suggested addition: could add company category column:
Automobiles & Components
Banks

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.

 

[Djemba]

=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".

Thanks for your assistance. By any chance, do you have the file with recent companies entered?

 

[pixel]

All ASX codes with names and GICS

 

[WillyWonka]

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.

 

[ahly92]

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