Get best of the week

How to use js and google sheets to become Bill Gates' golf club neighbor


Recently, articles have begun to appear on the hub that begin with stories about free time on self-isolation and, as a result, trolleybuses from a loaf have appeared. Perhaps the administration should consider adding a new hub - Self - isolation ..


So I got free time, which I devoted to analyzing my transactions at Tinkoff Investments. There are 2 types of people: some build multidimensional arrays beautifully in their heads, going through them for-cycle in the IPython Notebook, while others like to "feel" the numbers, putting them on the shelves in Excel. I attribute myself to the second category, so I carefully entered all my deals into Google Sheets.


Under the cut, I’ll tell you how I automated my routine using Google Apps Script and Tinkoff Investments API.


Before we get to the point, a small glossary of terms that I use in the article:


  • TI - Tinkoff Investment
  • An instrument is any security, such as a stock, bond or ETF.
  • Ticker — ID . , , .
  • Figi — Financial Instrument Global Identifier ( ). API- figi.
  • — .


. - , — . : , . /, .


- , . . OvkHabr. , API, .


Google Apps Script


, , Google Sheets, Tools -> Script editor, JavaScript.



OpenAPI


OpenAPI, swagger-ui


http- , . .


http .
?


  1. , figi
  2. ,
  3. ,

, :


class TinkoffClient
class TinkoffClient {
  constructor(token) {
    this.token = token
    this.baseUrl = 'https://api-invest.tinkoff.ru/openapi/'
  }

  _makeApiCall(methodUrl) {
    const url = this.baseUrl + methodUrl
    Logger.log(`[API Call] ${url}`)
    const params = {'escaping': false, 'headers': {'accept': 'application/json', "Authorization": `Bearer ${this.token}`}}
    const response = UrlFetchApp.fetch(url, params)
    if (response.getResponseCode() == 200)
      return JSON.parse(response.getContentText())
  }

  getInstrumentByTicker(ticker) {
    const url = `market/search/by-ticker?ticker=${ticker}`
    const data = this._makeApiCall(url)
    return data.payload.instruments[0]
  }

  getOrderbookByFigi(figi) {
    const url = `market/orderbook?depth=1&figi=${figi}`
    const data = this._makeApiCall(url)
    return data.payload
  }

  getOperations(from, to, figi) {
    // Arguments `from` && `to` should be in ISO 8601 format
    const url = `operations?from=${from}&to=${to}&figi=${figi}`
    const data = this._makeApiCall(url)
    return data.payload.operations
  }
}

const tinkoffClient = new TinkoffClient(OPENAPI_TOKEN)


- , . , yandex YNDX.


Custom Functions


Google Sheets , AVERAGE, SUM, VLOOKUP. , , . , , — .gs . , , , . , , , , .


getPriceByTicker, . (=getPriceByTicker("YNDX")).


figi , , :


function _getFigiByTicker(ticker) {
  const {figi} = tinkoffClient.getInstrumentByTicker(ticker)
  return figi
}

function getPriceByTicker(ticker) {
  const figi = _getFigiByTicker(ticker)
  const {lastPrice} = tinkoffClient.getOrderbookByFigi(figi)
  return lastPrice
}



! , , .



, , . , GAS :


  • ,
    function onEdit(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  sheet.getRange('Z1').setValue(Math.random())
}
  • , , getPriceByTicker
    =getPriceByTicker("YNDX", Z1)

Cache Service


, 2 API-. , , figi . , Apps Script Cache Service. key-value , :


const CACHE = CacheService.getScriptCache()

function _getFigiByTicker(ticker) {
  const cached = CACHE.get(ticker)
  if (cached != null) 
    return cached
  const {figi} = tinkoffClient.getInstrumentByTicker(ticker)
  CACHE.put(ticker, figi)
  return figi
}


. getTrades, API, .


, , . , ISO 8601, API.
Figi _getFigiByTicker, .


function getTrades(ticker, from, to) {
  const figi = _getFigiByTicker(ticker)
  if (!from) {
    from = TRADING_START_AT.toISOString()
  }
  if (!to) {
    const now = new Date()
    to = new Date(now + MILLIS_PER_DAY)
    to = to.toISOString()
  }
  const operations = tinkoffClient.getOperations(from, to, figi)
  ...
}


