Full ASX Excel Spreadsheet

[WillyWonka]

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]

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

Thanks for sharing Willy, good work I like it.

+1

 

[WillyWonka]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.