UK Capital Gains report for Smart Portfolios - MySQL script to calculate it

[dabb1er]

I was disappointed to discover that the IG platform does not provide Capital gains figures for the reballancing and monthly transaction fees on any shares account (easier to track share account but Smart Portfolio in its nature is harder as you are not in control of transactions)

In order to resolve this I created a MySQL script to calculate the allowable costs to offset against sales for each of the transactions.

Just export all your transactions to a csv file and import this into a MySQL database (free to install on numerous platforms).

The SQL query at the end will provide a report that can be exported showing your gains/losses for each SELL transaction.

Obviously use this script at your own risk - this was created purely for my personal use and manual checking of the first few units showed it was calculating accurately based on UK's HMRC's current allowance for a 104 section holding.

 

The script is listed below:

/* use this script with a MySQL database */
/* use MySQL workbench data import wizard to import data into tradehistory_import table into a schema called smartportfolio */
/* make sure the date and time fields at the start of the table have their name changed to tdate and ttime */

use smartportfolio;
ALTER TABLE `smartportfolio`.`tradehistory_import` 
CHANGE COLUMN `Cost/Proceeds` `Cost_Proceeds` DOUBLE NULL DEFAULT NULL ,
CHANGE COLUMN `Conversion rate` `Conversionrate` DOUBLE NULL DEFAULT NULL ,
CHANGE COLUMN `Order type` `Order_type` TEXT NULL DEFAULT NULL ,
CHANGE COLUMN `Venue ID` `VenueID` TEXT NULL DEFAULT NULL ,
CHANGE COLUMN `Settlement date` `Settlement_date` TEXT NULL DEFAULT NULL ,
CHANGE COLUMN `Order ID` `OrderID` TEXT NULL DEFAULT NULL ; 
drop table if exists tradehistory;
create table tradehistory as select *,str_to_date(concat(tdate, ' ',ttime),"%d-%m-%Y %H:%i:%s") as tradedate from tradehistory_import;
drop table if exists purchasecost;
CREATE TABLE `smartportfolio`.`purchasecost` (
  `tradedate` DATETIME NOT NULL,
  `Market` VARCHAR(255) NULL,
  `LastPoolOfCost` DOUBLE NULL,
  `PoolOfCost` DOUBLE NULL,
  `LastQuantityHeld` DOUBLE NULL,
  `QuantityHeld` DOUBLE NULL,
  `OrderID` TEXT NULL,
  INDEX `tradedate_idx` (`tradedate` ASC) INVISIBLE,
  INDEX `market_idx` (`Market` ASC) VISIBLE);
drop procedure if exists calculate_purchase_cost;
DELIMITER $$
create procedure calculate_purchase_cost()
BEGIN
  declare done int default false;
  declare c_tradedate datetime;
  declare c_orderid varchar(255);
  declare c_market varchar(255);
  declare c_direction varchar(255);
  declare c_quantity double;
  declare c_price double;
  declare last_poolofcost double;
  declare last_quantity double;
  declare last_tradedate datetime;
  declare last_orderid varchar(255);
  declare cur1 cursor for select tradedate, orderid, market, direction, quantity, price from tradehistory order by tradedate asc;
  delete from purchasecost;
  open cur1;
  read_loop: LOOP
    fetch cur1 into c_tradedate,c_orderid,c_market,c_direction,c_quantity,c_price;
    if done THEN
      leave read_loop;
    end if;
    select null,null,null,null into last_poolofcost, last_quantity, last_tradedate, last_orderid;
    select a.orderid, a.tradedate into last_orderid, last_tradedate from purchasecost a where a.market = c_market and a.tradedate < c_tradedate order by tradedate desc limit 1;
    select a.poolofcost,a.quantityheld into last_poolofcost, last_quantity from purchasecost a where a.market = c_market and a.orderid = last_orderid;
    if c_direction = 'SELL' then
      insert into purchasecost select c_tradedate,c_market,last_poolofcost,last_poolofcost + c_quantity / last_quantity * last_poolofcost,ifnull(last_quantity,0),ifnull(last_quantity,0) + c_quantity,c_orderid;
    end if;
    if c_direction = 'BUY' THEN
      insert into purchasecost select c_tradedate,c_market, ifnull(last_poolofcost,0), (c_price * c_quantity / 100 + ifnull(last_poolofcost,0)) ,ifnull(last_quantity,0), ifnull(last_quantity,0) + c_quantity,c_orderid;
    end if;
  end loop;
  close cur1;
end$$
DELIMITER ;

call calculate_purchase_cost;

SELECT a.tradedate,a.settlement_date,a.direction, a.orderid, a.market,a.quantity,a.price, b.lastquantityheld,b.quantityheld,b.lastpoolofcost,b.poolofcost, 
case when a.direction = 'SELL' then -1*a.quantity*a.price/100 else 0 end as price,
case when a.direction = 'SELL' then -1*a.quantity/b.lastquantityheld*lastpoolofcost else 0 end as allowable_cost, 
case when a.direction = 'SELL' then ifnull(-1*a.quantity*a.price/100 + a.quantity/b.lastquantityheld*lastpoolofcost,0) else 0 end as gain FROM tradehistory a, purchasecost b where a.orderid = b.orderid order by a.tradedate asc;