Spreadsheets for Recording your Trades

Tweet

This is yet another of those Blogs I have had sat on the Slate of Ideas for me to write but it has taken up until now to finally float to the top and therefore earn my priority and focus (turds float and all that……). That’s quite a misrepresentation really - rather than an organised and sequential list of Blog Ideas for me to work on, it is a very higgledy piggledy collection of possible Blog Titles with a few having some words underneath to help me understand what I was thinking at the time of coming up with the idea and typing it into my working Blog Word Document. So the true way that a Blog gets to this stage is that it quite simply becomes the thing I fancy doing today and I just get on with it. At least in this way it is more pleasant for me to work on as I do what I want to do, rather than a Blog that I must do because it happens to be at the top of an arbitrary List.

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

Years ago I was asked by Father Ted (@harry_southdown) on Twitter if I had an example Spreadsheet for recording my Trades that he could borrow from me and use as a starting point for him to tweak as he needs. At the time I rejigged the Spreadsheet I was using myself and sent it over and since that I have shared it with a few people but it is pretty obvious that many people might find this handy - or maybe they already have one themselves but this might give ideas on how they can tweak theirs to make it better.

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):

---

wd_example_share_trades_record_spreadsheet_v1.0.xlr
File Size:	17 kb
File Type:	xlr

Download File

---

---

Here is a Screenshot of how it looks (I told you it was simple !!):

---

---

Most of it should be blindingly obvious and much will be where you just key in the Numbers and the Dates and Text and stuff and then there are some that have Formulas behind them - you can just Copy and Paste and these Cells should automatically adjust to the Lines and Columns as appropriate. Here are some pointers:

• ‘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):

---

wd_example_spreadbet_trades_record_spreadsheet_v1.0.xlr
File Size:	14 kb
File Type:	xlr

Download File

---

---

Here is a Screenshot of how it looks (if you click on it you should get a larger version in your Browser thing):

---

---

The big difference on this one is that it has the capability (to a small extent !!) to record Short Trades as well - here are some things to note:

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

Regards, WD.