T212 Contract Note Email Importer

This script will import Contract Notes sent by T212 from your gmail inbox, to a google spread sheet.

  • create a new google sheet
  • click -> tools -> script editor
  • paste the script
  • change “c1” on line 3 here, to the sheet you want to update (or rename a sheet in the doc to c1)
  • change the label on line 5 to the actual label you use in Gmail.
  • save
  • close the script editor.
  • hard refresh the document
  • You should see a new menu item now like in the screenshot below
  • Click to import, first time you use it, google will ask you to authenticate (your docs to your email)

edit: made slight changes to download header once.

image

it’ll work as long as the format of the email is same, enjoy.

var currDocument=SpreadsheetApp.getActiveSpreadsheet();
// replace c1 with the sheet name you want to import into
var sheet1 = currDocument.getSheetByName('c1');

function getemails() {
  // this is the label you assigned to the contract notes, 
  // sub labels are separated by a forward slash
  var label = GmailApp.getUserLabelByName("someLabel/subLabel");
  var threads = label.getThreads();

  for (var i = 0; i < threads.length; i++) { 
    var messages=threads[i].getMessages();  
    for (var j = 0; j < messages.length; j++) {
      var message=messages[j];
      if(message.isUnread()) { // <- will only import unread messages, remove this if you want to import all
        process(message);
      }
    }
  }
}

function process(message) {
  var body = message.getPlainBody();
  var firstElement = "№"
  var lastElement = "Total cost"
  
  var rows = [];
  var cols = [];
  var lastCol = 0;
  var foundFirst = false
  var foundLast = false
  
  lines=body.split("\n").filter(function (el) {
    return el.trim() != "";
  });
  
  for( var i = 0; i < lines.length; i++) {
    var ct = lines[i].trim();
    if(ct == "* All transactions' execution times are GMT.") {
      break;
    }
    if( foundFirst && foundLast) {
      cols.push(ct);
      if(cols.length == lastCol) {
        rows.push(cols);
        cols = [];
      }
    } else {
      if( foundFirst == false && ct == firstElement) {
        foundFirst = true;
        cols.push("No")
      } else if(foundLast == false && ct == lastElement) {
        foundLast = true
        cols.push(ct)
        lastCol = cols.length;
        rows.push(cols);
        cols = [];
      } else if(foundFirst){
        cols.push(ct);
      }
    } 
  }
  for(var j = 0; j < rows.length; j++) {
    if(j==0 && sheet1.getLastRow()!==0){
      continue;
    } else {
      sheet1.appendRow(rows[j]);
    }
  }

  message.markRead();
}

function onOpen() {
  var entries = [{
    name : "T212 Contracts",
    functionName : "getemails"
  }];
  currDocument.addMenu("Import", entries);
};
20 Likes

You legend, thank you

Label? We are a progressive, inclusive community here :roll_eyes:

:+1: Thanks for sharing

I am a labeling addict :frowning:

image

1 Like

I was always like that with work email, never did it on my personal

I’m just gonna bump this. I feel it got buried and not many people saw this very helpful post.

Also I’ll add an example sheet of how I’ve formatted some of the values that are imported.

Using =GOOGLEFINANCE you can get a good update on the market value of your portfolio automatically. It will not reflect it exactly becuase GOOGLEFINANCE has up to 20mins delay.

1 Like

Thanks for this.
Very useful until T212 add in export functionality.

How to save contract notes into portfolio perfomance:

  • Create some accounts: (one time only)
    If you want to handle your portfolio like me, I recommend creating a “securities account” and a “deposit account” in portfolio performance for every pie you have on T212.
    Alternatively you can create just one securities and one deposit account if you want to just save all your investments in one place.
    image
  • Create a template in portfolio performance for T212 contract notes (one time only)
    go File -> import -> CSV Files
    select the CSV file you saved from google docs
    match the csv columns to the fields needed manually or:
    • save this following line as plain text file somewhere like somefilename.json
{"delimiter":",","columns":[{"field":"ticker","label":"Column 1"},{"field":"isin","label":"Column 2"},{"field":"type","format":"BUY=Buy;SELL=Sell;TRANSFER_IN=Transfer (Inbound);TRANSFER_OUT=Transfer (Outbound);DELIVERY_INBOUND=Delivery (Inbound);DELIVERY_OUTBOUND=Delivery (Outbound)","label":"Column 3"},{"field":"shares","format":"0,000.00","label":"Column 4"},{"label":"Column 5"},{"field":"value","format":"0,000.00","label":"Column 6"},{"field":"date","format":"dd-MM-yyyy","label":"Column 7"},{"field":"time","label":"Column 8"},{"field":"taxes","format":"0,000.00","label":"Column 9"},{"field":"exchangeRate","format":"0,000.00","label":"Column 10"},{"label":"Column 11"},{"field":"currency","label":"Column 12"},{"field":"account","label":"Column 13"},{"field":"portfolio","label":"Column 14"}],"isFirstLineHeader":false,"skipLines":0,"label":"Trading212 PT","encoding":"UTF-8","target":"portfolio-transaction"}
  • click the gear icon there and select import configuration
    image
  • use somefilaname.json
  • From now on you just go File -> import -> Templates -> Trading212 PT
  • will show you the fields like:

