Investment portfolio spreadsheets

[LostMyShirt]

I am having real trouble organizing a spreadshet to track my portfolio in order to link the profit/loss and end amont to my business portfolio to give a reflection fo the worth of my total assets.

I have used the link profided in another thread to the Capital Gains template offered by MS Office. However I am having some trouble with formula, and I am not that experienced with formula.

I need it to reflect the following, but can't get it to work properly with currently held investments and already sold investments;

-Available Cash
-on-going cash deposits
-market worth
-total worth
-total profit loss

I just can't get it to work after modifying the template.

A ready mate template would be excellent, but I recon id be pushing my luck there...

 

[LostMyShirt]

I want to attach a copy of the currently modified sheet that I can't get working with very simple formula. If someone can overlook it I would so very appreciative.

 

[skyQuake]

I want to attach a copy of the currently modified sheet that I can't get working with very simple formula. If someone can overlook it I would so very appreciative.

Can you elaborate? Whats not working
Also example of where its not working will help

 

[LostMyShirt]

Ok I'll do my best to describe it - it is pretty hard to explain what I am wanting out of this.

In the total P/L tab when I add in an array of stocks it ends up adding the worth of the stock at the time to the P/L.

For example if I add;

100 units x $10 = $1000 - it will just add the thousand into the P/L even though the stock is at what it costed me ie. $1000. So I purchase for $10 and the stock is worth $10 which means I am dead even - but the P/L will add 1000 and give me a wrong amount.

Also, it gets tricky when I want to add a cell that shows the total portfolio worth with sold stock, held stock and it's current price (I manually update EOD) because, I believe, it is affected by the same reason in the preceeding pararaph.

It gets even worse when I want to track my available cash. For instance, if my total deposits are 6000, and I purchased shares for 6000, but I am down 50% and they are worth 3000, then the available cash should read 0. instead I get a negative amount.

This is a very poor way to describe this, and in all honesty if you could direct me to a paid sheet that has all of those functions, namely tracking your portfolio's total worth and also against limited funds, I would gladly buy it.

I need this because i attach it to my business portfolio P/L to gather what my total worth is.

 

[LostMyShirt]

I ended up finding a very decent template that is not too fancy but works rather well for my needs.

I got it from a subscribtion (FREE) from a bloke who runs Dynamic Markets LLC.

This is much better than what I've created and is suiting me just fine for both paper trading and actual trading in conjunction with my balance sheet for my business.

Now I am able to track my worth with ease.

The sheet will track your progress based on an initial deposit amount, as well as your win/loss ratios and risk over time. It also has a money management calculator which you can use to determine how much stock to buy in a particular company while telling you how much should be bought based on your predetermined risk percentage.

All in all I am rather happy...

 

[skc]

If you want to become a serious trader, review and analysis of your trades are paramount.

Excel is by far the best tool for that.

And the best spreadsheet is often something you write yourself... because you'd know what's driving what and you'd know which lever to push/pull to change what you wish to change.

Every trader should invest a bit of time in Excel if they aren't already.

 

[theartglasshouse]

Why pay for MS when you can get Open Office for free...

I do the calculations in my head...numbers stick in my head...which drives me insane...especially when it comes to number plates...credit cards...account numbers...ABS figures give me a migraine...

Probably the reason the idiots in management keep me in my position...

 

[So_Cynical]

I brought 2010 Home and student office (3 users) the other day for $120 on-line...not alot to pay per user for Excel and Word for Windows 7.

 

[skyQuake]

Why pay for MS when you can get Open Office for free...

I do the calculations in my head...numbers stick in my head...which drives me insane...especially when it comes to number plates...credit cards...account numbers...ABS figures give me a migraine...

Probably the reason the idiots in management keep me in my position...

The programming part of Open Office is in Python which most non-programming background won't know.
Excel still miles ahead with complex tasks and macros.

 

[LostMyShirt]

Skc - too true. I love using these kinds of trackers and it really does help with analysis. I had a lot of trouble, as you can see, getting the formulas right. Hence my opting for a premade.

I have office 07 - very compatable...

Well at least now if anyone needs a decent spreadsheet for tracking their trades or even paper trades you know where to grab one.