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

Excel Guru?

Discussion in 'Software and Data' started by The Barbarian Investor, Mar 11, 2005.

  1. The Barbarian Investor

    The Barbarian Investor

    Posts:
    333
    Likes Received:
    0
    Joined:
    Nov 17, 2004
    Also see this related thread on Excel VBA
    RichKid
    Moderator


    Are any of you EXCEL guru's?

    or know of a program as i was chasing a program to insert shares i bought and sold (plus costs in and out) to keep track of my holdings/ profits/ losses and balance..

    can anyone recommend anything?
     
  2. Profitseeker

    Profitseeker

    Posts:
    374
    Likes Received:
    0
    Joined:
    Jan 30, 2005
    Re: Excel Guru ?

    I think it sounds pretty easy to do. Email me you exact requirements at cybervitara@hotmail.com and i'll try and put it together for you.
     
  3. TjamesX

    TjamesX

    Posts:
    364
    Likes Received:
    0
    Joined:
    Sep 15, 2004
    Re: Excel Guru ?

    I have an excel spreadsheet that monitors all my holdings and buying/selling - but I have to manually work with it. I am thinking of writing some VBA to make it a bit more 'user friendly', when its done i'll keep you posted.

    TJ
     
  4. RichKid

    RichKid PlanYourTrade > TradeYourPlan

    Posts:
    2,981
    Likes Received:
    3
    Joined:
    Jun 18, 2004
    Re: Excel Guru ?

    I've been looking for a similar programme- Comsec does a bit of record keeping but not too much.

    Do some of the charting programmes come with it (eg Amibroker, Metastock??).

    I don't have specific charting software, hence the question. Might save everyone some trouble if the software does everthing from charting to money management. Tech's thread on position sizing had some spreadsheets- maybe that's a good place to look?
     
  5. The Barbarian Investor

    The Barbarian Investor

    Posts:
    333
    Likes Received:
    0
    Joined:
    Nov 17, 2004
    Re: Excel Guru ?

    Profitseeker,

    we should be able to attach it here shouldn't we (under attachments ? Joe ?)

    And that way we can make it interactive with input from everyone.

    I was thinking of a sheet that you can keep track of shares held (i realise the price changes continually but maybe inputting each Friday pm would be a starting point) and showing your stop loss point

    But more importantly to keep track of shares sold, inclusive of entry/exit brokerage and profits/losses position sizes etc..

    At the end of the year you can decide on what to sell on june 30th and worth buying on july 1st and have a great sheet for your accountant (may help prove you're a trader :D )

    Any thoughts or help appreciated
     
  6. Profitseeker

    Profitseeker

    Posts:
    374
    Likes Received:
    0
    Joined:
    Jan 30, 2005
    Re: Excel Guru ?

    Ok. I'll give it a go when i get a minute.
     
  7. The Barbarian Investor

    The Barbarian Investor

    Posts:
    333
    Likes Received:
    0
    Joined:
    Nov 17, 2004
    Re: Excel Guru ?

    I've done up something..it's very basic though, i'll try and attach later

    I was hoping some of the more serious traders would have something of assistance for us all?

    Anyway maybe we can evolve something here
     
  8. rembrandt

    rembrandt

    Posts:
    44
    Likes Received:
    0
    Joined:
    Sep 1, 2004
    Re: Excel Guru ?

    If you are trading stocks you need to be Excel savvy ...

    Your can download an Options trading spreadsheet from my webspace to play with ... (http://www.users.bigpond.com/equus2/trading_book.xls)

    No warranty for error or ommission ...

    Cheers ...
     
  9. The Barbarian Investor

    The Barbarian Investor

    Posts:
    333
    Likes Received:
    0
    Joined:
    Nov 17, 2004
    Re: Excel Guru ?

    Rembrandt

    It asked for my password and username so didnt get to view it?
     
  10. TjamesX

    TjamesX

    Posts:
    364
    Likes Received:
    0
    Joined:
    Sep 15, 2004
    Re: Excel Guru ?

    Well here is the barebones skeleton of something more to come;

    You will need Excel 2000 or later (I think) and you need to enable macros. It is basically a simple way to track holdings and trades (paper or real). Don't convert any of your data into it, there are a lot of bugs in it - just try it out and see what its like.

    Feedback is welcome....

    The basic idea is to have a panel of control buttons at the top where you can do everything, Below that, a summary of all holdings and trades. The sheets are there for detail. There isn't much formatting - so it looks pretty ****ty and some of the data may look wierd as it may not be in the correct cell format.

    I basically started this out to track shares, and I like writing VBA in excel, there are not many features in it yet so I want feedback on what people think would be good - I'm thinking;

    Obviously;

    An overview of profit/loss total
    Bank account balance
    dividends and franking

    Possibly;

    Link share purchases to more than one account
    Buying shares on margin
    charting of total P/L over time
    charting of P/L per month
    other charts
    calculates brokerage for you depending on parameters you provide
    calculate Tax position for year

    For a lot of this stuff people will need to enter a date for every transaction - will people do this????

    cheers
    TJ
     

    Attached Files:

  11. rembrandt

    rembrandt

    Posts:
    44
    Likes Received:
    0
    Joined:
    Sep 1, 2004
    Re: Excel Guru ?

    Hi Barbarian ... username/password a mystery ??? ... should be a simple download situation ... I checked it a couple of times.

    I am more familiar with HC linking of files with HTML ... ASF does not allow?

    I wanted to attach as below ... but .xls files not supported?

    Suggest you go to the link in post above and paste into a separage browser and hit enter ... is should auto download ... just click Save to your HDD.

    If that fails ... give me an email addy (not ASF) and I shall send you the xls file as an attachment.

    Cheers ...
     
  12. DTM

    DTM

    Posts:
    674
    Likes Received:
    1
    Joined:
    Dec 21, 2004
    Re: Excel Guru ?

    Rembrandt, from memory, your profile won't allow us to email you. :confused:
     
  13. The Barbarian Investor

    The Barbarian Investor

    Posts:
    333
    Likes Received:
    0
    Joined:
    Nov 17, 2004
    JOE ?

    Joe are we able to have a function to attach EXCEL ???
     
  14. Joe Blow

    Joe Blow Administrator Staff Member

    Posts:
    5,582
    Likes Received:
    799
    Joined:
    May 28, 2004
    Re: JOE ?

    Done... in less than ten minutes.

    How's that for service? :D

    Maximum file size is set to 100K. Let me know if this is too small. I don't know too much about Excel.
     
  15. RichKid

    RichKid PlanYourTrade > TradeYourPlan

    Posts:
    2,981
    Likes Received:
    3
    Joined:
    Jun 18, 2004
    Re: Excel Guru ?

    Entering figures: if they aren't day traders or weekly traders I doubt it'll be a problem if people are serious about it. It'll have to be tested over time to be made simple and workable. You can always only enter the figures you need and ignore the other functions.

    The other place that has a good sample of money mgmt entries is the Amibroker interface: http://www.amibroker.com/tour/report.html
    If you could include those tools it'll be tops.
    There is also a link on post #61 (of Tech's thread, below) from memory to simple VanTharp money mgmt calculations (see online tutes)- could we include that as a start (ie trade number, loss/profit, positive expectancy etc)?

    Position sizing tools like the ones referred to in tech's thread would be good, I'm still getting my head around it, might be a bit difficult for you to do at this stage but maybe later:
    https://www.aussiestockforums.com/forums/showthread.php?t=717&page=5&pp=10&highlight=position+sizing

    Hope I haven't gone overboard in my wishlist but this type of data mgmt is crucial to success.
     
  16. TjamesX

    TjamesX

    Posts:
    364
    Likes Received:
    0
    Joined:
    Sep 15, 2004
    Re: Excel Guru ?

    RK,

    The basic idea is for it to be used by people who purchase shares on a reasonably regular basis, but not for day traders who are constantly buying/seeling - I am assuming they have management software integrated into their charting software.

    I think the ideas you put forward about Tech's trading analysis, and displaying reports on particular shares is great. These can be added in reasonably easily as functions over time.

    The way forward will be to have a workable program where you can enter you buys and sells, and it will display profit/loss and basic stuff. From here the data entered can be reused when/if I add functions over time for portfolio analysis etc. I will need to add an exporter/importer so that when updates are made you can transfer data across to the updated version.

    I think I could have an initial version for people to enter their trades into maybe in a week.

    Joe,

    You may need to increase limit for excel files up to 300k, otherwise I can probably zip it

    TJ
     
  17. skin

    skin

    Posts:
    46
    Likes Received:
    0
    Joined:
    Dec 29, 2004
    Re: Excel Guru ?

    I would be interested in looking at a completed model of this excel programme - it is a wonderful thing to see people working together. :star: :star: :star:
     
  18. rembrandt

    rembrandt

    Posts:
    44
    Likes Received:
    0
    Joined:
    Sep 1, 2004
    Re: Excel Guru ?

    Hi Barbarian ... I checked the link again today and it works fine ... it's a simple ten second download ... I am using Excel 2002 which is compatible back to '97.

    I put up another file ... (http://www.users.bigpond.com/equus2/tradingbook.xls)

    Out of interest ... does the username/password request you are getting look like a Microsoft .NET form ... if it is, I might send a please explain to MS Newsgroup ... naughty MS.

    JoeBlow ... my .xls file is 185kb and is fully featured so as suggested by others maybe 300kb limit might be the way to go.

    Cheers ... good luck folks ... that is about as much as I can do.
     
  19. Joe Blow

    Joe Blow Administrator Staff Member

    Posts:
    5,582
    Likes Received:
    799
    Joined:
    May 28, 2004
    Re: Excel Guru ?

    Maximum .xls filesize now modified to 300K.
     
  20. rembrandt

    rembrandt

    Posts:
    44
    Likes Received:
    0
    Joined:
    Sep 1, 2004
    Re: Excel Guru ?

    Hi JoeBlow ... thanks for that ...

    I shall attach the .xls file and hopefully everyone will be able to get it.

    Notwithstanding that the link can be pasted into a separate browser to save your bandwidth.

    Cheers ...
     

    Attached Files:

Loading...

Share This Page