Excel Guru?

Also see this related thread on Excel VBA (http://www.aussiestockforums.com/forums/showthread.php?t=1316&highlight=excel)
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?

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.

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

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?

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

Ok. I'll give it a go when i get a minute.

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

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

Rembrandt

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

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

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

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

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

Joe are we able to have a function to attach EXCEL ???

Joe are we able to have a function to attach EXCEL ???
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.

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

Feedback is welcome....

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

cheers
TJ

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:
http://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.

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

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:

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.

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

Maximum .xls filesize now modified to 300K.

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 rembrandt- got that no problems ;)

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

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.

This is the link to the video tutorials from the Van Tharp Institute (http://www.yourproductteam.com/vti/tutorials.htm)referred to in the Van Tharp thread (http://www.aussiestockforums.com/forums/showthread.php?t=846&highlight=van+tharpand) 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.

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

here's another spreadsheet for those interested.


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


cheers,

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.

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

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.

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

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

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

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.

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

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

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

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 :eek: and possibly cause your computer to crash :D

TJ

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.

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

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.

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: http://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.........

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

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.

Hello,
Are you still working on making this available?
Thanks,
Peter

Pete,

The spreadsheet that is posted in this thread (sharesVBA.xls) will not currently work properly - as yahoo have changed the format of their website (so it can't update prices properly). I have modified the one that I use so there isnt automatic price updating - I have been using it for a long time without problems, I may eventually get around to fixing it so that automatic price updates work again but not in the near future.

I can post the one I currently use if you want.

TJ

TjamesX, That would be good,thanks.I am hopeless with excel.
Cheers,
Peter

Here is a simple spreadsheet i have put together. Let me know if you want me to try and add something or need help on how it works.

Thank you very much, I shall check it out.
Cheers,
Peter

My offer for developing the Portfolio manager is still open but people haven't really been putting forward any ideas. I can make a simple portfolio manger which tracks buy, sell, dividends, dividend reinvestments but that would make the program the same as all others out there and why reinvent the wheel. What i was really thinking was that you were going to give me ideas and explanations on handling merges short selling and so forth.

Counting the number of days a stock has been held and then calculate necessary Tax on that according to a formula template which can be adjusted for different countries and so forth. These are the sort of ideas i need.

But currently I'm working on a pattern scanning program as there aren't many available and the ones that are out there are so bad that i can't even believe people pay for them let alone pay $400. Anyway here is a very early screen shot if your interested in it. A Beta will be out in two weeks or so. If you want to Beta test, sign up now because i'm only going to give 3 licenses away (but be prepared to give alot of feedback and bug reports).

My offer for developing the Portfolio manager is still open but people haven't really been putting forward any ideas. I can make a simple portfolio manger which tracks buy, sell, dividends, dividend reinvestments but that would make the program the same as all others out there and why reinvent the wheel. What i was really thinking was that you were going to give me ideas and explanations on handling merges short selling and so forth.


Hi Phoenix,
The key is making things simpler and easier to use, even if it duplicates what current software does, one thing you could do is look at Lally's book 'Mastering Risk' and see if you can incorporate some of his spreadsheets for trade management (eg pyramiding) and position sizing so that it's all in one for ease of use. If you can include essential trade stats like in TradeSim (eg max time in trade, number of losing trades, average loss etc to be abe to calculate expectancy and risk that'll be great, I've mentioned these things before in these excel/portfolio software threads, see threads in relaton to Van Tharp as well). If data can be filed by financial year and/or calendar year that'll help too.

We don't need too much functionality or it may get complicated. Sorry I can't be more helpful at present, still figuring out what I need myself but my focus is on money/risk mgmt stats and simple, efficient record keeping for tax.

Thanks again for the offer, if you can pull something together that really works for the demanding folk here I reckon you can sell it too if you need to. A clear, clean interface will help as well, see what you can do.

Mate I will try it out, because if I can use it , it must be simple!!
Thanks,
Peter

Pete i gather you are interested in beta testing my pattern scanner program? If you are then welcome aboard, soon as it is finished you will get the first copy before its released! My website should be up soon so for others who are interested and want to get a demo it should be up so stay tuned.

Pete i gather you are interested in beta testing my pattern scanner program? If you are then welcome aboard, soon as it is finished you will get the first copy before its released! My website should be up soon so for others who are interested and want to get a demo it should be up so stay tuned.

Yes I'll Beta test it! thanks mate I will see if I can blow it up, which should be no problems for me.
Cheers,
Peter