Buda_To_Spreadsheet.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Buda-To-Spreadsheet.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyOqe8i2xcjNNJ5YE9amVRRD",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/fnmendez/buda.com_to_google_spreadsheet/blob/main/Buda_To_Spreadsheet.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s2rwKR74EWnv"
      },
      "source": [
        "# Buda.com a Google Spreadsheet con un click (read-only API-Key)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p9kPZ2BuFnSQ"
      },
      "source": [
        "Este jupyter obtendrá todas las órdenes que has abierto en Buda en las que has pagado o recibido algún monto (parcial o completamente transadas), y las escribirá en el Google Spreadsheet que indiques.\n",
        "\n",
        "A las órdenes les adjuntará los depósitos y retiros, adaptándolos a su formato. Por ejemplo, las órdenes tienen tipos \"Bid\" y \"Ask\", entonces añadimos \"Dep\" y \"Wit\" de Deposit y Withdrawal. De esta manera escribiremos todos los flujos de tus divisas de Buda en el spreadsheet.\n",
        "\n",
        "La manera en la que se sobreescribe el spreadsheet te permitirá conservar los formatos que hayas definido, y no se eliminará el contenido que no se sobreescriba."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dbCSyh_tEk9R"
      },
      "source": [
        "## 1. Autenticación"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rXHo-tGQCcT5"
      },
      "source": [
        "# Reemplaza con los valores que correspondan\n",
        "\n",
        "# El nombre de tu spreadsheet que debe estar en la misma carpeta que este archivo y ya creado\n",
        "google_spreadsheet_name = \"Your Buda Index\"\n",
        "\n",
        "# Tus claves de Buda.com: genéralas en Configuración > API Keys, no usaremos permisos de trading ni de retiros así que desactívalos\n",
        "api_key = \"your_api_key\"\n",
        "secret = \"your_api_secret\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "arcpSrTJB7pz"
      },
      "source": [
        "# Ingresa sesión con tu cuenta Google (necesitarás hacer OAuth, mira los logs)\n",
        "\n",
        "!pip install --upgrade gspread\n",
        "\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "\n",
        "import gspread\n",
        "from oauth2client.client import GoogleCredentials\n",
        "\n",
        "gc = gspread.authorize(GoogleCredentials.get_application_default())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FS_76ID2EtQM"
      },
      "source": [
        "## 2. La magia"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8SyBB9iVDeQ1"
      },
      "source": [
        "### 2.1 Este inciso solo es necesario ejecutarlo la primera vez"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pU-uyWt58oFw"
      },
      "source": [
        "import base64\n",
        "import hmac\n",
        "import time\n",
        "import requests.auth\n",
        "from datetime import datetime"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CNRc7SXt7l-0"
      },
      "source": [
        "class BudaHMACAuth(requests.auth.AuthBase):\n",
        "    \"\"\"Adjunta la autenticación HMAC de Buda al objeto Request.\"\"\"\n",
        "\n",
        "    def __init__(self, api_key: str, secret: str):\n",
        "        self.api_key = api_key\n",
        "        self.secret = secret\n",
        "\n",
        "    def get_nonce(self) -> str:\n",
        "        # 1. Generar un nonce (timestamp en microsegundos)\n",
        "        return str(int(time.time() * 1e6))\n",
        "\n",
        "    def sign(self, r, nonce: str) -> str:\n",
        "        # 2. Preparar string para firmar\n",
        "        components = [r.method, r.path_url]\n",
        "        if r.body:\n",
        "            encoded_body = base64.b64encode(r.body).decode()\n",
        "            components.append(encoded_body)\n",
        "        components.append(nonce)\n",
        "        msg = ' '.join(components)\n",
        "        # 3. Obtener la firma\n",
        "        h = hmac.new(key=self.secret.encode(),\n",
        "                        msg=msg.encode(),\n",
        "                        digestmod='sha384')\n",
        "        signature = h.hexdigest()\n",
        "        return signature\n",
        "\n",
        "    def __call__(self, r):\n",
        "        nonce = self.get_nonce()\n",
        "        signature = self.sign(r, nonce)\n",
        "        # 4. Adjuntar API-KEY, nonce y firma al header del request\n",
        "        r.headers['X-SBTC-APIKEY'] = self.api_key\n",
        "        r.headers['X-SBTC-NONCE'] = nonce\n",
        "        r.headers['X-SBTC-SIGNATURE'] = signature\n",
        "        return r"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zMLraU7s_jgn"
      },
      "source": [
        "# Utils para API de Buda\n",
        "\n",
        "auth = BudaHMACAuth(api_key, secret)\n",
        "\n",
        "# Obtener órdenes\n",
        "def get_orders(market_id = 'btc-clp', state = None):\n",
        "  url = f'https://www.buda.com/api/v2/markets/{market_id}/orders'\n",
        "  response = requests.get(url, auth=auth, params={\n",
        "      'state': state,\n",
        "      'per': 300,\n",
        "      'page': 1,\n",
        "  })\n",
        "  return response.json()[\"orders\"]\n",
        "\n",
        "# Obtener depósitos\n",
        "def get_deposits(currency = 'btc', state = 'confirmed'):\n",
        "  url = f'https://www.buda.com/api/v2/currencies/{currency}/deposits'\n",
        "  response = requests.get(url, auth=auth, params={\n",
        "      'state': state,\n",
        "      'per': 300,\n",
        "      'page': 1,\n",
        "  })\n",
        "  return response.json()[\"deposits\"]\n",
        "\n",
        "# Obtener retiros\n",
        "def get_withdrawals(currency = 'btc', state = 'confirmed'):\n",
        "  url = f'https://www.buda.com/api/v2/currencies/{currency}/withdrawals'\n",
        "  response = requests.get(url, auth=auth, params={\n",
        "      'state': state,\n",
        "      'per': 300,\n",
        "      'page': 1,\n",
        "  })\n",
        "  return response.json()[\"withdrawals\"]\n",
        "\n",
        "# Parsear depósitos o retiros como si fueran órdenes\n",
        "\n",
        "def parseToOrder(kind, created_at, amount, currency, fee, state):\n",
        "  ret = {}\n",
        "  ret[\"created_at\"] = created_at\n",
        "  ret[\"type\"] = kind\n",
        "  ret[\"price_type\"] = \"\"\n",
        "  ret[\"state\"] = state\n",
        "  ret[\"original_amount\"] = amount\n",
        "  ret[\"traded_amount\"] = amount\n",
        "  ret[\"total_exchanged\"] = [\"\", \"\"]\n",
        "  ret[\"paid_fee\"] = fee\n",
        "  ret[\"amount\"] = [\"0.0\", currency]\n",
        "  ret[\"_price\"] = [\"\", \"\"]\n",
        "  return ret"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "F8AKVK4Kd81F"
      },
      "source": [
        "# Utils para API de Google Spreadsheets\n",
        "\n",
        "# rowcol(1,1) => A1\n",
        "rowcol = gspread.utils.rowcol_to_a1\n",
        "\n",
        "# datetime parser\n",
        "readtime = lambda x: datetime.strptime(x, \"%Y-%m-%dT%H:%M:%S.%fZ\") # TODO: aceptar distintos a Zulu time (+00:00)\n",
        "writetime = lambda x: x.strftime(\"%Y-%m-%d %H:%M\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QmC3BNnrD-dw"
      },
      "source": [
        "### 2.2 Ejecuta los siguientes bloques para actualizar el sheet"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lL3tY0bzpH4u"
      },
      "source": [
        "# Obtenemos las órdenes y filtramos las que no se pagó/recibió algún monto\n",
        "\n",
        "orders_raw = get_orders('btc-clp', None)\n",
        "orders = list(filter(lambda x: float(x[\"total_exchanged\"][0]) != 0.0, orders_raw))\n",
        "for o in orders:\n",
        "  o[\"_price\"] = [\n",
        "    float(o[\"total_exchanged\"][0])/float(o[\"traded_amount\"][0]),\n",
        "    f'{o[\"total_exchanged\"][1]}/{o[\"traded_amount\"][1]}'\n",
        "  ]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FKRaxZT-udwg"
      },
      "source": [
        "# Obtenemos los depósitos y retiros y los parseamos para mezclarlos con las órdenes\n",
        "\n",
        "deposits_raw = get_deposits('btc', 'confirmed')\n",
        "deposits = []\n",
        "for dr in deposits_raw:\n",
        "  aux = parseToOrder(\"Dep\", dr[\"created_at\"], dr[\"amount\"], dr[\"currency\"], dr[\"fee\"], dr[\"state\"])\n",
        "  deposits.append(aux)\n",
        "\n",
        "withdrawals_raw = get_withdrawals('btc', 'confirmed')\n",
        "withdrawals = []\n",
        "for wr in withdrawals_raw:\n",
        "  aux = parseToOrder(\"Wit\", wr[\"created_at\"], wr[\"amount\"], wr[\"currency\"], wr[\"fee\"], wr[\"state\"])\n",
        "  withdrawals.append(aux)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UkxC48OR0RGf"
      },
      "source": [
        "# Juntamos órdenes, depósitos y retiros en una sola variable\n",
        "\n",
        "super_data = orders + deposits + withdrawals"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hERlLiVYop3b"
      },
      "source": [
        "# Parámetros posicionales para el sheet\n",
        "HRC = (1,1) # posición inicial de los headers\n",
        "ORC = (HRC[0]+1,HRC[1]) # poosición inicial de las órdenes\n",
        "\n",
        "# Abrimos el archivo de Google Spreadsheets, específicamente el primer sheet (la primera hoja) [puedes investigar cómo escribir en otra ;)]\n",
        "worksheet = gc.open(google_spreadsheet_name).sheet1"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pfPUZdi__k8v"
      },
      "source": [
        "# Definir Headers\n",
        "\n",
        "# (Texto-Celda, Nombre-Llave, Tipo)\n",
        "HEADERS = [\n",
        "    (\"Fecha\", \"created_at\", \"date\"),\n",
        "    (\"Tipo\", \"type\", \"string\"),\n",
        "    (\"Modo\", \"price_type\", \"string\"),\n",
        "    (\"Estado\", \"state\", \"string\"),\n",
        "    (\"\", \"original_amount\", \"currency\"),\n",
        "    (\"Monto Inicial\", \"original_amount\", \"amount\"),\n",
        "    (\"\", \"traded_amount\", \"currency\"),\n",
        "    (\"Monto Transado\", \"traded_amount\", \"amount\"),\n",
        "    (\"\", \"total_exchanged\", \"currency\"),\n",
        "    (\"Monto Pagado\", \"total_exchanged\", \"amount\"),\n",
        "    (\"\", \"paid_fee\", \"currency\"),\n",
        "    (\"Comisión\", \"paid_fee\", \"amount\"),\n",
        "    (\"\", \"amount\", \"currency\"),\n",
        "    (\"No Transado\", \"amount\", \"amount\"),\n",
        "    (\"\", \"_price\", \"currency\"),\n",
        "    (\"Precio\", \"_price\", \"amount\") # este lo calculamos nosotros como exchanged/traded\n",
        "] # unused: id, market_id, account_id, source, fee_currency, limit\n",
        "\n",
        "# Tip: los `currency` y los `amount` son arreglos del estilo [5.54420000, \"BTC\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jHTc1ZnlrKVX"
      },
      "source": [
        "# Escribimos los headers\n",
        "header_cells = worksheet.range(f'{rowcol(HRC[0],HRC[1])}:{rowcol(HRC[0],HRC[1]+len(HEADERS)-1)}')\n",
        "for i, header_cell in enumerate(header_cells):\n",
        "  header_cell.value = HEADERS[i][0]\n",
        "worksheet.update_cells(header_cells)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0W8Z6eShfeEg"
      },
      "source": [
        "# Escribimos los datos de las órdenes, depósitos y retiros\n",
        "for i, sd in enumerate(super_data):\n",
        "  sd_cells = worksheet.range(f'{rowcol(ORC[0]+i,ORC[1])}:{rowcol(ORC[0]+i,ORC[1]+len(HEADERS)-1)}')\n",
        "  for j, sd_cell in enumerate(sd_cells):\n",
        "    cur = HEADERS[j]\n",
        "    if cur[2] == \"date\":\n",
        "      sd_cell.value = writetime(readtime(super_data[i][cur[1]]))\n",
        "    elif cur[2] == \"string\":\n",
        "      sd_cell.value = super_data[i][cur[1]]\n",
        "    elif cur[2] == \"currency\":\n",
        "      sd_cell.value = super_data[i][cur[1]][1]\n",
        "    elif cur[2] == \"amount\":\n",
        "      sd_cell.value = super_data[i][cur[1]][0]\n",
        "  # \"USER_ENTERED\" nos permite que el output considere el formato que estaba definido en la celda\n",
        "  # si se desea que el output sea tal cual el input, usar \"RAW\"\n",
        "  worksheet.update_cells(sd_cells, value_input_option=\"USER_ENTERED\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UpFWb5_oz_S8"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j2CI6SarEqLq"
      },
      "source": [
        "## 3. Trabajo futuro"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "woFaEk4pEuC9"
      },
      "source": [
        "- Crear spreadsheet si no existe\n",
        "- Paginar resultados (si se tienen más de 300)\n",
        "- Agregar análisis de los datos"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4WA-j7u35Sc2"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}