Full ASX Excel Spreadsheet

[WillyWonka]

Hello,
I am new to the stock market game and I was looking around for a spread sheet that contained all 2150 ASX listed
companies with detailed updateable information.

After a long internet search I was unable to find what I was looking for so I set about make my own.

There is 1001 web sites that will give you quote information however getting all 2150 companies data into Excel was easier said than done. I decided to use Yahoo Finance as my information source, that main problem was that most of these quote providers have a limit on how many quotes you can receive at once, for or Yahoo this limit is 200.
This hurdle was overcome by adding 200 company codes to 11 hidden sheets and updating the individually. This is all done with a button press that updates each sheet and copies the data to the "ALL" sheet.

The update button is located in the top left and takes around 10-15 seconds for the data to download.

At the moment the columns I have added to the sheet are as follows:
CODE, NAME, LAST TRADE DATE, OPEN, LAST, ASK, BID, HIGH, LOW, CHANGE, DIV YEILD, PE RAITO, AVERAGE DAILY VOL , VOLUME, % CHANGE, 52 LOW, 52 HIGH, MARKET CAPTIAL, BOOK VALUE.

Please let me know if there are any bugs or improvements that could be made, As I am a beginner to this game I don't fully understand all the data types that are available on Yahoo Finance, I have added what I thought was needed.

 

[burglar]

Warren Buffett says that your seventh best idea will not make you wealthy.

So how do you get six good companies?
I would suggest you get a "filter" of sorts to limit the number of companies you look at.

 

[WillyWonka]

Warren Buffett says that your seventh best idea will not make you wealthy.

So how do you get six good companies?
I would suggest you get a "filter" of sorts to limit the number of companies you look at.

Adding a "filter" is up to whoever wants to use this spread sheet. The problem was getting and updating all the data in one spread sheet.

 

[burglar]

Adding a "filter" is up to whoever wants to use this spread sheet. The problem was getting and updating all the data in one spread sheet.

I feel misunderstood.

 

[banco]

Given the liquidity of shares outside the ASX 300 it hardly seems worth looking at them.

 

[WillyWonka]

I feel misunderstood.

I have played around with a few "IF" statements that attempt to filter stocks, they were mainly based around penny shares that were at the bottom of the 52 week run and with decent book value and market cap.

But to be honest I need to to a lot more reading

 

[burglar]

I have played around with a few "IF" statements that attempt to filter stocks, they were mainly based around penny shares that were at the bottom of the 52 week run and with decent book value and market cap.

But to be honest I need to to a lot more reading

Now I feel better.

My mentor had 43 share companies in his portfolio.
I told him to cull them to be more manageable!
He cut it down to 35.
Now 35 was a magic number of companies he could place on a single watch list.

It still had many gold mining juniors. (what I would call duplicates)
He was forever reading, but that was what he chose to do!!

I have six (6) companies.

You may have as many or as few as you wish, just offering my opinion.

 

[WillyWonka]

Now I feel better.

My mentor had 43 share companies in his portfolio.
I told him to cull them to be more manageable!
He cut it down to 35.
Now 35 was a magic number of companies he could place on a single watch list.

It still had many gold mining juniors. (what I would call duplicates)
He was forever reading, but that was what he chose to do!!

I have six (6) companies.

You may have as many or as few as you wish, just offering my opinion.

6...?, what about 7, lucky 7

how often would these 6 change ?

 

[burglar]

6...?, what about 7, lucky 7

how often would these 6 change ?

I am a gambler and an opportunistic trader.
So I would rather not teach you my wurst habits.

When I sell all of my holding, the company goes on my shortlist.
I research it continuously until an other opportunity arises.

Yes. Yes. I sometimes have seven or eight!

 

[pixel]

Whether it makes sense or not to look at 2150 stocks is a moot point, to be decided by every individual for himself.
I do use a "full ASX" watchlist for my MA7 Analyser scans. Whether a particular result suits my purpose at the time is something I can decide there and then when it happens.
If you have already gone through 1001 sites, here is the 1002nd, straight from the Horse's Mouth - or the ASX, for that matter: http://www.asx.com.au/asx/research/listedCompanies.do
It does have a URL link near the top that says "download the complete list (csv)".

 

