Calculating UK trading tax liability with Python

Do you trade in the UK? I do. I have a systematic trading system which buys and sells futures, and I also do a bit of equity and ETF trading. This year I have made some money, so I need to pay some tax. There are three ways you can be taxed:

1) Not at all.

This only applies to those using spread-bets, or allegedly my ex boss Stanley.

2) Income tax.

It might be possible to pay income tax on your trading profits, if you're classed as earning it by 'trading' (the HMRC definition) rather than by speculation. If you earn the majority of your income from trading, and you satisfy certain other tests, then you could have your trading profits taxed under the income tax regime.

3) Capital Gains Tax.

Probably the majority of people operating as traders in the UK will pay capital gains tax (CGT) on their trading profits.

I intend to send HMRC both calculations this year, plus an explanation of exactly what I am doing, to see if they think my trading profits should be taxed as income. This may or not be advantageous, but as HSBC are learning, you don't mess with the taxman or woman so it's better to be open and honest.

The rules for UK capital gains are ... interesting.

If you just buy and sell UK shares however you can make use of nice websites like http://www.cgtcalculator.com/. However this can't cope with short selling, or foreign currency trading, or futures for which value doesn't equal price. It also doesn't easily cope with the .html reports produced by interactive brokers (IB).

So I've spent the last week or so hacking together some python code which deals with these issues.

I can't emphasise enough that you should be very careful with this code. It may be full of errors. Tax rules may change. My interpretation of how HMRC rules are applied to futures and foreign currency trades may be incorrect. Its no substitute for professional advice. Be careful out there.

Getting and running the code

You'll need python and the pandas library. If you're going to use the .html reports produced by IB you'll also need Beautiful Soup.

You'll probably also need the quandl python API  for FX prices, unless you have another source you prefer (in which case you'll need to patch it in yourself).

You can get the source code from github here..


If all is well you should be able to run the code in the example.py file. This will produce a report for some made up data that I created.

Trade and position source files

You can get trade data from eithier the .html reports produced by IB, or from flat .csv files. Made up examples of each are included in the repo. To get your own IB trade files log in to Account manager... Reports.... trade confirmations. And then save as .html. Reports need to cover the period from when you opened your account. You can only run one year of trade reports at a time, so its a good idea to run them regularly and save them

We obviously need trade files, but why do we need positions? These are optional, but very useful. The final positions I have after processing my trades should match what I have from the position files. To get the IB positions log in to Account manager... Reports.... activity report. Save as .html

Spot FX positions are hard to extract from the position reports IB produce and even harder to reconcile.  So its quite normal to get a break between trades and positions for this asset class.
 
It's possible to join together multiple trade and position objects, and I show how this is done in the example.

Output file (argument reportfile)

The example outputs to a text file which you can change. If you remove this argument from the calling function then it will report to the screen.

FX Data (argument fxsource)

There are three ways to get FX data. You can use FIXED FX rates, a set of which I've included (which is the default in the example). You can download them from the excellent website QUANDL. I use my own DATABASE. Naturally this option won't work for you, unless you change the code in databasefxrates.py.

Calculation method (argument CGTCalc)

As I said above its not clear if us traders should calculate our tax using income tax or capital gains. If you set this flag to False it will calculate trading profits using a simpler method of average cost to date versus realised value.

 

 

Verbosity (argument reportinglevel)

There are several levels of detail available.

• ANNUAL - Gives a summary for the year
• BRIEF - As above, plus one line per trade
• NORMAL - As above, plus matching details
• CALCULATE - As above, plus explicit calculations
• VERBOSE - As above, plus a breakdown of the matching trades

Digging into the data

If you run the report at the BRIEF level as in the example you might want to dig into specific trades.  For example lets have a look at the December 2014 French Bond (BTP) futures trades. We can eithier just look the trades for that code (at whatever level of detail we want):


    taxcalc_dict['FBTP DEC 14'].
display_taxes_for_code(taxyear=2015, CGTCalc=CGTCalc, reportinglevel="CALCULATE")


