tbot-v0-6.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tbot - Trade Bot\n",
    "\n",
    " Tbot is a Robot of trade developed to trade cryptourrencies or s&p500 stocks, based on https/json queries. With few adjustments it may help traders to improve their workflow. In this file we shall compile three steps followed at the \"Algorithimic Trading Using Python\" course created by the api developer and completed by the developer of this api. (\"https://freecodecamp.org\")\n",
    " \n",
    "### Library Imports\n",
    "The first thing we need to do is import the open-source software libraries that we'll be using in this api. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests\n",
    "import secrets\n",
    "import xlsxwriter\n",
    "import math\n",
    "import hmac\n",
    "import time\n",
    "import datetime\n",
    "import hashlib\n",
    "import binascii"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Timestamp data\n",
    "Acquires timestamp and stores it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Moved to API Call and further sections for more precise query purposes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### API token, secret key and acess keys\n",
    "API tokens (and other sensitive information) shall be stored in a secrets.py file that doesn't get pushed to your local Git repository. The AcessKey and SecretKey are provided by the server admin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from secrets import SecretKey\n",
    "from secrets import AccessKey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from novadax import RequestClient as NovaClient\n",
    "\n",
    "nova_client = NovaClient(AccessKey, SecretKey)\n",
    "symbol = 'ETH_BRL'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing tht List of currencies, making the API Call and the table Dataframe\n",
    "List of crypto-currencies and table creation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'code': 'A10000', 'data': {'ask': '2000', 'baseVolume24h': '10.0814', 'bid': '1998', 'high24h': '2036.22', 'lastPrice': '1982.45', 'low24h': '1928.19', 'open24h': '1951.64', 'quoteVolume24h': '19907.8842', 'symbol': 'ETH_USDT', 'timestamp': 1613840497172}, 'message': 'Success'}\n"
     ]
    }
   ],
   "source": [
    "#API call\n",
    "symbols = ['ETH_USDT', 'BTC_USDT']\n",
    "symbol = 'ETH_USDT'\n",
    "api_url = f'https://api.novadax.com/v1/market/ticker?symbol={symbol}'\n",
    "drequest = requests.get(api_url).json()\n",
    "print(drequest)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Parse for data then get price\n",
    "dfdata = drequest['data']\n",
    "price = float(dfdata['lastPrice'])\n",
    "baseVolume24h = dfdata['baseVolume24h']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "R$ 5.032490237523734\n"
     ]
    }
   ],
   "source": [
    "#dolar price BRL: 1/(ETH/BRL / ETH/USD)\n",
    "currency = 'R$'\n",
    "dsymbol = 'ETH_BRL'\n",
    "dsymbol_url = f'https://api.novadax.com/v1/market/ticker?symbol={dsymbol}'\n",
    "xdrequest = requests.get(dsymbol_url).json()\n",
    "dt = xdrequest['data']\n",
    "dtprice = float(dt['lastPrice'])\n",
    "dolar_calc = (dtprice / price)\n",
    "dolar_price = 1/dolar_calc\n",
    "print(currency, dolar_price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Currency</th>\n",
       "      <th>Balance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BTT</td>\n",
       "      <td>96.09424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USDT</td>\n",
       "      <td>0.09682579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ETH</td>\n",
       "      <td>0.0674948847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BRL</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DGB</td>\n",
       "      <td>0.0353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LINK</td>\n",
       "      <td>0.0088601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DOGE</td>\n",
       "      <td>0.00881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>XLM</td>\n",
       "      <td>0.00447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NULS</td>\n",
       "      <td>0.0044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IOTA</td>\n",
       "      <td>0.003905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>XRP</td>\n",
       "      <td>0.0036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>XTZ</td>\n",
       "      <td>0.000097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>WAVES</td>\n",
       "      <td>0.0000914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ADA</td>\n",
       "      <td>0.000067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>DASH</td>\n",
       "      <td>0.0000642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>XMR</td>\n",
       "      <td>0.0000562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>BTC</td>\n",
       "      <td>0.00000052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>DOT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DAI</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>BCHA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NPXS</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>DCR</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>BNB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ETC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>BCH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>BSV</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>USDT_ERC20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>TRX</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>LTC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>EOS</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>OMG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>BRZ</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Currency       Balance\n",
       "0          BTT      96.09424\n",
       "1         USDT    0.09682579\n",
       "2          ETH  0.0674948847\n",
       "3          BRL          0.06\n",
       "4          DGB        0.0353\n",
       "5         LINK     0.0088601\n",
       "6         DOGE       0.00881\n",
       "7          XLM       0.00447\n",
       "8         NULS        0.0044\n",
       "9         IOTA      0.003905\n",
       "10         XRP        0.0036\n",
       "11         XTZ      0.000097\n",
       "12       WAVES     0.0000914\n",
       "13         ADA      0.000067\n",
       "14        DASH     0.0000642\n",
       "15         XMR     0.0000562\n",
       "16         BTC    0.00000052\n",
       "17         DOT             0\n",
       "18         DAI             0\n",
       "19        BCHA             0\n",
       "20        NPXS             0\n",
       "21         DCR             0\n",
       "22         BNB             0\n",
       "23         ETC             0\n",
       "24         BCH             0\n",
       "25         BSV             0\n",
       "26  USDT_ERC20             0\n",
       "27         TRX             0\n",
       "28         LTC             0\n",
       "29         EOS             0\n",
       "30         OMG             0\n",
       "31         BRZ             0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#ballance\n",
    "balance = nova_client.get_account_balance()\n",
    "bdata = balance['data']\n",
    "bcolumns = ['Currency', 'Balance']\n",
    "bdataframe = pd.DataFrame(columns = bcolumns,)\n",
    "bs = 0\n",
    "\n",
    "for bsymbol in bdata:\n",
    "    pdis = bdata[bs]\n",
    "    bis = pdis['balance']\n",
    "    bic = pdis['currency']\n",
    "    bdataframe.append(\n",
    "        pd.Series([\n",
    "            bic,\n",
    "            bis,\n",
    "        ], index = bcolumns),\n",
    "        ignore_index = True)\n",
    "    bdataframe = bdataframe.append(\n",
    "    pd.Series([\n",
    "        bic,\n",
    "        bis,\n",
    "    ], index=bcolumns),\n",
    "        ignore_index=True)\n",
    "    bs = bs + 1  \n",
    "\n",
    "bdataframe.sort_values('Balance', ascending = False, inplace = True)\n",
    "bdataframe.reset_index(drop = True, inplace = True)\n",
    "bdataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Currency Price</th>\n",
       "      <th>Base Volume 24h</th>\n",
       "      <th>Number of Shares to Buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ETH_USDT</td>\n",
       "      <td>1982.45</td>\n",
       "      <td>10.0814</td>\n",
       "      <td>9.705231e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BTC_USDT</td>\n",
       "      <td>56268.76</td>\n",
       "      <td>3.886683</td>\n",
       "      <td>3.419328e-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Symbol Currency Price Base Volume 24h  Number of Shares to Buy\n",
       "0  ETH_USDT        1982.45         10.0814             9.705231e-06\n",
       "1  BTC_USDT       56268.76        3.886683             3.419328e-07"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Dataframe get\n",
    "#20210219 instructions: put the above wallet loop inside the below dataframe loop to call 'position' for each\n",
    "#                       currency independently at time.\n",
    "#r4f: https://stackoverflow.com/questions/16729574/how-to-get-a-value-from-a-cell-of-a-dataframe\n",
    "#val = bdataframe['Balance'].values[1]\n",
    "#print(\"U$DT\", val)\n",
    "my_columns = ['Symbol', 'Currency Price', 'Base Volume 24h', 'Number of Shares to Buy']\n",
    "f_dataframe = pd.DataFrame(columns = my_columns)\n",
    "\n",
    "for symbol in symbols:\n",
    "    api_url = f'https://api.novadax.com/v1/market/ticker?symbol={symbol}'\n",
    "    fddata = requests.get(api_url).json()\n",
    "    fdata = fddata['data']\n",
    "    price = float(fdata['lastPrice'])\n",
    "    baseVolume24h = fdata['baseVolume24h']\n",
    "    dolarwal = bdataframe['Balance'].values[1]\n",
    "    shares_to_buy = (float(dolarwal)/float(dolar_price)/float(price))\n",
    "    for i in fdata['symbol'][:1]:\n",
    "        f_dataframe.append(\n",
    "            pd.Series(\n",
    "                [\n",
    "                    symbol,\n",
    "                    price,\n",
    "                    baseVolume24h,\n",
    "                    'N/A'\n",
    "                ]\n",
    "                ,index = my_columns),\n",
    "            ignore_index=True)\n",
    "    \n",
    "    \n",
    "        f_dataframe = f_dataframe.append(\n",
    "                                            pd.Series([symbol, \n",
    "                                                       fdata['lastPrice'], \n",
    "\n",
    "                                                       fdata['baseVolume24h'], \n",
    "                                                       shares_to_buy], \n",
    "                                                      index = my_columns), \n",
    "                                            ignore_index = True)\n",
    "\n",
    "f_dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sending the requisition to the web\n",
    "The content in the order requisition should be sent to the server following the instructions given by the company or individual that holds the coin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Ammount</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ETH_USDT</td>\n",
       "      <td>0.0361</td>\n",
       "      <td>1978.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ETH_USDT</td>\n",
       "      <td>0.0304</td>\n",
       "      <td>1982.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ETH_USDT</td>\n",
       "      <td>0.024</td>\n",
       "      <td>1984.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ETH_USDT</td>\n",
       "      <td>0.018</td>\n",
       "      <td>1991.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ETH_USDT</td>\n",
       "      <td>0.0304</td>\n",
       "      <td>1993.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BTC_USDT</td>\n",
       "      <td>0.000416</td>\n",
       "      <td>56188.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BTC_USDT</td>\n",
       "      <td>0.002077</td>\n",
       "      <td>56268.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BTC_USDT</td>\n",
       "      <td>0.018872</td>\n",
       "      <td>56368.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BTC_USDT</td>\n",
       "      <td>0.490343</td>\n",
       "      <td>56401.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>BTC_USDT</td>\n",
       "      <td>0.207057</td>\n",
       "      <td>56401.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Symbol   Ammount     Price\n",
       "0  ETH_USDT    0.0361   1978.23\n",
       "1  ETH_USDT    0.0304   1982.45\n",
       "2  ETH_USDT     0.024   1984.02\n",
       "3  ETH_USDT     0.018   1991.00\n",
       "4  ETH_USDT    0.0304   1993.01\n",
       "5  BTC_USDT  0.000416  56188.76\n",
       "6  BTC_USDT  0.002077  56268.76\n",
       "7  BTC_USDT  0.018872  56368.17\n",
       "8  BTC_USDT  0.490343  56401.09\n",
       "9  BTC_USDT  0.207057  56401.09"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#orders\n",
    "listtrades = nova_client.list_orders(symbol, 'FILLED', limit=10)\n",
    "ldata = listtrades['data']\n",
    "lindice= ldata[0]\n",
    "amount = lindice['amount']\n",
    "\n",
    "my_columns = ['Symbol', 'Ammount','Price']\n",
    "f_dataframe = pd.DataFrame(columns = my_columns)\n",
    "\n",
    "for symbol in symbols:\n",
    "    listtrades = nova_client.list_trades(symbol, limit=5)\n",
    "    f = 0\n",
    "    for i in listtrades['data']:\n",
    "        ldata = listtrades['data']\n",
    "        lindice = ldata[f]\n",
    "        amount = lindice['amount']\n",
    "        price = float(lindice['price'])\n",
    "        tsp = (lindice['timestamp'])\n",
    "        f = f + 1\n",
    "        f_dataframe.append(\n",
    "                    pd.Series(\n",
    "                    [\n",
    "                        symbol,\n",
    "                        amount,\n",
    "                        price,\n",
    "                    ]\n",
    "                    ,index = my_columns),\n",
    "                ignore_index=True)\n",
    "\n",
    "    \n",
    "        f_dataframe = f_dataframe.append(\n",
    "                pd.Series([\n",
    "                        symbol,\n",
    "                        amount,\n",
    "                        price,\n",
    "                      ]\n",
    "                ,index = my_columns), \n",
    "                ignore_index = True)\n",
    "\n",
    "f_dataframe.sort_values('Price', ascending = True, inplace = True)\n",
    "f_dataframe.reset_index(drop = True, inplace = True)\n",
    "f_dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#sort price [ok]\n",
    "#wallet_amount [ok]\n",
    "#buy lower(wallet_amount - amount)     \n",
    "#sell + profit(constant*market momentum)\n",
    "#if sell = true; [buy lower < sell][(sell - (contant*momentum)/2 or (buy + constant/momentum)/2\n",
    "#P = S - B; SUM(Pi to Pn)  = Pi{(S - D) or (S - B)}\n",
    "#in the if statement one more contidional is needed at the \"or\" statment. If or else(momentum>=x or else)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Format and export xlsx file\n",
    "In the last section of the tbot a xlsx file is saved with the order table for later reading purposes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter('orders.xlsx', engine = 'xlsxwriter')\n",
    "f_dataframe.to_excel(writer, sheet_name = 'orderlist', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "background_color = '#0a0a23'\n",
    "font_color = '#ffffff'\n",
    "\n",
    "string_template = writer.book.add_format(\n",
    "        {\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "dollar_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'$0.00',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "integer_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'0',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "float_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'0',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "percent_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'0.0%',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_formats = {\n",
    "    'A':['Symbol', string_template],\n",
    "    'B':['Amount', float_template],\n",
    "    'C':['Price', dollar_template],\n",
    "}\n",
    "\n",
    "for column in column_formats.keys():\n",
    "    writer.sheets['orderlist'].set_column(f'{column}:{column}', 25, column_formats[column][1])\n",
    "    writer.sheets['orderlist'].write(f'{column}1', column_formats[column][0], column_formats[column][1])\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}