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

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    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.
     

    Attached Files:

    Tpbal and khtolg like this.
  2. burglar

    burglar

    Posts:
    3,661
    Likes Received:
    2
    Joined:
    Nov 22, 2010
    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.
     
    turokMc2 likes this.
  3. WillyWonka

    WillyWonka

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

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

    burglar

    Posts:
    3,661
    Likes Received:
    2
    Joined:
    Nov 22, 2010
    I feel misunderstood. :eek:
     
  5. banco

    banco

    Posts:
    1,059
    Likes Received:
    1
    Joined:
    Oct 6, 2008
    Given the liquidity of shares outside the ASX 300 it hardly seems worth looking at them.
     
  6. WillyWonka

    WillyWonka

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

    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 :)
     
  7. burglar

    burglar

    Posts:
    3,661
    Likes Received:
    2
    Joined:
    Nov 22, 2010
    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. :2twocents
     
  8. WillyWonka

    WillyWonka

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

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

    how often would these 6 change ?
     
  9. burglar

    burglar

    Posts:
    3,661
    Likes Received:
    2
    Joined:
    Nov 22, 2010
    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!
     
  10. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    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)".
     
  11. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    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.
     
  12. Skate

    Skate

    Posts:
    1,367
    Likes Received:
    5,298
    Joined:
    Dec 28, 2013
  13. pixel

    pixel DIY Trader

    Posts:
    5,359
    Likes Received:
    319
    Joined:
    Feb 3, 2010
    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.
     
  14. WillyWonka

    WillyWonka

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

    :) 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:)
     
  15. burglar

    burglar

    Posts:
    3,661
    Likes Received:
    2
    Joined:
    Nov 22, 2010
    Sorry! It did make sense to me at time of writing.
     
  16. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
  17. WillyWonka

    WillyWonka

    Posts:
    36
    Likes Received:
    3
    Joined:
    Jan 11, 2014
    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
     

    Attached Files:

  18. Craton

    Craton Mostly passive, contrarian.

    Posts:
    640
    Likes Received:
    41
    Joined:
    Feb 6, 2013
    Thanks for sharing Willy, good work I like it. :xyxthumbs
     
  19. Valued

    Valued

    Posts:
    472
    Likes Received:
    10
    Joined:
    Aug 31, 2013
    That's pretty cool. Thanks.
     
  20. waimate01

    waimate01

    Posts:
    205
    Likes Received:
    1
    Joined:
    Nov 22, 2010
    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)
     
Loading...

Share This Page