Clever Geek Handbook

We track the status of our portfolio with the broker "Tinkoff Investments" through Google Sheets

Surely many keep track of transactions and monitor the status of their portfolio in Google Sheets or Excel. In the past, I had to manually enter information about each instrument bought or sold, and this took up a significant part of my time. Then I wanted to automate this process and I started looking for ways to implement this idea.



I found a user articleErhoSen... From it, I learned that the Tinkoff Investments broker offers its clients an API for interacting with a brokerage account with an individual investment account (IIS). With its help, you can get a list of stocks, currency pairs, find out the price of a security by ticker, view a list of account transactions, get information about your portfolio, etc. However, I lacked the existing functionality of the script and I decided to add it by adding my functions there.



Getting a token



API work is done through a token. The process of obtaining a token is described in the documentation on GitHub . To get it you need:



  1. Go to Tinkoff's website and log into your account.
  2. Make sure that the function “Confirmation of deals by code” is disabled.
  3. Scroll down the page to the item token for OpenAPI and click on "Token for trading"
  4. Copy and save the token. It is displayed only once, but you can issue an unlimited number of tokens.


After receiving the token, you can start working with the table. Follow the link and copy the table in which you will keep records.



Paste your token into cell B1 in the Settings sheet.



Important! After inserting the token, do not delete or rename the “Settings” sheet. If the current dollar rate appears in cell B3 - congratulations, everything is ready to go.



The main functions of the script :



  • getPrice () - get the current value of the instrument by the ticker
  • getTrades (ticker) - get a list of all transactions for a specific instrument. In brackets, you must indicate the ticker of the instrument for which you want to see the list of operations
  • getAllTrades() — .
  • getPortfolio() —
  • getCurrencies() —
  • getTradesIIS (ticker) — (). ,
  • getAllTradesIIS() — ().
  • getIISPort() —
  • getCurrenciesIIS() —
  • getUSDval() — ( )


After receiving the necessary data, you can copy the obtained values ​​and continue working with them on other sheets or in another table, including Excel-e.





An example of how the getPortfolio function works to get a portfolio



Manual data update



By default, Google does not provide a tool to update custom scripts, so I had to use a third-party solution. In order to be able to update data by clicking, when using functions in brackets, you need to set the cell $ Z $ 1, which records the current date and time, which is later used for manual updating. Below I will present a piece of code that is responsible for updating the data. 



function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  var entries = [{
    name : "",
    functionName : "refresh"
  }]
  sheet.addMenu("TI", entries)
};

function refresh() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('Z1').setValue(new Date().toTimeString());
}


First, you need to click on the Update button in the TI submenu (appears to the right of the Help tab).

After that, a warning window will appear:







Click Continue . Next, another warning window will appear, where you will need to click on Additional settings and in the field that opens, select Go to the "Tinkoff Invest" page (unsafe) :







These actions must be performed because the script to change the value of cell Z1 must first access the current table so that it is performed only in it. After that, we have the opportunity to update the data in the table upon request. An example of a manual update for the getPrice function ("AMD"; $ Z $ 1):







ATTENTION!The developer of this script will not have access to your Google account, the list of tables in Drive, or the token. If you are worried about the safety of your data, you can copy my script and paste it into your table. To do this, in Google Sheets, open your spreadsheet and in the top menu, click on Tools , and then on the Script Editor .



This will open the google scripting environment. Erase the default code in the window, copy the code from the link and paste it into the window.



After that, save the script, giving any name to the project and the script itself. Then go back to the table, create a “Settings” sheet and in cell B1 paste your OpenAPI token. After that you can work with the table.



Conclusion



I want to express my gratitude ErhoSenfor his article and the source code, which was modified by me.



Also for personal use, I created a Telegram bot, with plans for the future to bring this idea to mind if the idea finds a response. The bot is convenient in cases when you do not want to waste time entering the Tinkoff Investments application, who often sits at a laptop and does not want to constantly log in to the site, or to store the history of changes in the contents of the portfolio.


More articles:


All Articles