# 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

burglar said:


> 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

WillyWonka said:


> 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

burglar said:


> 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

WillyWonka said:


> 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

burglar said:


> 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

WillyWonka said:


> 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

pixel said:


> 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

WillyWonka said:


> 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

pixel said:


> 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

pixel said:


> 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

burglar said:


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


----------



## WillyWonka

waimate01 said:


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





Thanks waimate01,

I really was surprised that I could not find something similar posted somewhere on the net. I didn't even think of using a Google spread sheet, nice idea, I agree 100% with you that it would be a better option to use java than the VBA that excel uses. I really do dislike VBA  I call it "coding for morons"


----------



## SuperGlue

WillyWonka said:


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




Will,

Would be ideal if the last two ex-dividend dates can be added.
Good for dividend stripping. 
At least then have a rough idea when the next dividend can be expected.

SGlue



Craton said:


> Thanks for sharing Willy, good work I like it.




+1


----------



## WillyWonka

SuperGlue said:


> Will,
> 
> Would be ideal if the last two ex-dividend dates can be added.
> Good for dividend stripping.
> At least then have a rough idea when the next dividend can be expected.
> 
> SGlue
> 
> 
> 
> +1





SGlue,

These are the columns that can be added from yahoo finance with minimal effort, take a look and let me know 

    SpecialTags(0) = "a": Tag_Name(0) = "Ask"
    SpecialTags(1) = "a2": Tag_Name(1) = "Average Daily Volume"
    SpecialTags(2) = "a5": Tag_Name(2) = "Ask Size"
    SpecialTags(3) = "b": Tag_Name(3) = "Bid"
    SpecialTags(4) = "b2": Tag_Name(4) = "Ask (Real-time)"
    SpecialTags(5) = "b3": Tag_Name(5) = "Bid (Real-time)"
    SpecialTags(6) = "b4": Tag_Name(6) = "Book Value"
    SpecialTags(7) = "b6": Tag_Name(7) = "Bid Size"
    SpecialTags(8) = "c": Tag_Name(8) = "Change & Percent Change"
    SpecialTags(9) = "c1": Tag_Name(9) = "Change"
    SpecialTags(10) = "c3": Tag_Name(10) = "Commission"
    SpecialTags(11) = "c6": Tag_Name(11) = "Change (Real-time)"
    SpecialTags(12) = "c8": Tag_Name(12) = "After Hours Change (Real-time)"
    SpecialTags(13) = "d": Tag_Name(13) = "Dividend/Share"
    SpecialTags(14) = "d1": Tag_Name(14) = "Last Trade Date"
    SpecialTags(15) = "d2": Tag_Name(15) = "Trade Date"
    SpecialTags(16) = "e": Tag_Name(16) = "Earnings/Share"
    SpecialTags(17) = "e1": Tag_Name(17) = "Error Indication (returned for symbol changed / invalid)"
    SpecialTags(18) = "e7": Tag_Name(18) = "EPS Estimate Current Year"
    SpecialTags(19) = "e8": Tag_Name(19) = "EPS Estimate Next Year"
    SpecialTags(20) = "e9": Tag_Name(20) = "EPS Estimate Next Quarter"
    SpecialTags(21) = "f6": Tag_Name(21) = "Float Shares"
    SpecialTags(22) = "g": Tag_Name(22) = "Day's Low"
    SpecialTags(23) = "h": Tag_Name(23) = "Day's High"
    SpecialTags(24) = "j": Tag_Name(24) = "52-week Low"
    SpecialTags(25) = "k": Tag_Name(25) = "52-week High"
    SpecialTags(26) = "g1": Tag_Name(26) = "Holdings Gain Percent"
    SpecialTags(27) = "g3": Tag_Name(27) = "Annualized Gain"
    SpecialTags(28) = "g4": Tag_Name(28) = "Holdings Gain"
    SpecialTags(29) = "g5": Tag_Name(29) = "Holdings Gain Percent (Real-time)"
    SpecialTags(30) = "g6": Tag_Name(30) = "Holdings Gain (Real-time)"
    SpecialTags(31) = "i": Tag_Name(31) = "More Info"
    SpecialTags(32) = "i5": Tag_Name(32) = "Order Book (Real-time)"
    SpecialTags(33) = "j1": Tag_Name(33) = "Market Capitalization"
    SpecialTags(34) = "j3": Tag_Name(34) = "Market Cap (Real-time)"
    SpecialTags(35) = "j4": Tag_Name(35) = "EBITDA"
    SpecialTags(36) = "j5": Tag_Name(36) = "Change From 52-week Low"
    SpecialTags(37) = "j6": Tag_Name(37) = "Percent Change From 52-week Low"
    SpecialTags(38) = "k1": Tag_Name(38) = "Last Trade (Real-time) With Time"
    SpecialTags(39) = "k2": Tag_Name(39) = "Change Percent (Real-time)"
    SpecialTags(40) = "k3": Tag_Name(40) = "Last Trade Size"
    SpecialTags(41) = "k4": Tag_Name(41) = "Change From 52-week High"
    SpecialTags(42) = "k5": Tag_Name(42) = "Percebt Change From 52-week High"
    SpecialTags(43) = "l": Tag_Name(43) = "Last Trade (With Time)"
    SpecialTags(44) = "l1": Tag_Name(44) = "Last Trade (Price Only)"
    SpecialTags(45) = "l2": Tag_Name(45) = "High Limit"
    SpecialTags(46) = "l3": Tag_Name(46) = "Low Limit"
    SpecialTags(47) = "m": Tag_Name(47) = "Day's Range"
    SpecialTags(48) = "m2": Tag_Name(48) = "Day's Range (Real-time)"
    SpecialTags(49) = "m3": Tag_Name(49) = "50-day Moving Average"
    SpecialTags(50) = "m4": Tag_Name(50) = "200-day Moving Average"
    SpecialTags(51) = "m5": Tag_Name(51) = "Change From 200-day Moving Average"
    SpecialTags(52) = "m6": Tag_Name(52) = "Percent Change From 200-day Moving Average"
    SpecialTags(53) = "m7": Tag_Name(53) = "Change From 50-day Moving Average"
    SpecialTags(54) = "m8": Tag_Name(54) = "Percent Change From 50-day Moving Average"
    SpecialTags(55) = "n": Tag_Name(55) = "Name"
    SpecialTags(56) = "n4": Tag_Name(56) = "Notes"
    SpecialTags(57) = "o": Tag_Name(57) = "Open"
    SpecialTags(58) = "p": Tag_Name(58) = "Previous Close"
    SpecialTags(59) = "p1": Tag_Name(59) = "Price Paid"
    SpecialTags(60) = "p2": Tag_Name(60) = "Change in Percent"
    SpecialTags(61) = "p5": Tag_Name(61) = "Price/Sales"
    SpecialTags(62) = "p6": Tag_Name(62) = "Price/Book"
    SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date"
    SpecialTags(64) = "r": Tag_Name(64) = "P/E Ratio"
    SpecialTags(65) = "r1": Tag_Name(65) = "Dividend Pay Date"
    SpecialTags(66) = "r2": Tag_Name(66) = "P/E Ratio (Real-time)"
    SpecialTags(67) = "r5": Tag_Name(67) = "PEG Ratio"
    SpecialTags(68) = "r6": Tag_Name(68) = "Price/EPS Estimate Current Year"
    SpecialTags(69) = "r7": Tag_Name(69) = "Price/EPS Estimate Next Year"
    SpecialTags(70) = "s": Tag_Name(70) = "Symbol"
    SpecialTags(71) = "s1": Tag_Name(71) = "Shares Owned"
    SpecialTags(72) = "s7": Tag_Name(72) = "Short Ratio"
    SpecialTags(73) = "t1": Tag_Name(73) = "Last Trade Time"
    SpecialTags(74) = "t6": Tag_Name(74) = "Trade Links"
    SpecialTags(75) = "t7": Tag_Name(75) = "Ticker Trend"
    SpecialTags(76) = "t8": Tag_Name(76) = "1 yr Target Price"
    SpecialTags(77) = "v": Tag_Name(77) = "Volume"
    SpecialTags(78) = "v1": Tag_Name(78) = "Holdings Value"
    SpecialTags(79) = "v7": Tag_Name(79) = "Holdings Value (Real-time)"
    SpecialTags(80) = "w": Tag_Name(80) = "52-week Range"
    SpecialTags(81) = "w1": Tag_Name(81) = "Day's Value Change"
    SpecialTags(82) = "w4": Tag_Name(82) = "Day's Value Change (Real-time)"
    SpecialTags(83) = "x": Tag_Name(83) = "Stock Exchange"


