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);
};
25 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.

5 Likes

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.

3 Likes

this is fantastic, thank you very much.

Finally sat down and setup portfolio performance. Very nice, thanks! Much easier than maintaining my gsheet. Now need to find a way to import my dividend history. Although I think I’ll just wait until T212 has exportable data as it should be here by the end of the month.

@kali You wouldn’t happen to know how the performance benchmarks work would you? Does it assume a purchase when a deposit is made? Or when I buy/sell a security, it also assumes buy/sell of the benchmark asset. e.g:

If you’re not sure dw. I’ll ask on their forum. Thanks for the guide again.

2 Likes

Congrats! you won’t regret it and it’ll be one of the “always running” software you have :slight_smile:

I am not 100% sure but portfolio performance developers understand trading very well. So I am going to assume benchmarks behave like any other benchmark index. When you buy X, it’ll buy benchmark, when you sell X it’ll sell benchmark and if you have additional unused cash inside your portfolio, it’ll keep additional uninvested cash in benchmark.

1 Like

Hi,

Is this working?
I’ve tried to run it but I’m getting an error:

TypeError: Cannot read property ‘split’ of undefined

isin

@ Code.gs:8

I get the same error. Any help from you guys would be appreciated.

@nickspacemonkey can I ask what software is that and how I can create one for myself? Thanks

This is in fact still working, and I use it daily. The error message indicates your script was not able read an email (it is failing to split the email body on lines)

Did you make the changes mentioned in the comments like pointing to the correct labels in gmail?

1 Like

Yes, I did. Actually it worked after a few tries. Dunno why. Thanks for the script.

One more question, on your latest script iteration you’re converting the values to GBP, right? And removed the column headers?

It’s portfolio performance: https://www.portfolio-performance.info/en/

I didn’t really do anything special, just followed @kali’s guide a few posts up:

1 Like

Hi,

I have made multiple failed attempts at trying to get the code to work. I have tried both the original script and the altered version posted on 31/08. When I save the script in the editor and hard refresh the google sheet, there is no option to import the script. As I said, I have followed the steps outlined multiple times and have failed on each attempt. Any help is greatly appreciated.

image

without using the menu item, in the script editor you can select the function you want and hit run. Do you see any errors in the when doing so?