Or there might be a particular trade we are interested in. We will need the reference number, which is highlighted in the report extract below (note this is different from the TradeID which we'll see later)

2: SELL 1 FBTP DEC 14 Futures on 16/10/2014 at EUR 129,750 each gives PROFIT of EUR 1,926 equals GBP 1,589
3: SELL 2 FBTP DEC 14 Futures on 16/10/2014 at EUR 126,900 each gives LOSS of EUR -6,958 equals GBP -5,742
4: SELL 1 FBTP DEC 14 Futures on 23/10/2014 at EUR 128,820 each gives LOSS of EUR -876 equals GBP -726
5: SELL 1 FBTP DEC 14 Futures on 06/11/2014 at EUR 130,180 each gives PROFIT of EUR 484 equals GBP 401
Trade number 3 looks interesting, so we can get a full breakdown for that

    taxcalc_dict['FBTP DEC 14']. matched[3].group_display_taxes(taxyear=2015, CGTCalc=CGTCalc, reportinglevel="VERBOSE")

As a bonus you might want to analyse the profits or losses for your own purposes.

    ## Bonus feature - analyse profits
    profits=taxcalc_dict.return_profits(2015, CGTCalc)
    profit_analyser(profits) 

Returns:

920 Trades Profits 412 Losses 505
Average profit 185.18 Average loss -94.34

Total profits by code:
            code  profit
76          VXX4  -2117
61         BRWMl  -1843
108   FVS NOV 14  -1339
34          014Z  -1026
......
196         VXN4   1630
224  FOAT SEP 14   1934
178   FVS JUN 14   2107
52          SLIl   2325
65          CGLl   3219
32          VXM4   4173

June was clearly better than November for european volatility futures....

 

 

What does it all mean?

 

Capital gains tax example

Here is a particularly interesting German 2 year bond (Shatz) future - my least favourite market right now.

 

 SELL 9 FGBS DEC 14 Futures on 06/11/2014 at EUR 110,975 each gives PROFIT of EUR 195 equals GBP 129
 Commission EUR 18 and taxes EUR 0 on SELL

 

We sold 9 lots of the Shatz contract on the 6th November. We made 195 Euros, which is converted to GBP at the FX rate on the date we closed the trade.

Trade details:ID 1550 Code FGBS DEC 14 Date 2014-11-06 05:00:10 Quantity -9 Price 110.975 Value per block 110,975

The trade ID of the closing trade is 1550. These ID's are allocated when we read in the .html files; if you have your own source of trade information you can use your own (assuming they are unique). Note we're differentiating between price and value - for this future one is 1000 times bigger than the other. Sometimes you'll see trade ID's like this 1500:1 or 1500:2. That means I've split a 'natural' trade into a close and an open, where the trade as a whole changes the sign.

Total allowable cost EUR 998,562   Total disposal proceeds EUR 998,757


CGT profit is the difference between these two values. Disposal is what we get when we sell. Allowable cost is what we paid to buy. Commissions and fees are deducted from both values.



Now we enter the whacky world of matching. Each of these 9 lots needs to be matched with buys. First we match with trades that occurred the same day.

Matches with:
SAME DAY TRADE(S) Matches with BUY of 1 FGBS DEC 14 at average of EUR 110,985 each
 Commissions EUR 2 Taxes EUR 0

Trades:
ID 1531 Code FGBS DEC 14 Date 2014-11-06 02:52:40 Quantity 1 Price 110.985 Value per block 110,985
 

There was a buy of 1 lot a few hours earlier. Next we look at trades made in the 30 days after the closing trade - the so called 'bed and breakfust' rule.



SUBSEQUENT 2 TRADE(S) Within 30 days between 2014-11-14 and 2014-11-27: Matches with BUY of 2 FGBS DEC 14 at of EUR 110,935 each
 Commissions EUR 4 Taxes EUR 0 

Trades:
ID 1532 Code FGBS DEC 14 Date 2014-11-14 02:51:17 Quantity 1 Price 110.950 Value per block 110,950
ID 1533 Code FGBS DEC 14 Date 2014-11-27 05:22:12 Quantity 1 Price 110.920 Value per block 110,920



Finally we have the delights of the section104 holding. This is effectively a pro-rata of the unmatched trades that you held before you sold. Because its a pro-rata we need to split out a proportion of the previous trades.

PRO-RATA SECTION 104: Quantity 6.000000 FGBS DEC 14 allocated from total holding of 32, made up of 10 trades between 2014-09-01 and 2014-11-05
 At average value of EUR 110,948 Commissions EUR 12.0000 Taxes EUR 0  

Trades:
ID 1499a Code FGBS DEC 14 Date 2014-09-01 05:03:43 Quantity 4 Price 110.930 Value per block 110,930 (Allocated from: ID 1499 Quantity 20)
ID 1514a Code FGBS DEC 14 Date 2014-10-02 02:59:41 Quantity 0.200000 Price 111.015 Value per block 111,015 (Allocated from: ID 1514 Quantity 1)

..... several trades removed ....

ID 1529c Code FGBS DEC 14 Date 2014-11-03 02:49:50 Quantity 0.200000 Price 110.985 Value per block 110,985 (Allocated from: ID 1529 Quantity 3)
ID 1530a Code FGBS DEC 14 Date 2014-11-05 08:00:37 Quantity 0.200000 Price 110.970 Value per block 110,970 (Allocated from: ID 1530 Quantity 1)

 
Thus the suffix a,b,c....on each TradeID, the fractional trade sizes, and the information telling us which trade the pro-rata allocation comes from. Finally the detailed calculation:

CALCULATION: (9*110975) - 18 - 0 -(1*110985) - 2 - 0 -(2*110935) - 4 - 0 -(6*110948) - 12.0000 - 0  = 195


This spells out how we made our money. Each term shows the number of lots traded multiplied by value, less trades and commissions.

Short tax example

Let's look at a short sale (I've removed some detail here).