----------



## pixel

SuperGlue said:


> Will,
> 
> Would be ideal if the last two ex-dividend dates can be added.
> Good for dividend stripping.
> At least then have a rough idea when the next dividend can be expected.




You'll have to pull that information from the ASX: http://www.asx.com.au/asx/markets/dividends.do .
http://www.sharedividends.com.au/subcatDisplay/Dividend+History has even more history, but they charge for it.


----------



## SuperGlue

WillyWonka said:


> SGlue,
> 
> These are the columns that can be added from yahoo finance with minimal effort, take a look and let me know




Willy,

The following addition will be ideal:
SpecialTags(16) = "e": Tag_Name(16) = "Earnings/Share"
SpecialTags(13) = "d": Tag_Name(13) = "Dividend/Share"
SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date"

SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date" (plus 6 months)

"Next Approx. Ex Dividend Date" (6 months later), so have to do a bit of data manipulation.
This will be a look head for anyone looking for dividend.


----------



## WillyWonka

Ok, 

I have added these new tags 
SpecialTags(16) = "e": Tag_Name(16) = "Earnings/Share"
SpecialTags(13) = "d": Tag_Name(13) = "Dividend/Share"
SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date"


Yahoo finance dose not provide more than one ex-dividend date so there is only one in this new sheet, this data needs to be pulled from the asx site which I don't have time to code atm. 

