{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Code taken and adapted from: https://github.com/gosuto-ai/candlestick_retriever/blob/master/main.py\n\nMain updates:\n- only keeping relevant tickers\n- removing the preprocessing dependency","metadata":{}},{"cell_type":"markdown","source":"# Imports and metadata","metadata":{}},{"cell_type":"code","source":"import json\nimport os\nimport random\nimport subprocess\nimport time\nfrom datetime import date, datetime, timedelta\nfrom datetime import date\n\n\nimport requests\nimport pandas as pd\nimport numpy as np\n\nAPI_BASE = 'https://api.binance.com/api/v3/'\n\nLABELS = [\n    'open_time',\n    'open',\n    'high',\n    'low',\n    'close',\n    'volume',\n    'close_time',\n    'quote_asset_volume',\n    'number_of_trades',\n    'taker_buy_base_asset_volume',\n    'taker_buy_quote_asset_volume',\n    'ignore'\n]\n\nMETADATA = {\n    'id': 'lucasmorin/G-research-binance-history',\n    'title': 'G-research-binance-history',\n    'isPrivate': False,\n    'licenses': [{'name': 'other'}],\n    'keywords': [\n        'business',\n        'finance',\n        'investing',\n        'currencies and foreign exchange'\n    ],\n    'collaborators': [],\n    'data': []\n}","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:38:58.927728Z","iopub.execute_input":"2021-11-06T16:38:58.927992Z","iopub.status.idle":"2021-11-06T16:38:58.935594Z","shell.execute_reply.started":"2021-11-06T16:38:58.927965Z","shell.execute_reply":"2021-11-06T16:38:58.934665Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# tools from above mentionned github","metadata":{}},{"cell_type":"code","source":"def write_metadata(n_count):\n    \"\"\"Write the metadata file dynamically so we can include a pair count.\"\"\"\n    METADATA['subtitle'] = f'1 minute candlesticks for all {n_count} cryptocurrency pairs'\n    METADATA['description'] = f\"\"\"### Introduction\\n\\nThis is a collection of all 1 minute candlesticks of all cryptocurrency pairs on [Binance.com](https://binance.com). All {n_count} of them are included. Both retrieval and uploading the data is fully automated—see [this GitHub repo](https://github.com/gosuto-ai/candlestick_retriever).\\n\\n### Content\\n\\nFor every trading pair, the following fields from [Binance's official API endpoint for historical candlestick data](https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md#klinecandlestick-data) are saved into a Parquet file:\\n\\n```\\n #   Column                        Dtype         \\n---  ------                        -----         \\n 0   open_time                     datetime64[ns]\\n 1   open                          float32       \\n 2   high                          float32       \\n 3   low                           float32       \\n 4   close                         float32       \\n 5   volume                        float32       \\n 6   quote_asset_volume            float32       \\n 7   number_of_trades              uint16        \\n 8   taker_buy_base_asset_volume   float32       \\n 9   taker_buy_quote_asset_volume  float32       \\ndtypes: datetime64[ns](1), float32(8), uint16(1)\\n```\\n\\nThe dataframe is indexed by `open_time` and sorted from oldest to newest. The first row starts at the first timestamp available on the exchange, which is July 2017 for the longest running pairs.\\n\\nHere are two simple plots based on a single file; one of the opening price with an added indicator (MA50) and one of the volume and number of trades:\\n\\n![](https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F2234678%2Fb8664e6f26dc84e9a40d5a3d915c9640%2Fdownload.png?generation=1582053879538546&alt=media)\\n![](https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F2234678%2Fcd04ed586b08c1576a7b67d163ad9889%2Fdownload-1.png?generation=1582053899082078&alt=media)\\n\\n### Inspiration\\n\\nOne obvious use-case for this data could be technical analysis by adding indicators such as moving averages, MACD, RSI, etc. Other approaches could include backtesting trading algorithms or computing arbitrage potential with other exchanges.\\n\\n### License\\n\\nThis data is being collected automatically from crypto exchange Binance.\"\"\"\n    with open('compressed/dataset-metadata.json', 'w') as file:\n        json.dump(METADATA, file, indent=4)\n\ndef get_batch(symbol, interval='1m', start_time=0, limit=1000):\n    \"\"\"Use a GET request to retrieve a batch of candlesticks. Process the JSON into a pandas\n    dataframe and return it. If not successful, return an empty dataframe.\n    \"\"\"\n\n    params = {\n        'symbol': symbol,\n        'interval': interval,\n        'startTime': start_time,\n        'limit': limit\n    }\n    try:\n        # timeout should also be given as a parameter to the function\n        response = requests.get(f'{API_BASE}klines', params, timeout=30)\n    except requests.exceptions.ConnectionError:\n        print('Connection error, Cooling down for 5 mins...')\n        time.sleep(5 * 60)\n        return get_batch(symbol, interval, start_time, limit)\n    \n    except requests.exceptions.Timeout:\n        print('Timeout, Cooling down for 5 min...')\n        time.sleep(5 * 60)\n        return get_batch(symbol, interval, start_time, limit)\n    \n    except requests.exceptions.ConnectionResetError:\n        print('Connection reset by peer, Cooling down for 5 min...')\n        time.sleep(5 * 60)\n        return get_batch(symbol, interval, start_time, limit)\n\n    if response.status_code == 200:\n        return pd.DataFrame(response.json(), columns=LABELS)\n    print(f'Got erroneous response back: {response}')\n    return pd.DataFrame([])\n\n\ndef all_candles_to_csv(base, quote, interval='1m'):\n    \"\"\"Collect a list of candlestick batches with all candlesticks of a trading pair,\n    concat into a dataframe and write it to CSV.\n    \"\"\"\n\n    # see if there is any data saved on disk already\n    try:\n        batches = [pd.read_csv(f'data/{base}-{quote}.csv')]\n        last_timestamp = batches[-1]['open_time'].max()\n    except FileNotFoundError:\n        batches = [pd.DataFrame([], columns=LABELS)]\n        last_timestamp = 0\n    old_lines = len(batches[-1].index)\n\n    # gather all candlesticks available, starting from the last timestamp loaded from disk or 0\n    # stop if the timestamp that comes back from the api is the same as the last one\n    previous_timestamp = None\n\n    while previous_timestamp != last_timestamp:\n        # stop if we reached data from today\n        if date.fromtimestamp(last_timestamp / 1000) >= date.today():\n            break\n\n        previous_timestamp = last_timestamp\n\n        new_batch = get_batch(\n            symbol=base+quote,\n            interval=interval,\n            start_time=last_timestamp+1\n        )\n\n        # requesting candles from the future returns empty\n        # also stop in case response code was not 200\n        if new_batch.empty:\n            break\n\n        last_timestamp = new_batch['open_time'].max()\n\n        # sometimes no new trades took place yet on date.today();\n        # in this case the batch is nothing new\n        if previous_timestamp == last_timestamp:\n            break\n\n        batches.append(new_batch)\n        last_datetime = datetime.fromtimestamp(last_timestamp / 1000)\n\n        covering_spaces = 20 * ' '\n        print(datetime.now(), base, quote, interval, str(last_datetime)+covering_spaces, end='\\r', flush=True)\n\n    # write clean version of csv to parquet\n    parquet_name = f'{base}-{quote}.parquet'\n    full_path = f'compressed/{parquet_name}'\n    df = pd.concat(batches, ignore_index=True)\n    df = quick_clean(df)\n    write_raw_to_parquet(df, full_path)\n    METADATA['data'].append({\n        'description': f'All trade history for the pair {base} and {quote} at 1 minute intervals. Counts {df.index.size} records.',\n        'name': parquet_name,\n        'totalBytes': os.stat(full_path).st_size,\n        'columns': []\n    })\n\n    # in the case that new data was gathered write it to disk\n    if len(batches) > 1:\n        df.to_csv(f'data/{base}-{quote}.csv', index=False)\n        return len(df.index) - old_lines\n    return 0\n\ndef set_dtypes(df):\n    \"\"\"\n    set datetimeindex and convert all columns in pd.df to their proper dtype\n    assumes csv is read raw without modifications; pd.read_csv(csv_filename)\"\"\"\n\n    df['open_time'] = pd.to_datetime(df['open_time'], unit='ms')\n    df = df.set_index('open_time', drop=True)\n\n    df = df.astype(dtype={\n        'open': 'float64',\n        'high': 'float64',\n        'low': 'float64',\n        'close': 'float64',\n        'volume': 'float64',\n        'close_time': 'datetime64[ms]',\n        'quote_asset_volume': 'float64',\n        'number_of_trades': 'int64',\n        'taker_buy_base_asset_volume': 'float64',\n        'taker_buy_quote_asset_volume': 'float64',\n        'ignore': 'float64'\n    })\n\n    return df\n\n\ndef set_dtypes_compressed(df):\n    \"\"\"Create a `DatetimeIndex` and convert all critical columns in pd.df to a dtype with low\n    memory profile. Assumes csv is read raw without modifications; `pd.read_csv(csv_filename)`.\"\"\"\n\n    df['open_time'] = pd.to_datetime(df['open_time'], unit='ms')\n    df = df.set_index('open_time', drop=True)\n\n    df = df.astype(dtype={\n        'open': 'float32',\n        'high': 'float32',\n        'low': 'float32',\n        'close': 'float32',\n        'volume': 'float32',\n        'number_of_trades': 'uint16',\n        'quote_asset_volume': 'float32',\n        'taker_buy_base_asset_volume': 'float32',\n        'taker_buy_quote_asset_volume': 'float32'\n    })\n\n    return df\n\n\ndef assert_integrity(df):\n    \"\"\"make sure no rows have empty cells or duplicate timestamps exist\"\"\"\n\n    assert df.isna().all(axis=1).any() == False\n    assert df['open_time'].duplicated().any() == False\n\n\ndef quick_clean(df):\n    \"\"\"clean a raw dataframe\"\"\"\n\n    # drop dupes\n    dupes = df['open_time'].duplicated().sum()\n    if dupes > 0:\n        df = df[df['open_time'].duplicated() == False]\n\n    # sort by timestamp, oldest first\n    df.sort_values(by=['open_time'], ascending=False)\n\n    # just a doublcheck\n    assert_integrity(df)\n\n    return df\n\n\ndef write_raw_to_parquet(df, full_path):\n    \"\"\"takes raw df and writes a parquet to disk\"\"\"\n\n    # some candlesticks do not span a full minute\n    # these points are not reliable and thus filtered\n    df = df[~(df['open_time'] - df['close_time'] != -59999)]\n\n    # `close_time` column has become redundant now, as is the column `ignore`\n    df = df.drop(['close_time', 'ignore'], axis=1)\n\n    df = set_dtypes_compressed(df)\n\n    # give all pairs the same nice cut-off\n    df = df[df.index < str(date.today())]\n\n    df.to_parquet(full_path)\n\n\ndef groom_data(dirname='data'):\n    \"\"\"go through data folder and perform a quick clean on all csv files\"\"\"\n\n    for filename in os.listdir(dirname):\n        if filename.endswith('.csv'):\n            full_path = f'{dirname}/{filename}'\n            quick_clean(pd.read_csv(full_path)).to_csv(full_path)\n\n\ndef compress_data(dirname='data'):\n    \"\"\"go through data folder and rewrite csv files to parquets\"\"\"\n\n    os.makedirs('compressed', exist_ok=True)\n    for filename in os.listdir(dirname):\n        if filename.endswith('.csv'):\n            full_path = f'{dirname}/{filename}'\n\n            df = pd.read_csv(full_path)\n\n            new_filename = filename.replace('.csv', '.parquet')\n            new_full_path = f'compressed/{new_filename}'\n            write_raw_to_parquet(df, new_full_path)","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:54:53.347470Z","iopub.execute_input":"2021-11-06T16:54:53.347862Z","iopub.status.idle":"2021-11-06T16:54:53.370165Z","shell.execute_reply.started":"2021-11-06T16:54:53.347835Z","shell.execute_reply":"2021-11-06T16:54:53.369150Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_symbols = pd.DataFrame(requests.get(f'{API_BASE}exchangeInfo').json()['symbols'])","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:54:55.648283Z","iopub.execute_input":"2021-11-06T16:54:55.648712Z","iopub.status.idle":"2021-11-06T16:54:56.849692Z","shell.execute_reply.started":"2021-11-06T16:54:55.648667Z","shell.execute_reply":"2021-11-06T16:54:56.849012Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dict_ticker = {'Bitcoin Cash':'BCH',\n'Binance Coin':'BNB',\n'Bitcoin':'BTC',\n'EOS.IO':'EOS',\n'Ethereum Classic':'ETC',\n'Ethereum':'ETH',\n'Litecoin':'LTC',\n'Monero':'XMR',\n'TRON':'TRX',\n'Stellar':'XLM',\n'Cardano':'ADA',\n'IOTA':'IOTA',\n'Maker':'MKR',\n'Dogecoin':'DOGE'}","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:54:58.542412Z","iopub.execute_input":"2021-11-06T16:54:58.543082Z","iopub.status.idle":"2021-11-06T16:54:58.548450Z","shell.execute_reply.started":"2021-11-06T16:54:58.543034Z","shell.execute_reply":"2021-11-06T16:54:58.547653Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Check if all assets are available","metadata":{}},{"cell_type":"code","source":"for a in dict_ticker:\n    quoteAssetsa = all_symbols[all_symbols.baseAsset == dict_ticker[a]].quoteAsset.unique()\n    USDquoteAssetsa = [qA for qA in quoteAssetsa if 'USD' in qA]\n    print(USDquoteAssetsa)","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:55:00.648152Z","iopub.execute_input":"2021-11-06T16:55:00.649124Z","iopub.status.idle":"2021-11-06T16:55:00.673745Z","shell.execute_reply.started":"2021-11-06T16:55:00.649085Z","shell.execute_reply":"2021-11-06T16:55:00.672728Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can choose between 'USDT' and 'BUSD' to get dollar price.","metadata":{}},{"cell_type":"code","source":"quote = 'BUSD'","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:55:02.353705Z","iopub.execute_input":"2021-11-06T16:55:02.354247Z","iopub.status.idle":"2021-11-06T16:55:02.357484Z","shell.execute_reply.started":"2021-11-06T16:55:02.354210Z","shell.execute_reply":"2021-11-06T16:55:02.356856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_pairs = [(dict_ticker[a],quote) for a in dict_ticker]","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:55:03.220435Z","iopub.execute_input":"2021-11-06T16:55:03.221019Z","iopub.status.idle":"2021-11-06T16:55:03.224877Z","shell.execute_reply.started":"2021-11-06T16:55:03.220979Z","shell.execute_reply":"2021-11-06T16:55:03.224225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_pairs","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:55:03.890344Z","iopub.execute_input":"2021-11-06T16:55:03.890653Z","iopub.status.idle":"2021-11-06T16:55:03.897330Z","shell.execute_reply.started":"2021-11-06T16:55:03.890618Z","shell.execute_reply":"2021-11-06T16:55:03.896445Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# make sure data folders exist\nos.makedirs('data', exist_ok=True)\nos.makedirs('compressed', exist_ok=True)\n\n# do a full update on all pairs\nn_count = len(all_pairs)\nfor n, pair in enumerate(all_pairs, 1):\n    base, quote = pair\n    new_lines = all_candles_to_csv(base=base, quote=quote)\n    if new_lines > 0:\n        print(f'{datetime.now()} {n}/{n_count} Wrote {new_lines} new lines to file for {base}-{quote}')\n    else:\n        print(f'{datetime.now()} {n}/{n_count} Already up to date with {base}-{quote}')","metadata":{"execution":{"iopub.status.busy":"2021-11-06T16:55:14.806901Z","iopub.execute_input":"2021-11-06T16:55:14.807161Z","iopub.status.idle":"2021-11-06T17:10:41.447637Z","shell.execute_reply.started":"2021-11-06T16:55:14.807134Z","shell.execute_reply":"2021-11-06T17:10:41.446422Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# clean the data folder and upload a new version of the dataset to kaggle\ntry:\n    os.remove('compressed/.DS_Store')\nexcept FileNotFoundError:\n    pass\nwrite_metadata(n_count)\n\nyesterday = date.today() - timedelta(days=1)\nsubprocess.run(['kaggle', 'datasets', 'version', '-p', 'compressed/', '-m', f'full update of all {n_count} pairs up to {str(yesterday)}'])\nos.remove('compressed/dataset-metadata.json')\n","metadata":{},"execution_count":null,"outputs":[]}]}