Get best of the week

Cómo usar las hojas js y google para convertirse en vecino del club de golf de Bill Gates


Recientemente, han comenzado a aparecer artículos en el centro que comienzan con historias sobre el tiempo libre en el autoaislamiento y, como resultado, han aparecido trolebuses desde un pan. Quizás la administración debería considerar agregar un nuevo centro: autoaislamiento .


Entonces obtuve tiempo libre, que dediqué a analizar mis transacciones en Tinkoff Investments. Hay 2 tipos de personas: algunos construyen arreglos multidimensionales maravillosamente en sus cabezas, los revisan por ciclo en el cuaderno de IPython, mientras que a otros les gusta "sentir" los números, colocándolos en los estantes en Excel. Me atribuyo a la segunda categoría, por lo que ingresé cuidadosamente todas mis ofertas en Hojas de cálculo de Google.


Debajo del corte, le contaré cómo automaticé mi rutina usando Google Apps Script y Tinkoff Investments API.


Antes de llegar al punto, un pequeño glosario de términos que uso en el artículo:


  • TI - Tinkoff Investment
  • Un instrumento es cualquier valor, como acciones, bonos o 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 . , )


Todo el código y un breve tutorial se suben a github


Para aquellos lectores que desean embarcarse en el camino de la inversión, pero no saben por dónde comenzar, puedo aconsejar un curso gratuito de la revista Tinkoff https://journal.tinkoff.ru/pro/invest/ , es breve, informativo y fácil de entender.


Y cuando abra una cuenta de corretaje en TI, bajo mi enlace recibirá una acción por valor de hasta 20,000 rublos como regalo.


Gracias por su atención.

More articles:


All Articles