Please can somebody confirm if the this last column "ex-dividend date" is correct as the data it is pulling from Yahoo seems to have some funny dates,  there are a lot of 2014 dates....? I am not confident with the data in the last column, but anyhow here it is.


----------



## pixel

WillyWonka said:


> Ok,
> Please can somebody confirm if the this last column "ex-dividend date" is correct as the data it is pulling from Yahoo seems to have some funny dates,  there are a lot of 2014 dates....? I am not confident with the data in the last column, but anyhow here it is.




Please stand by;
I've transferred the codes off all stocks in your list with a stated Ex-Div date into my exDiv sheet and am now running my usual report that I know I can rely on. As the result will be too big for ASF as attachment, I'll upload it into my web server for anyone to pull it down. 
The URL will be http://rettmer.com.au/TrinityHome/Xamples/ExDiv.xls

OK - done already. The xls has two sheets. Check them both out


----------



## SuperGlue

I did a random check on a few blue chip companies, dividends are spot on & ex-dividend dates are fairly accurate. Difference of 3 to 1 day. Note only some companies put out a financial events calendar.

The dates, if anyone notice is actually for the second half of the financial calendar.

Thanks Willy, appreciate your time & work for doing up the spread sheet for everyone.


Added: See Pixel's webpage


----------



## pixel

pixel said:


> Please stand by;
> I've transferred the codes off all stocks in your list with a stated Ex-Div date into my exDiv sheet and am now running my usual report that I know I can rely on. As the result will be too big for ASF as attachment, I'll upload it into my web server for anyone to pull it down.
> The URL will be http://rettmer.com.au/TrinityHome/Xamples/ExDiv.xls
> 
> OK - done already. The xls has two sheets. Check them both out




I have updated the ExDiv sheets, this time run over the entire ASX, so feel free to download it again.
If you sort the sheet by code, you can also use it as the latest list of ASX-listed companies.


----------



## davidkho

WillyWonka said:


> 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




Hi Willy,

I am unable to downdload your file in excel format. Would appreciate if you could email your excel spreadsheet to my email at davidkho888@gmail.com. 

Regards
Dave


----------



## WillyWonka

davidkho said:


> Hi Willy,
> 
> I am unable to downdload your file in excel format. Would appreciate if you could email your excel spreadsheet to my email at davidkho888@gmail.com.
> 
> Regards
> Dave




For reasons unknown this forum changes the name and extension of all uploaded files. What you need to do is change the extension of the file from "attachment.php" to "whetevername.xls" 