, Operation :


Operation
"operation": {
    "id": "string",
    "status": "Done",
    "trades": [{
        "tradeId": "string",
        "date": "2019-08-19T18:38:33.131642+03:00",
        "price": 0,
        "quantity": 0
     }],
    "commission": {
      "currency": "RUB",
      "value": 0
    },
    "currency": "RUB",
    "payment": 0,
    "price": 0,
    "quantity": 0,
    "figi": "string",
    "instrumentType": "Stock",
    "isMarginCall": true,
    "date": "2019-08-19T18:38:33.131642+03:00",
    "operationType": "Buy"
  }

- . , : 100 YNDX 2500₽, 40 2500₽, 60 2499₽. , OvkHabr, — trades.


, , , .


,


Pricew=2500∗40+2499∗60100=2499,4



function _calculateTrades(trades) {
  let totalSum = 0
  let totalQuantity = 0
  for (let j in trades) {
    const {quantity, price} = trades[j]
    totalQuantity += quantity
    totalSum += quantity * price
  }
  const weigthedPrice = totalSum / totalQuantity
  return [totalQuantity, totalSum, weigthedPrice]
}


, custom , . , .


, . , , . , " " ( ) . , ( )


const values = [
  ["ID", "Date", "Operation", "Ticker", "Quantity", "Price", "Currency", "SUM", "Commission"], 
]
for (let i=operations.length-1; i>=0; i--) {
  const {operationType, status, trades, id, date, currency, commission} = operations[i]
  if (operationType == "BrokerCommission" || status == "Decline") 
    continue
  let [totalQuantity, totalSum, weigthedPrice] = _calculateTrades(trades) // calculate weighted values
  if (operationType == "Buy") {  // inverse values in a way, that it will be easier to work with
    totalQuantity = -totalQuantity
    totalSum = -totalSum
  }
  values.push([
    id, isoToDate(date), operationType, ticker, totalQuantity, weigthedPrice, currency, totalSum, commission.value
  ])
}

values. :


getTrades
function isoToDate(dateStr){
  // How to format date string so that google scripts recognizes it?
  // https://stackoverflow.com/a/17253060
  const str = dateStr.replace(/-/,'/').replace(/-/,'/').replace(/T/,' ').replace(/\+/,' \+').replace(/Z/,' +00')
  return new Date(str)
}

function _calculateTrades(trades) {
  let totalSum = 0
  let totalQuantity = 0
  for (let j in trades) {
    const {quantity, price} = trades[j]
    totalQuantity += quantity
    totalSum += quantity * price
  }
  const weigthedPrice = totalSum / totalQuantity
  return [totalQuantity, totalSum, weigthedPrice]
}

function getTrades(ticker, from, to) {
  const figi = _getFigiByTicker(ticker)
  if (!from) {
    from = TRADING_START_AT.toISOString()
  }
  if (!to) {
    const now = new Date()
    to = new Date(now + MILLIS_PER_DAY)
    to = to.toISOString()
  }
  const operations = tinkoffClient.getOperations(from, to, figi)

  const values = [
    ["ID", "Date", "Operation", "Ticker", "Quantity", "Price", "Currency", "SUM", "Commission"], 
  ]
  for (let i=operations.length-1; i>=0; i--) {
    const {operationType, status, trades, id, date, currency, commission} = operations[i]
    if (operationType == "BrokerCommission" || status == "Decline") 
      continue
    let [totalQuantity, totalSum, weigthedPrice] = _calculateTrades(trades) // calculate weighted values
    if (operationType == "Buy") {  // inverse values in a way, that it will be easier to work with
      totalQuantity = -totalQuantity
      totalSum = -totalSum
    }
    values.push([
      id, isoToDate(date), operationType, ticker, totalQuantity, weigthedPrice, currency, totalSum, commission.value
    ])
  }
  return values
}

:




API , , Google Apps Script, Google Sheets . , )


All code and a short how-to are uploaded to github


For those readers who want to embark on the road of investing, but do not know where to start, I can advise a free course from Tinkoff Magazine https://journal.tinkoff.ru/pro/invest/ - it is short, informative and easy to understand.


And when you open a brokerage account in TI, under my link you will receive a share worth up to 20,000 rubles as a gift.


Thank you for attention.

More articles:


All Articles