[WillyWonka]

Whether it makes sense or not to look at 2150 stocks is a moot point, to be decided by every individual for himself.
I do use a "full ASX" watchlist for my MA7 Analyser scans. Whether a particular result suits my purpose at the time is something I can decide there and then when it happens.
If you have already gone through 1001 sites, here is the 1002nd, straight from the Horse's Mouth - or the ASX, for that matter: http://www.asx.com.au/asx/research/listedCompanies.do
It does have a URL link near the top that says "download the complete list (csv)".

This was the 1st web site I visited. I used the above link to get the 2150 ASX company
codes. The ASX website has a end of day csv available to download but it only contains that days
price data ie high, low. The critical data like 52 high/ low , book , pe , yield , market cap are not available from
the ASX.

 

[Skate]

Thanks Willy, I'm impressed..

I use the ASX website - http://www.asx.com.au/asx/research/listedCompanies.do but its so time consuming to view.

Your Excel spreadsheet is of benefit to me.

 

[pixel]

This was the 1st web site I visited. I used the above link to get the 2150 ASX company
codes. The ASX website has a end of day csv available to download but it only contains that days
price data ie high, low. The critical data like 52 high/ low , book , pe , yield , market cap are not available from
the ASX.

OK, I got it: You also want the current end-of-day data plus the entire box'n'dice in a single complete watchlist.
Sorry, but I'm sure you won't find that anywhere for free. It requires quite an effort to collect and update; many data providers do, some better than others; but all I know have it as part of their software package inside something like a watchlist feature, and they will all charge you for it.

You can visit D2MX or Paritech and request a free trial of their software. I use both, Market Analyser and Pulse, and I know they provide more than what you're after.

 

[WillyWonka]

OK, I got it: You also want the current end-of-day data plus the entire box'n'dice in a single complete watchlist.
Sorry, but I'm sure you won't find that anywhere for free. It requires quite an effort to collect and update; many data providers do, some better than others; but all I know have it as part of their software package inside something like a watchlist feature, and they will all charge you for it.

You can visit D2MX or Paritech and request a free trial of their software. I use both, Market Analyser and Pulse, and I know they provide more than what you're after.

yes that is what I set out to make. I think you may have misunderstood me, I have created a spread sheet for download in the first post that has all this data at the click of a button

 

[burglar]

Whether it makes sense or not to look at 2150 stocks is a moot point, ...

Sorry! It did make sense to me at time of writing.

 

[WillyWonka]

Sorry! It did make sense to me at time of writing.

http://www.urbandictionary.com/define.php?term=moot point

 

[WillyWonka]

Just fixed a bug in the bid/ask columns, they were not working last night which I thought was because the market was closed, however it turned out to be a bug in the code

Updated to v1.1

 

[Craton]

Thanks for sharing Willy, good work I like it.

 

[Valued]

That's pretty cool. Thanks.

 

[waimate01]

Willy, well done, good work !

There's no end of spruikers selling secret-sauce that really amounts to not much more than a series of simple filters followed by some judicious eyeballs. The 'moat' that lets them charge so much is their automation. They claim it's not practical for you to trawl through several thousand stocks doing a series of simple calculations on each one. They're relying on people not having the ability to provide their own simple automation. Well done you for bridging their moat !

In my opinion a better place to do this is a Google Spreadsheet rather than Excel. Google can get data from Yahoo, plus also from Google Finance. And you can write server-side javascripts to send you alerts and all sorts of things. Additionally, you can query your spreadsheet using SQL and insert the results, live-linked, into another spreadsheet.

All updating in near-realtime, while your computer is turned off and you're laying on a beach somewhere getting the occasional update via your smartphone, then looking at the detail with Google Docs on your mobile device.

(Google does impose some limits on how many external data sources you can have, but that's easy enough to get around with scripting)