{"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":"# Idea #\nFrom the way target is calculated, it is clear that target values for individual assets are not independent:\n$$\\text{Target}^a(t) = R^a(t) - \\beta^a M(t)$$\n$$\\beta^a = \\frac{\\langle M \\cdot R^a \\rangle}{\\langle M^2 \\rangle}$$\n$$M(t) = \\frac{\\sum_a w^a R^a(t)}{\\sum_a w^a}$$\nInstead we need to predict is a vector of targets $(Target_1, Target_2, ... Target_{14})$ for each timestamp. \n\nBuilding a model to predict the full vector $(Target_1, Target_2, ... Target_{14})$ can be hard, so why not start with a simplier setup: consider there are only two cryptocurrencies in the universe: Bitcoin and Etherium. We can gain some insights and try models in this 2-asset setup. If a solution does not work in the 2-asset setup, it will probably not work in the 14-asset setup. It is important to notice that in this new 2-asset system the old target values no longer apply and we need to construct the targets ourselves by applying the equations above.","metadata":{}},{"cell_type":"markdown","source":"# Converting data from one asset per line to a vector of assets per line #","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom matplotlib import pyplot as plt","metadata":{"execution":{"iopub.status.busy":"2021-11-20T13:17:24.571475Z","iopub.execute_input":"2021-11-20T13:17:24.571965Z","iopub.status.idle":"2021-11-20T13:17:24.597822Z","shell.execute_reply.started":"2021-11-20T13:17:24.571880Z","shell.execute_reply":"2021-11-20T13:17:24.596835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.read_csv(\"../input/g-research-crypto-forecasting/train.csv\")\n\ndf_BTC = df[df[\"Asset_ID\"] == 1]  # Bitcoin\ndf_BTC = df_BTC.drop(columns=[\"Asset_ID\", \"Target\"])  # asset id and old target are no longer useful\ndf_BTC.set_index(\"timestamp\", inplace=True)\ndf_BTC = df_BTC.add_suffix('_BTC')\n\ndf_ETH = df[df[\"Asset_ID\"] == 6]  # Etherium\ndf_ETH = df_ETH.drop(columns=[\"Asset_ID\", \"Target\"])   # asset id and old target are no longer useful\ndf_ETH.set_index(\"timestamp\", inplace=True)\ndf_ETH = df_ETH.add_suffix('_ETH')\n\ndf_BTC_ETH = pd.concat([df_BTC, df_ETH], axis=1)\n\n# at some timestamps only one asset is traded - fill the second with previous values\ndf_BTC_ETH.fillna(method='ffill', inplace=True)\n\ndf_BTC_ETH","metadata":{"execution":{"iopub.status.busy":"2021-11-20T13:17:30.578868Z","iopub.execute_input":"2021-11-20T13:17:30.579158Z","iopub.status.idle":"2021-11-20T13:18:33.419385Z","shell.execute_reply.started":"2021-11-20T13:17:30.579129Z","shell.execute_reply":"2021-11-20T13:18:33.418436Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature engineering #","metadata":{}},{"cell_type":"markdown","source":"Time for some feature engineering. The variables Open, High, Low, Close, VWAP are all strongly correlated to each other so it makes sence to only keep one of them (I keep Close). Moreover, it makes sence to store the price in the log format as the returns are simply differences between logprices:\n$$R^a(t) = log (P^a(t+16)\\ /\\ P^a(t+1)) = log (P^a(t+16) - log (P^a(t+1)$$\n\nThe other prices are only useful as we can infer price differences:\n- The Open-Close difference is probably not very helpful since we it only gives a short term behaviour (1 minute - much less than the needed 15-minute intervals)\n- The Low-High difference gives some measure of how volatile the asset is, so I introduce this as a new feature\n\nAs far as time is concerned, it is possible that an hour of day matters (e.g. people who live in a different timezones trade assets differently). I introduce this new feature as a `frac_of_day`. Similarly, I also extract `frac_of_week`, `frac_of_month`, and `frac_of_year`\n","metadata":{}},{"cell_type":"code","source":"# engineer logprice and volatility\ndf_features = df_BTC_ETH.copy()\nfor suffix in [\"_BTC\", \"_ETH\"]:\n    df_features[\"logprice\" + suffix] = np.log(df_features[\"Close\" + suffix])    \n    df_features[\"Volatility\" + suffix] = np.log(df_features[\"High\" + suffix]) - np.log(df_features[\"Low\" + suffix])\n    df_features = df_features.drop(columns=[\"Close\"+suffix, \"High\"+suffix, \"Low\"+suffix, \"Open\"+suffix, \"VWAP\"+suffix])\n    \n    \n    \n# engineer meaningful features out of timestamp\nfrom datetime import datetime as dt\nimport time\n\ndef get_time_fractions(date):\n        \n    def s(date): # returns seconds since epoch\n        return time.mktime(date.timetuple())\n\n    year = date.year\n    month = date.month\n    day = date.day\n    dayofweek = date.dayofweek\n    \n    startOfThisDay = dt(year=year, month=month, day=day, hour=0, minute=0, second=0)\n    n_sec_in_day = 86400\n    fraction_of_day = (s(date) - s(startOfThisDay)) / n_sec_in_day\n    \n    fraction_of_week = (dayofweek + fraction_of_day) / 7\n    \n    startOfThisMonth = dt(year=year, month=month, day=1)\n    startOfNextMonth = dt(year=year, month=month+1, day=1) if month < 12 else dt(year=year+1, month=1, day=1)\n    fraction_of_month = (s(date) - s(startOfThisMonth)) / (s(startOfNextMonth) - s(startOfThisMonth))\n    \n    startOfThisYear = dt(year=year, month=1, day=1)\n    startOfNextYear = dt(year=year+1, month=1, day=1)\n    fraction_of_year = (s(date) - s(startOfThisYear)) / (s(startOfNextYear) - s(startOfThisYear))   \n\n    return fraction_of_day, fraction_of_week, fraction_of_month, fraction_of_year\n\ndatetimes = pd.Series(df_features.index).astype('datetime64[s]')\ndf_features['frac_of_day'], df_features['frac_of_week'], df_features['frac_of_month'], df_features['frac_of_year'] = zip(*datetimes.map(get_time_fractions))\n\ndf_features","metadata":{"execution":{"iopub.status.busy":"2021-11-15T17:59:43.749185Z","iopub.execute_input":"2021-11-15T17:59:43.750338Z","iopub.status.idle":"2021-11-15T18:02:17.635204Z","shell.execute_reply.started":"2021-11-15T17:59:43.750287Z","shell.execute_reply":"2021-11-15T18:02:17.634369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Constructing targets #\nAs mentioned above, the targets need to be recalculated for the 2-asset system as follows:\n$$\\text{Target}^a(t) = R^a(t) - \\beta^a M(t)$$\n$$\\beta^a = \\frac{\\langle M \\cdot R^a \\rangle}{\\langle M^2 \\rangle}$$\n$$M(t) = \\frac{\\sum_a w^a R^a(t)}{\\sum_a w^a}$$\n\nWe probably need to add $\\beta^a$ in our features since a model has no way of learning how it's calculated unless we use a very long time series. $\\beta^a$ are averaged over a long time period (3750 minutes) and should remain constant over the 15 min interval, so it makes sence to just supply the last calculated $\\beta^a$ to the model.\n\nThe final dataset for model training `df_features_targets` contains the vectorized target `(Target_BTC, Target_ETH)` as the last two columns.","metadata":{}},{"cell_type":"code","source":"# calculate 2-asset targets (different from 14-asset targets!)\n\n    \ndf_logprices = df_features[[\"logprice_BTC\", \"logprice_ETH\"]]\n\ndf_returns = df_logprices.shift(-16) - df_logprices.shift(-1)\nfor suffix in [\"_BTC\", \"_ETH\"]:\n    df_returns.rename(columns={\"logprice\"+suffix : \"R\"+suffix}, inplace = True)\n\nasset_details = pd.read_csv(\"../input/g-research-crypto-forecasting/asset_details.csv\")\nasset_details = asset_details[(asset_details[\"Asset_ID\"] == 1) | (asset_details[\"Asset_ID\"] == 6)]\nasset_details = asset_details.sort_values(by=[\"Asset_ID\"])\nweights = asset_details[\"Weight\"].to_numpy()\nweights = weights.reshape(len(weights),1)\n\nR = df_returns.to_numpy()\nweights_sum = np.sum(weights)\nM = np.dot(R, weights) / weights_sum\ndf_M = pd.DataFrame(data=M, index=df_returns.index, columns=[\"M\"])\n\ndf_R_M = df_returns.copy()\nfor col in df_R_M.columns:\n    df_R_M[col] = df_R_M[col] * df_M[\"M\"]\nfor suffix in [\"_BTC\", \"_ETH\"]:\n    df_R_M.rename(columns={\"R\"+suffix : \"R_M\"+suffix}, inplace = True)\ndf_R_M_rolling = df_R_M.rolling(window=3750).mean()\n\ndf_M2 = df_M ** 2\ndf_M2.rename(columns={\"M\" : \"M2\"}, inplace = True)\ndf_M2_rolling = df_M2.rolling(window=3750).mean()\n\ndf_betas = df_R_M_rolling.copy()       \nfor col in df_betas.columns:\n    df_betas[col] = df_betas[col] / df_M2_rolling[\"M2\"]\nfor suffix in [\"_BTC\", \"_ETH\"]:\n    df_betas.rename(columns={\"R_M\"+suffix : \"beta\"+suffix}, inplace = True)\n\ndf_targets = df_returns.copy()\nfor suffix in [\"_BTC\", \"_ETH\"]:\n    df_targets[\"R\"+suffix] -= df_betas[\"beta\"+suffix] * df_M[\"M\"]\n    df_targets.rename(columns={\"R\"+suffix : \"Target\"+suffix}, inplace = True)\n    \n\ndf_features_targets = pd.concat([df_features, df_betas, df_targets], axis=1)\ndf_features_targets = df_features_targets.iloc[3750:-16]  # get rid of the Nan rows\n\ndf_features_targets","metadata":{"execution":{"iopub.status.busy":"2021-11-15T18:04:03.286268Z","iopub.execute_input":"2021-11-15T18:04:03.286994Z","iopub.status.idle":"2021-11-15T18:04:03.800439Z","shell.execute_reply.started":"2021-11-15T18:04:03.286959Z","shell.execute_reply":"2021-11-15T18:04:03.799839Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Looking at the data #","metadata":{}},{"cell_type":"code","source":"# correlation heatmap\nfrom bokeh.io import output_notebook, show\nfrom bokeh.models import (\n    BasicTicker,\n    ColorBar,\n    ColumnDataSource,\n    LinearColorMapper,\n    PrintfTickFormatter,\n)\nfrom bokeh.plotting import figure\nfrom bokeh.transform import transform\n\noutput_notebook()\n\ndf_to_viz = df_features_targets\n\nxcorr = abs(df_to_viz.corr())\nxcorr.index.name = \"Feature1\"\nxcorr.columns.name = \"Feature2\"\n\ndf = pd.DataFrame(xcorr.stack(), columns=[\"Corr\"]).reset_index()\n\nsource = ColumnDataSource(df)\n\ncolors = [\n    \"#75968f\",\n    \"#a5bab7\",\n    \"#c9d9d3\",\n    \"#e2e2e2\",\n    \"#dfccce\",\n    \"#ddb7b1\",\n    \"#cc7878\",\n    \"#933b41\",\n    \"#550b1d\",\n]\n\nmapper = LinearColorMapper(palette=colors, low=df.Corr.min(), high=df.Corr.max())\n\nf1 = figure(\n    plot_width=800,\n    plot_height=800,\n    title=\"Correlation Heat Map\",\n    x_range=list(sorted(xcorr.index)),\n    y_range=list(reversed(sorted(xcorr.columns))),\n    toolbar_location=None,\n    tools=\"hover\",\n    x_axis_location=\"above\",\n)\n\nf1.rect(\n    x=\"Feature2\",\n    y=\"Feature1\",\n    width=1,\n    height=1,\n    source=source,\n    line_color=None,\n    fill_color=transform(\"Corr\", mapper),\n)\n\ncolor_bar = ColorBar(\n    color_mapper=mapper,\n    location=(0, 0),\n    ticker=BasicTicker(desired_num_ticks=len(colors)),\n    formatter=PrintfTickFormatter(format=\"%d%%\"),\n)\nf1.add_layout(color_bar, \"right\")\n\nf1.hover.tooltips = [\n    (\"Feature1\", \"@{Feature1}\"),\n    (\"Feature2\", \"@{Feature2}\"),\n    (\"Corr\", \"@{Corr}{1.1111}\"),\n]\n\nf1.axis.axis_line_color = None\nf1.axis.major_tick_line_color = None\nf1.axis.major_label_text_font_size = \"12px\"\nf1.axis.major_label_standoff = 2\nf1.xaxis.major_label_orientation = 1.0\n\nshow(f1)","metadata":{"execution":{"iopub.status.busy":"2021-11-15T19:11:29.980017Z","iopub.execute_input":"2021-11-15T19:11:29.980293Z","iopub.status.idle":"2021-11-15T19:11:31.593483Z","shell.execute_reply.started":"2021-11-15T19:11:29.980266Z","shell.execute_reply":"2021-11-15T19:11:31.592961Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# looking at returns\ndf_to_plot = df_returns.iloc[::100]\ndf_to_plot[(np.abs(df_to_plot[\"R_BTC\"]) < 0.01) & (np.abs(df_to_plot[\"R_ETH\"]) < 0.01)].plot.hist(bins=50, alpha=0.5)\nplt.xlabel(\"Return\")\nplt.title(\"Return frequency\")\nplt.show()\n\nplt.plot(df_to_plot[\"R_BTC\"], df_to_plot[\"R_ETH\"], '.b')\nplt.xlim([-0.01, 0.01])\nplt.ylim([-0.01, 0.01])\nplt.xlabel(\"Return_BTC\")\nplt.ylabel(\"Return_ETH\")\nplt.grid()\nplt.title(\"ETH return vs BTC return\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-15T18:52:49.058786Z","iopub.execute_input":"2021-11-15T18:52:49.059266Z","iopub.status.idle":"2021-11-15T18:52:49.690932Z","shell.execute_reply.started":"2021-11-15T18:52:49.059221Z","shell.execute_reply":"2021-11-15T18:52:49.688346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can notice a few things:\n- for both assets the return distribution resembles normal distrubution, but is more pointy (low returns are disproportionatly frequent)\n- for BTC, this pointy effect is more pronounced than for ETH\n- we see significant correlation between the returns of the two assets. Mostly, when BTC is up, ETH is up.","metadata":{}},{"cell_type":"code","source":"# looking at betas\ny1 = df_features_targets[\"beta_BTC\"].iloc[::15000]\ny2 = df_features_targets[\"beta_ETH\"].iloc[::15000]\nplt.plot(y1, '-b', label=\"beta_BTC\")\nplt.plot(y2, '-r', label=\"beta_ETH\")\nplt.xlabel(\"time, s\")\nplt.ylabel(\"betas\")\nplt.legend()\nplt.grid()\nplt.title(\"beta vs time\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-15T18:13:19.50083Z","iopub.execute_input":"2021-11-15T18:13:19.501584Z","iopub.status.idle":"2021-11-15T18:13:19.722672Z","shell.execute_reply.started":"2021-11-15T18:13:19.501539Z","shell.execute_reply":"2021-11-15T18:13:19.721748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The two betas always add up to 2, and vary considerably vs time","metadata":{}},{"cell_type":"code","source":"# looking at targets\ndf_to_plot = df_features_targets[[\"Target_BTC\", \"Target_ETH\"]].iloc[::100]\ndf_to_plot[(np.abs(df_to_plot[\"Target_BTC\"]) < 0.01) & (np.abs(df_to_plot[\"Target_ETH\"]) < 0.01)].plot.hist(bins=50, alpha=0.5)\nplt.xlabel(\"Target\")\nplt.title(\"Target frequency\")\nplt.show()\n\n\nplt.plot(df_to_plot[\"Target_BTC\"], df_to_plot[\"Target_ETH\"], '.b')\nplt.xlim([-0.01, 0.01])\nplt.ylim([-0.01, 0.01])\nplt.xlabel(\"Target_BTC\")\nplt.ylabel(\"Target_ETH\")\nplt.grid()\nplt.title(\"ETH target vs BTC target\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-15T18:54:19.766004Z","iopub.execute_input":"2021-11-15T18:54:19.766302Z","iopub.status.idle":"2021-11-15T18:54:20.431143Z","shell.execute_reply.started":"2021-11-15T18:54:19.766272Z","shell.execute_reply":"2021-11-15T18:54:20.430479Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is interesting that residualization transforms the spread-out 2D distribution of returns to a 1D distribution of targets (all the target points all line up on a straight line). Similarly, for 14 assets I expect the distribution of targets to be 13-dimensional. If each asset was treated separately, this feature of the target distribution would have been missed.","metadata":{}},{"cell_type":"code","source":"df_features_targets[[\"Target_BTC\", \"Target_ETH\"]].corr()","metadata":{"execution":{"iopub.status.busy":"2021-11-15T19:01:06.157442Z","iopub.execute_input":"2021-11-15T19:01:06.15804Z","iopub.status.idle":"2021-11-15T19:01:06.249935Z","shell.execute_reply.started":"2021-11-15T19:01:06.158005Z","shell.execute_reply":"2021-11-15T19:01:06.249005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The BTC and ETH targets are strictly anti-correlated. This means that for model training we can just try to match Target_BTC. If we can get any correlation, the other asset will be correlated automatically.","metadata":{}},{"cell_type":"markdown","source":"# Next steps #\nWe need to train a model and try to get some meaningful correlation. So far I have not had any success with this. It is possible that the current data is insufficient and we need to add dat from previous timestamps in the data set (time series problem).\n\nAny suggestions are welcome!","metadata":{}}]}