CLOSE SHORT 100000 AUD.USD Forex on 16/12/2014 at USD 0.821980 each gives LOSS of USD -99 equals GBP -60
 Commission USD 1.28000 and taxes USD 0 on CLOSE SHORT
 
Total allowable cost USD 82,196.72   Total disposal proceeds USD 82,100


CALCULATION: -(100000*0.821980) - 1.28000 - 0 +(100000*0.821000) - 0 - 0  = -99 

Notice that the allowable cost and disposable proceeds are reversed; the cost still refers to the buy even though this is the closing trade. The minus sign in front of the first term of the calculation, and the plus sign in the second term, also highlights this.

Annual summary

Note we get annual summaries of some of this information, which will correspond to entries on your tax return.

Summary for tax year ending 5th April 2015
Figures in GBP

Disposal Proceeds = 441,615, Allowable Costs = 424,080, Disposals = 918
 Year Gains = 93,924  Year Losses = -76,389 PROFIT = 17,535

Income tax 

Let's look at the same trade with CGTCalc =False, on an income tax basis. Here we just do a straightforward match of the close with the average cost of our previous buys.


  SELL of 9 FGBS DEC 14 Futures on 06/11/2014 at EUR 110,975 each Net PROFIT of EUR 12 equals GBP 8
Trade details:ID 1550 Code FGBS DEC 14 Date 2014-11-06 05:00:10 Quantity -9 Price 110.975 Value per block 110,975

BUY at average value 110,970 each between 2014-09-01 and 2014-11-06.  Total round-trip commission EUR 36, and taxes EUR 0
Trades:
ID 1499q Code FGBS DEC 14 Date 2014-09-01 05:03:43 Quantity 0.908314 Price 110.930 Value per block 110,930 (Allocated from: ID 1499 Quantity 20)
ID 1500q Code FGBS DEC 14 Date 2014-09-02 02:48:32 Quantity 0.0908314 Price 110.940 Value per block 110,940 (Allocated from: 
ID 1500 Quantity 2)

.... many trades removed ...


ID 1530a Code FGBS DEC 14 Date 2014-11-05 08:00:37 Quantity 0.290323 Price 110.970 Value per block 110,970 (Allocated from: ID 1530 Quantity 1)
ID 1531a Code FGBS DEC 14 Date 2014-11-06 02:52:40 Quantity 0.290323 Price 110.985 Value per block 110,985 (Allocated from: ID 1531 Quantity 1)

CALCULATION: (9*110975) - 18 - 0 -(9*110970) - 18 - 0  = 12


Here is the annual summary for income tax:

Summary for tax year ending 5th April 2015 
Figures in GBP

Gross trading profit 20,263, Commission paid 755.20, Taxes paid 152.94, Net profit 19,355

Not included: interest paid, interest received, data and other fees, internet connection,...
 hardware, software, books, subscriptions, office space, Dividend income (report seperately)

Note that if you are earning income as a trader then there are probably additional costs you can claim against your profits (but I'm not an accountant, so don't take my word for it). Taxes on dividends is another kettle of fish, and another section of your tax return, though thankfully a relatively easy one to fill in.