Some users may need to uncheck a box in Windows control panel--> Folder options--> Hide extensions for known file types.


----------



## vrfund

Hi Will

Thankyou so so much for creating this spreadsheet. I have been working for a few days on trying to automate the price data in my spreadsheet before I realised that I was probably trying to re-invent the wheel!! So a little searching and I came across this thread and am now a very happy girl!! 

Pixel thankyou too for your spreadsheet - very useful info esp this time of year! I did try running the update however I assume that the macros are not in the worksheet as I came up with an error in Excel stating that the 'ExDiv_xlsMain' macro was not available. Is there any chance that you would either be able to share the spreadsheet with the macros or else provide a version with updated data (please excuse my ignorance if the version you already shared does have the macros and the problem is at my end).

Thankyou both so much


----------



## WillyWonka

I have had a few PM's stating that this spread sheet is no longer working

The problem is caused by a recent Office update. Here is the fix posted on MS site with an automated installer to delete the required files. http://support.microsoft.com/kb/3025036/EN-US

 You can do it manually if you are that way inclined by following these steps:

 1.Close all Office applications. 
 2.Do a search in Windows Explorer – make sure to include hidden and system files and folders – for *.exd files (note: that’s not *.exe !!) and delete any you find. Make sure you get these: C:\users\username\AppData\Local\Temp\Excel8.0\MSFo rms.exd C:\users\username\AppData\Local\Temp\VBE\MSForms.e xd 
 3.Reboot the computer (this is not always necessary, but probably better safe than sorry) 
 4.Restart your Office apps and test the controls again.


----------



## Skate

pixel said:


> Please stand by;
> I've transferred the codes off all stocks in your list with a stated Ex-Div date into my exDiv sheet and am now running my usual report that I know I can rely on. As the result will be too big for ASF as attachment, I'll upload it into my web server for anyone to pull it down.
> The URL will be http://rettmer.com.au/TrinityHome/Xamples/ExDiv.xls
> 
> OK - done already. The xls has two sheets. Check them both out




Hi Pixel

May I have the Excel Macro to update your spreadsheet please


----------



## pixel

Skate said:


> Hi Pixel
> 
> May I have the Excel Macro to update your spreadsheet please




Hi Skate,

the macro is part of a suite of programs and scripts which I have developed as a line of business; licenses are available for a fee. I have sent you an IM with details.


----------



## wvl1211

Hi Willy
Great idea - but cant get the sheet to work - I have downloaded version 1.2 and applied the Microsoft fixes you suggested but to no avail - it just is not working - do you know why???
Will Van Leeuwen


----------



## WillyWonka

wvl1211 said:


> Hi Willy
> Great idea - but cant get the sheet to work - I have downloaded version 1.2 and applied the Microsoft fixes you suggested but to no avail - it just is not working - do you know why???
> Will Van Leeuwen





Hmmmm..... you are right it doesn't work anymore. Looks like Yahoo have modified their API. I will look into it and see if I can get it working again


----------



## Newt

I found Yahoo portfolio downloads for comma delimited files no longer have a delimiter at the end of each stock "line".  As of about a week ago.  (Assuming that's what the code here used to use)

Haven't been able to find any online reference why it changed, or any comments on it.  
Bit more messing to distinguish end of line from start of next is all that's required.


----------



## WillyWonka

Newt said:


> I found Yahoo portfolio downloads for comma delimited files no longer have a delimiter at the end of each stock "line".  As of about a week ago.  (Assuming that's what the code here used to use)
> 
> Haven't been able to find any online reference why it changed, or any comments on it.
> Bit more messing to distinguish end of line from start of next is all that's required.




Yes thats the problem exactly, that damm comma is now missing and now all data is writting to the first line, F$#@ would they change this


----------



## Newt

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.


----------



## skc

Newt said:


> 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

Newt said:


> 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

kamakaze said:


> 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

kamakaze said:


> 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

jimmywinvests said:


> 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

Djemba said:


> 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

Aussiesteve said:


> 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

WillyWonka said:


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


----------



## deandob

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

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

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

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

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

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

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

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

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

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

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

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

Sadly pixel is no longer with us  

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


----------



## MarketMatters

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

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

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

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


----------