Click next and next again, enjoy your transactions in portfolio performance.

1 Like

Slightly altered version of this importer, creating some fields and modifying others for easy import into portfolio performance.
google docs script:

var currDocument=SpreadsheetApp.getActiveSpreadsheet();
// change c2 to the sheet name you want to import contract notes into
var sheet2 = currDocument.getSheetByName('c2');

var numbersOnly = regex = /[^0-9,.]/gi;

function isin(input) {
  var sp = input.split("/");
  return [ sp[0], sp[1]];
}

function identity(input) {
  return [input];
}

function parseF(input) {
  return [parseFloat(input)];
}
function removeText(input) {
  return [input.replace(regex, "")];
}

function pseudoColumns(newRow) {
  newRow[4] = newRow[4]/newRow[9];
  if(newRow[9] <= 0.01) {
    newRow.push("GBX");
  } else if(newRow[9] <= 0.8) {
    newRow.push("USD");
  } else if (newRow[9] <= 0.99) {
    newRow.push("EUR");
  } else {
    newRow.push("GBP");
  }

  newRow[9] = 1/newRow[9];
  newRow.push("GBP");
  newRow.push("cash account name"); 
  newRow.push("securities account name");
}

var columnMap = [
  [false, "na"],         // 0 
  [false, "na"],         // 1 
  [true, isin],          // 2 0,1
  [true, identity],      // 3 2 direction
  [true, identity],      // 4 3 quantity
  [true, parseF],        // 5 4 price
  [true, parseF],        // 6 5 Total Amount
  [true, identity],      // 7 6 Trading day
  [true, identity],      // 8 7 Trading time
  [false, "na"],         // 9 Commission  
  [true, parseF],        // 10 8 Charges And Fees
  [false, "na"],         // 11 order type
  [false, "na"],         // 11 execution venue
  [true, parseF],        // 12 9 exchange rate
  [false, "na"],         // 13 Total cost
  [false, "na"],
  [false, "na"],
  [false, "na"]
];



function reprocess( transactions ) {
  var out = []
  for(var row = 0; row < transactions.length; row++ ) {
    var newRow = []
    for( var col = 0; col < transactions[row].length; col++) {
      if(columnMap[col][0]) {
        var generated = columnMap[col][1](transactions[row][col]);
        for(i = 0; i < generated.length; i++) {
          newRow.push(generated[i])
        }
      }
    }
    pseudoColumns(newRow);
    out.push(newRow);
  }
  return out;

}

function getemails() {
  // change the label to match where you have your contract notes in gmail
  var label = GmailApp.getUserLabelByName("documents/trade/trade212/contract");
  var threads = label.getThreads();

  for (var i = 0; i < threads.length; i++) { 
    var messages=threads[i].getMessages();  
    for (var j = 0; j < messages.length; j++) {
      var message=messages[j];
      if(message.isUnread()) { // <- will only import unread messages, remove this if you want to import all
        process(message);
      }
    }
  }
}

function process(message) {
  var body = message.getPlainBody();
  var firstElement = "№"
  var lastElement = "Total cost"
  
  var rows = [];
  var cols = [];
  var lastCol = 0;
  var foundFirst = false
  var foundLast = false
  
  lines=body.split("\n").filter(function (el) {
    return el.trim() != "";
  });
  
  for( var i = 0; i < lines.length; i++) {
    var ct = lines[i].trim();
    if(ct == "* All transactions' execution times are GMT.") {
      break;
    }
    if( foundFirst && foundLast) {
      cols.push(ct);
      if(cols.length == lastCol) {
        rows.push(cols);
        cols = [];
      }
    } else {
      if( foundFirst == false && ct == firstElement) {
        foundFirst = true;
        cols.push("No")
      } else if(foundLast == false && ct == lastElement) {
        foundLast = true
        cols.push(ct)
        lastCol = cols.length;
        rows.push(cols);
        cols = [];
      } else if(foundFirst){
        cols.push(ct);
      }
    } 
  }

  /*
  for(var j = 0; j < rows.length; j++) {
    if(j==0 && sheet1.getLastRow()!==0){
      continue;
    } else {
      sheet1.appendRow(rows[j]);
    }
  }
*/

  message.markRead();
  

  var nr = reprocess(rows.slice(1));
  for(var j = 0; j < nr.length; j++) {
    sheet2.appendRow(nr[j]);
  }
  
  

}

function onOpen() {
  var entries = [{
    name : "T212 Contracts",
    functionName : "getemails"
  }];
  currDocument.addMenu("Import", entries);
};

sample result imported into google docs in next screenshot (note since there is no pie/account information in T212 contract notes the cash account and securities account is a placeholder)

Change the last two columns manually to the matching account names in your portfolio performance.

1 Like

this is fantastic, thank you very much.