Excel Guru?

[The Barbarian Investor]

Re: Excel Guru ?

Thanks rembrandt- got that no problems

 

[RichKid]

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

Hi TJ,
Just trying it out now, looks great, the buttons are very good, that's the type of thing we need to make it easier to use. Had trouble figuring out the abreviations but got over that pretty quick. Let's see how I go...

 

[RichKid]

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

Thanks for the file Rembrandt, downloaded fine for me. Currently fiddling with it, thanks very much for making it available.

Also look forward to seeing your website when it's done, I loved the colourful show at the start.

 

[RichKid]

Re: Excel Guru ?

This is the link to the video tutorials from the Van Tharp Institute referred to in the Van Tharp thread and in Tech's thread (mentioned earlier). They are EXCELLENT tutes imho. I've posted the link here to give the Excel Gurus an idea of some of the things which are important and require calculation. It also explains the concepts (briefly) behind position sizing, something any programmer will have to know to design a good tool or system for our purposes.

 

[The Barbarian Investor]

Re: Excel Guru ?

I had a very simple sheet as i'm no guru when it comes to formulae

 

[Stan 101]

Re: Excel Guru ?

here's another spreadsheet for those interested.

ftp://kandn@members.iinet.net.au/

cheers,

 

[clowboy]

Re: Excel Guru ?

TjamesX

Great program....thanx.

Couple of thoughts.....

1) it doesn't apear to allow for franking credit in the dividend section.

2) Do you know of any method to "update" the share price of your portfolio without manually entering the data?

I downloaded a program that "auto updates" share prices but once you sell the shares it drops of your return lising.

Also some totals for profit/loss and return would be great.

 

[TjamesX]

Re: Excel Guru ?

Bit of an update..

I haven't been able to do anything for about a week (work and study and footy and GF) and won't be able to do any more until next week...

I'm hoping to get the basic excel program working by the end of next week, to a piont where I am happy with it for entering all share holdings, buy/sells, profit etc

TJ

 

[RichKid]

Re: Excel Guru ?

Bit of an update..

I haven't been able to do anything for about a week (work and study and footy and GF) and won't be able to do any more until next week...

I'm hoping to get the basic excel program working by the end of next week, to a piont where I am happy with it for entering all share holdings, buy/sells, profit etc

TJ

TJ,

If you want to see what happens when you really get into Excel see what the guy who created this did http://www.stideas.com/free_trading_tools.htm (bottom link). Personally using it for MACD and similar indicators is a waste of time when you can get free charting software that does it BUT it does help the mathematically inclined to understand the technical basis of indicators (and programming).

We're better off on our track here in this thread just trying to track the portfolio numbers for profitability and money mgmt.

This thread is becoming a very handy one, nice to see everyone helping out. Moreover, once we realise what we really need in portfolio mgmt software we're in a better position to choose a suitable software package once we decide to upgrade (if needed).

NOTE: That site also has some great Fibbonacci calc's and other tools, worth a look, I've already downloaded the Fib calcs, very handy if your charting software doesn't have it.

 

[TjamesX]

Re: Excel Guru ?

If you want to see what happens when you really get into Excel see what the guy who created this did http://www.stideas.com/free_trading_tools.htm (bottom link). Personally using it for MACD and similar indicators is a waste of time when you can get free charting software that does it BUT it does help the mathematically inclined to understand the technical basis of indicators (and programming).

Well, the guy who wrote that has put a crap load of time into that one!! One for the maths and stats heads for sure....

But I do agree, the usefulness of doing something like that with free charting software around now is probably not that great.

Thats why I am definitely aiming at doing something that just tracks and manages buy/sells and portfolio position etc. Once you have those basics going - we can add statistical stuff and analysis to a persons trades.

TJ

 

[RichKid]

Re: Excel Guru ?

Well, the guy who wrote that has put a crap load of time into that one!! One for the maths and stats heads for sure....

But I do agree, the usefulness of doing something like that with free charting software around now is probably not that great.

Thats why I am definitely aiming at doing something that just tracks and manages buy/sells and portfolio position etc. Once you have those basics going - we can add statistical stuff and analysis to a persons trades.

TJ

Hi TJ,
Spot on there, it's easy to get distracted and spend hours on fancy charting and tinkering when the essence is managing the portfolio and numbers. Good point you make about recording the essential raw data first in a practical, user friendly format and then analysing it second to extract the secondary data needed for trade management.

 

[TjamesX]

Re: Excel Guru ?

A question to anyone regarding recording P/L on trades;

How do you account for brokerage?? Do you include it in the P/L for each trade separately - in which case it gets complicated if for example your sale parcel is not the same as you buy parcel as you have to aportion brokerage according to the parcel sizes. Currently I have been doing this method - so if the sale parcel is less than the buy parcel, the brokerage for the buy parcel is adjusted down and accounted for in that trade separately - then if you sell the rest of the holding the remaining brokerage is accounted for in that trade......

