You might have figured out from the Title that this Blog is about how I record my Individual Share Trades and Spreadbet Trades onto dedicated Spreadsheets so I have a record of what Trades I have opened and once they are closed off it enables me to calculate my Profit or Loss in £ Note terms and in Percentage terms expressed against my Capital that was used for the Trade (read that as ‘Investment’ mostly).
My plan had been to finally sort mine out so it would make more sense to Readers and then I would upload it into the ‘Useful Links’ bit of the Website, but as I have thought about this in recent days it has occurred to me that it would be better if I did it as a Blog so that I can include a bit of commentary to explain what the columns etc. mean and any other points to note on how to best use them.
Anyway, there are 2 basic Spreadsheets that I use - one for Share Trades and one for Spreadbet Trades - but then I have different Spreadsheets for different Accounts - e.g. one for my iDealing Main ISA Trading Account and a separate one for my iWeb Income Portfolio ISA etc. Obviously Readers can use Spreadsheets in any way they find most appropriate and practical.
WD Example Share Trades Record Spreadsheet v1.0
This is a very simple Spreadsheet and probably includes most things Readers would want. Here is an Embedded File in Microsoft Excel Format (hopefully):
- ‘Qty’ - is obviously an abbreviation for Quantity and is the Number of Shares you bought.
- ‘Revised SL’ means Revised Stoploss - this is where you trail the Price up and move the Stoploss up or whatever. I rarely use Stoplosses so this Column hasn’t been used much by me in practice.
- The ‘Target’ number can obviously be changed over time - you might prefer to do similar to what I did with Stoplosses where you have a ‘Revised Target’ Column or whatever.
- On ‘Deal Value inc Costs’ you could create a Formula to sit behind this but the way I use it is to write down the Total Deal Value (including all Costs like Trading Fees and Stamp Duty etc.) from the ‘Deal Contract Note’ my Broker provides and then I enter that figure in here (in other words I don’t use a Formula here but you could).
WD Example Spreadbet Trades Record Spreadsheet v1.0
This is another very simple Spreadsheet and should include most things Readers would want. Here is an Embedded File in Microsoft Excel Format (hopefully):
|File Size:||14 kb|
- On Short Trades (the second example here regarding VOD shows this) you need to put in a Minus Sign on the ‘£s per Point’ Column or you end up with a Negative Profit and it gets a bit confusing.
- The Column marked ‘Approximate Interest Charges’ I rarely use these days and I just keep it blank or bung a Zero in. It is up to Readers how they do this but I take the view that for practical purposes the Dividends I get on Long Spreadbets tend to cover the Interest Charges and I don’t need to know that precise level of detail. When we cut to the chase and think about what really matters it is the Value of the Portfolios overall that matters most and this is something I religiously monitor on an End of Year basis (I will be doing the usual ‘Scores on the Doors’ Blog very soon) to calculate my Overall Return. However, that Return is the critical thing and how I got to this in terms of Individual Spreadbet or Share Trades is much less important so I do not need 100% total accuracy.
- I get my ‘Initial Deposit’ (this is the same as the ‘Margin’ you use to enable the Trade to be first opened) number from my Spreadbet Platform at the time of opening my Trades. I base my ‘% Return on Capital’ on this Initial Deposit number but of course as time goes on the amount of Capital or Margin used for the Trade varies - I think it is most appropriate to use this Starting Number.
- The ‘% Return on Exposure’ is based on the Initial Exposure - again as the Share Price rises or whatever, this Exposure Value changes but I think it is best to use this Initial Number.
- The ‘Initial Exposure’ calculation comes out Negative for a Short Trade because you need to put the Minus Sign against the ‘£s per Point’ figure. In a way this makes sense because logically when you are Short it is sort of the opposite of Long Exposure. However, the catch here is that when you calculate the ‘% Return on Exposure’ Column you get a Negative Number which is not strictly correct. I only shoved these Columns in tonight and I can’t figure out quickly how to fix this so I will leave that for you guys to resolve !! (Don’t say I fail to give you any Xmas treats……)
- I realise there is a quirk in the Profit Columns but I will leave it as is and you can alter the Formulas if you wish to. The Column marked ‘Profit/Loss Outturn b4 Costs’ does not include the Dividends but the ‘Profit after Costs’ Column does include Dividends in the Formula.
- As with the Share Trades Record Spreadsheet, the blank Cells below my Example Entries do not contain any Formulas etc. so you need to Copy and Paste them.
OK, that’s it, hopefully this makes some sort of sense - feel free to contact me by Email or Tweet or DM or whatever if something is utterly confusing you.