Get best of the week

Wie man js und Google Sheets benutzt, um Bill Gates 'Nachbar im Golfclub zu werden


In letzter Zeit erscheinen auf dem Hub Artikel, die mit Geschichten über Freizeit zur Selbstisolation beginnen, und infolgedessen sind Obusse von einem Laib erschienen. Vielleicht sollte die Verwaltung in Betracht ziehen, einen neuen Hub hinzuzufügen - Selbstisolation .


So bekam ich Freizeit, die ich der Analyse meiner Transaktionen bei Tinkoff Investments widmete. Es gibt zwei Arten von Personen: Einige erstellen mehrdimensionale Arrays wunderschön in ihren Köpfen und gehen sie im IPython-Notizbuch zyklisch durch, während andere die Zahlen gerne "fühlen" und sie in Excel in die Regale stellen. Ich ordne mich der zweiten Kategorie zu und habe daher alle meine Angebote sorgfältig in Google Sheets eingegeben.


Im Folgenden werde ich Ihnen erläutern, wie ich meine Routine mithilfe von Google Apps Script und Tinkoff Investments API automatisiert habe.


Bevor wir zum Punkt kommen, ein kleines Glossar der Begriffe, die ich im Artikel verwende:


  • TI - Tinkoff Investition
  • Ein Instrument ist ein Wertpapier wie eine Aktie, eine Anleihe oder ein 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 . , )


Der gesamte Code und eine kurze Anleitung werden auf github hochgeladen


Für diejenigen Leser, die sich auf den Weg des Investierens machen möchten, aber nicht wissen, wo sie anfangen sollen - ich kann einen kostenlosen Kurs vom Tinkoff Magazine https://journal.tinkoff.ru/pro/invest/ empfehlen - es ist kurz, informativ und leicht zu verstehen.


Und wenn Sie ein Maklerkonto in TI eröffnen, erhalten Sie über meinen Link einen Anteil im Wert von bis zu 20.000 Rubel als Geschenk.


Vielen Dank für Ihre Aufmerksamkeit.

More articles:


All Articles