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.

Why I don't like short end German bonds any more

Its a cardinal sin if you meddle with a systematic futures trading system. So I hope the patron Saint of Traders will forgive me.

There doesn't seem to be a patron saint of Traders, but St. Matthew is the patron saint of Bankers so that is close enough.

I'm going to stop trading 2 year german government bond futures (Shatz). There are two main reasons.It's basically a question of very high downside versus limited upside.

Firstly the downside: volatility is very low, about 20 basis points a year movement on the yield. This compares to normal volatility in interest rates of about 100 basis points a year.

Low vol means more leverage, and hence exposure to a Swiss Franc type episode. It feels to me like an asset with negative skew with a serious peso problem. It also means higher volatility adjusted trading costs.

Secondly there isn't much upside. German 2 year interest rates are minus 18bp! Yes you read that correctly, there is a minus sign.*

With thanks and apologies for screen grabbing from Bloomberg

* There is a subtle difference between the benchmark yield and the effective yield on the Cheapest to Deliver bond on the future, but lets not worry about that today - its Friday.



I'm extremely uncomfortable trading bonds when interest rates are negative or close to it. As systematic traders we rely on the future being like the past. There aren't enough periods of negative rates in the past to make me confident that my back-tested models will work.

This isn't just a case of turning off all developed world interest rate trading models in a post 2011 ZIRP / QE enviroment. Its a problem that's specific to Germany (and Japan, Switzerland and Denmark; but I don't trade bond or STIR futures there).

Contrast the Shatz to the US two year future. US two year yields are a reasonably healthy 50bp and volatility is around three times what it is on the Shatz.

I have no hard and fast rules about where the threshold is between 'models work' and 'models not working'. For now at least I'm going to keep trading the German 5 year Bobl future. Although five year rates are around zero the volatility is a slightly more sane 35bp, so my exposure to a black swan event is more limited. If the 5 year rate goes below minus 10bp, or the volatility drops below 25bp (about 1 point a year in price terms), then the Bobl will be joining the Shatz in Room 101.

Anyway I'll be reallocating my risk to both the 5 year and 10 year (Bund) futures.

I don't trade 20 year Buxl as the risk is too chunky for my modest book. 


Of course QE will probably push prices up further (although it might be a case of buy the rumor, sell the fact?), but I'll participate in the upside. It strikes me that the yield curve is unlikely to move parallel on the QE move. In Japan we saw curves flattening as rates approached zero, with yields out to a few years hitting zero and then a 'hockey stick' shape developing further out. So there should be more upside further out on the curve.

(I also hold Italian and French government futures, which will probably benefit)

This is all over thinking things somewhat, and I'd like to emphasise that this isn't a 'trade', I'm just trying to support what is for me at least a controversial decision.

Whenever you make an intervention like this you should set some 'exit conditions. So if the Shatz volatility rises above 30bp and the yield above 25bp, then I'll be back in.

I won't be selling out immediately, but I've started gradually reducing. I expect to be out well before when I would normally be ready to roll into the June 2015 futures. Hopefully there won't be a blow up before then!

UK politics through the looking glass of finance and IT

I'm going to do something potentially dangerous today, and write about politics. In a little under 4 months time we're going to have an election in the UK, and the campaigning has already started in earnest. Watching the main party leaders haranguaging each other, and us, about the deficit and the health service is somewhat depressing.

I don't know much about politics, although I know a bit of economics, something that makes hearing these debates even more annoying. There are a few things I do feel comfortable with such as financial analysis, behavioural finance and IT project management. So I thought it would make complete sense to analyse the election using ideas from those fields.

The arcane art of data obfuscation

Politicans, ably assisted by the media, remind me of active fund managers producing performance statistics. They all manage to beat the benchmark. This makes it tough to work out which manager is best, and hides the real issue, which is that they all lost money.

Similarly the lack of public understanding of economic issues makes it all to easy for our elected leaders to spin the numbers to suit their case, and ensure we don't focus on what is important. The government are spending roughly 45% of GDP versus revenue of 40% of GDP, a net deficit of 5% of GDP. We currently owe about 70% of our GDP (public sector net debt, rather than the gross figure usually quoted). This is the highest figure achieved except after major wars.

I'd bet you'd struggle to find many members of the public who know those figures. This is a real pity because it makes it very easy for politicians to treat us like children. We know that times are tough at home because Daddy has lost his job, but we don't know exactly how good or bad our financial situation is. So when Mummy and Daddy argue about what the best solution is to the problem, its difficult to know whose right because we're not even sure what the problem is exactly.

I guess they think we are too stupid to understand.

The overconfidence bias

One of the main findings of behavioural finance is that people are woefully overconfident. This is a real problem when people are making decisions about large amounts of client money, such as in huge hedge funds like the one I used to work for.

Politicians are keen to slag off idiots in the banking and investment industries, but they are also people making decisions about even larger amounts of other peoples money. At least hedge fund managers are regulated, and many have some kind of training or education to do their jobs, unlike politicans.

Take the issue with the deficit above. There are only five ways to deal with this.

1. Raise more revenue through increasing taxes
2. Cut spending in real terms
3. Increase productivity and see the economy grow in real terms
4. Let inflation rise to inflate away the debt
5. Repudiate the debt and not pay it back

So we can eithier move the budget into surplus by options 1,2 and 3; or directly cut our outstanding debt with options 4 and 5.

Leaving aside the final option, which is not really an option for an economy like ours, we've got four possibilities. Unfortunately option three is a difficult one since governments don't really know how to make this happen. They can do supply side stuff, but most government impact on growth will come from the secondary effects of options one and two, making the problem even less tractable.

Option four also seems to be off the table. The Bank of England can't currently produce 2% inflation, never mind the 5% that would get the deficit down to manageable levels relatively easily. I have some ideas that would help but this isn't the place to talk about unconventional monetary policy, and if the public can't be trusted to grasp simple accounting identities about government borrowing then IS/LM models and RBC calibration are probably a bit esoteric for the front cover of a tabloid.

This leaves us with options one and two.

Unfortunately economists can't agree or don't know which of these is correct. Yet if you listen to the political debate you would think that the main party leaders had a hotline to some hietherto unknown economic guru who has all the answers. They talk as if they are completlely sure that their preferred way is correct, and the other way is economic madness.

This is overconfidence of the highest order. I'm reminded of people whose portfolios are wholly undiversified and who refuse to countenance any suggestion that this is a bad idea, and maybe they should buy just a few bonds to hedge themselves a little.

The big IT project fallacy

If you've worked in the corporate world you will know the dangers of the big IT project well. Someone senior will look at your current sticky taped together mess of legacy systems and declare that the whole lot needs rewriting. A project spec is produced showing the whole thing will take a year. A highly paid project manager is brought in.

The huddled mass of existing programmers, who have been bravely keeping the show on the road for years with no budget, are augmented by a team of over paid consultants and permanent newly hired whizzkids.

The whole enterprise is doomed from the start but spec creep and user interface over-design always make it worse. If the organisation is brave enough, or run out of money, they will cancel it after two years and swallow the sunk cost. If not then after five years something vaguely useable will appear. In the first year nobody will know how to use it, preferring the old system. When the old stuff is turned off there will be another year, at least, of chaos.

Eventually everyone will get used to it. However all the patches that have been required to make it work will mean it looks like a horrible mess, and the cycle will begin again.

It is massive overconfidence of the highest order to assume you can completely build new complex systems without something going wrong, in relatively short periods of time, without causing chaos.

Yet this is exactly what the government does every time it decides to reorganise the NHS, the education system, the military etc. They decide the whole thing is a bit rubbish, fragmented, inefficient and decide to replace it with a new shiny NHS. The rest of the story is exactly the same, except that there is also the possibility that another government will come in and cancel the project after five years; only to replace it with their own version.

Often ironically this process involves the installation of a massive computer system, so we get both the digital and the analogue version of this story running in parallel. The failures of large government computer system projects are well known, but nobody has drawn the logical conclusion that this is just a symptom of the underlying problem. New governments trying to rewrite the software of a large mixed economy every five years are doomed to failure.

Here is Carver's 17th law of organisations:

Bad change is worse than a bad system. 

People are remarkably good at working with bad systems and making them work. In the Soviet Union a parallel economy existed where factory managers bartered goods to keep things going, completely bypassing the creaking machinery of state planning. Things only disintegrated when the system was replaced overnight by crony capitalism.

The continous revolution that people working in the education and health sectors, to name but two, is much worse than if we'd left an apparently poor system alone and let people make it work.

What should we do?

If I ruled the UK  - strictly as a figurehead with elected political leaders below me of course - I would take the following steps.

1) I'd close the office for budget responsibility, and use the money saved to beef up the funding of the Institute of Fiscal Studies. Both these organisations provide a sort of independent economic audit of the governments finances. But the former, run by a former IFS head, is seen as too close to the government to be independent. The IFS funding would become ringfenced so it couldn't be influenced by future governments.

In return the IFS would have to do a lot more public outreach explaining and interpreting the economic numbers to an audience beyond who they reach now. I'd like to see the director of the IFS writing columns in the Sun and the Mirror.

2) I'd force all politicians to adopt diversified portfolios. I've no idea whether tax cuts or revenue raising is best. I don't want any future party experimenting to find out. Let's limit the maximum amount of deficit that can be closed by one method or the other to 65%, with a minimum of 35% coming from the other element. With the IFS educating the public we can then take a more informed choice of which is best, but at least if we get it wrong the consequences will be less extreme. Let the ideological difference come down to which spending we cut or tax we raise, rather than by how much.

3) Finally the government would have to adopt an agile methodology to all system changes, using the method the best software development teams have used for many years. This means doing things in small bite sized chunks rather than doing a massive change to the entire system. In case its not clear I'm talking about all changes to the state machinery, not just computer software.

The overall project plans for any change expected to last longer than 5 years would have to be agreed by cross party support and then frozen so they can't be messed with by the next government.


I think thats enough for one post. Next week I'll solve world peace.