But I realised that because brokerage is an upfront cost in the trade that it would be tax deductable before any P/L occurs on the trade... Is this right??? If so you can deduct all of your brokerage in a tax year - no matter if you have sold the relevant shares? If this is the case then it would probably be better to leave it out for calculation of P/L on specific trades and just record the total brokerage that has been paid for all trades during the relevant year.

??

TJ

 

[GreatPig]

Re: Excel Guru ?

TJ,

By my understanding, it depends on whether you're classed as a share investor or a share trader (ie. operating a trading business).

As an investor, I believe brokerage is not deductible at all, but rather forms part of the cost base on sale (ie. it's a capital cost).

As a business trader, I believe it's deductible as an expense in the year it's incurred.

However, I'm not an accountant and this is just my understanding.

See attached document regarding the business trading part of it.

Cheers,
GP

 

[TjamesX]

Re: Excel Guru ?

Thanks GP,

So essentially if it is not treated as a business then the brokerage should be calculated as part of the capital gain/loss for each trade. Which is the way I have been calculating it currently I believe.

If its treated as a business then the second method should be used - deducting brokerage upfront as a cost of business.

Cheers,
TJ

 

[TjamesX]

Re: Excel Guru ?

Well after much delay....

I have an updated version of the excel spreadsheet!! It is still very much in construction phase - so it is definitly use at your own risk at this stage.

The functions that work so far are buying, selling and importing data. Currently I am entering in my contract notes for the past year to test how everything is going and so far so good.... so I am happy to use it now for my purposes.

The accounts page does not work as yet, and this will happen in the near future. Same with the dividends function. I am also aware of a couple of bugs that exist;

- Limited number of buy and sells for the same share code
- Selling out of a share holding and then buying back in will cause errors/funny behaviour

I know how these can be fixed.

In the meantime have a play, if you chose to enter actual data - it will be captured on the data sheet, this can then be used to reimport the data once I update to a new version. The format is the same for import and data sheets so you can play around with putting data directly into the import sheet (make sure you use 'default' as the Acct) and then hit the import button on the main page - this function can be used as an easy way to load historical data.

Note: dont start inserting rows/data directly into any of the sheets (except import) as this could cause problems and possibly cause your computer to crash

TJ

 

[Phoenix]

I have a better Idea!!!

You people design the software and being a computer science student i will write it in .net and give you, all here who have shown interest and contributed, free licenses to it. I already have made a few libraries to read Metastock Data and ASCII data etc.....so with all your help i can make something very professional and best of all it will be basically built by the users for the users. If your all intrested, I'm ready to begin and start programming.

 

[pete152]

Re: I have a better Idea!!!

You people design the software and being a computer science student i will write it in .net and give you, all here who have shown interest and contributed, free licenses to it. I already have made a few libraries to read Metastock Data and ASCII data etc.....so with all your help i can make something very professional and best of all it will be basically built by the users for the users. If your all intrested, I'm ready to begin and start programming.

Well I am interested as the only thing I can do on a computer is use the mouse!!
Thanks for all the interesting sheets.
Peter

 

[RichKid]

Re: I have a better Idea!!!

You people design the software and being a computer science student i will write it in .net and give you, all here who have shown interest and contributed, free licenses to it. I already have made a few libraries to read Metastock Data and ASCII data etc.....so with all your help i can make something very professional and best of all it will be basically built by the users for the users. If your all intrested, I'm ready to begin and start programming.

That's a great idea Phoenix, and glad to see the cooperation continuing after TJ's efforts to get this far. Maybe chatting with TJ on his latest improvements and experiences may help to get things going. I'll do some thinking and see what further suggestions I've got. See the references in the current thread to similar threads for some ideas in the meantime Phoenix.

Maybe people who use commercial software for this can share their pet hates so we can address those issues in our version.

 

[TjamesX]

Re: I have a better Idea!!!

That's a great idea Phoenix, and glad to see the cooperation continuing after TJ's efforts to get this far. Maybe chatting with TJ on his latest improvements and experiences may help to get things going. I'll do some thinking and see what further suggestions I've got. See the references in the current thread to similar threads for some ideas in the meantime Phoenix.

Maybe people who use commercial software for this can share their pet hates so we can address those issues in our version.

Cheers Rich, I haven't changed anything with my excel program for ages (note to anyone - don't download the one in this thread the latest one was in another thread: https://www.aussiestockforums.com/forums/showthread.php?t=1316). At the moment the automatic updating of prices doesn't work as yahoo changed the format of their websites last week.... but I've been happily using it so far to track all my holdings and trades - it will be very useful when I get around to tax time.

However i don't envisage making any great additions in the future as it does pretty much what I want for the time being.

On to Pheonix - you're a brave man . From what I gather, a lot of people seemed to lack a comprehensive way of tracking their portfolio and analysing their trades without wanting to purchase the expensive software. I don't see much point in creating a full blown charting and TA tool as Amibroker and the likes seem to do this for a few hundred and to recreate that stuff would take 100's of hours - I reckon concentrate on the portfolio side of things and analysis of trades would be a good start..... but I'm sure a lot of other people have some good ideas of what they would want

so.........

 

[pete152]

Re: Excel Guru ?

Gidday,
How is all the work going you excel gurus??
Peter