You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

381 lines
11KB

  1. {
  2. "cells": [
  3. {
  4. "cell_type": "code",
  5. "execution_count": 1,
  6. "metadata": {},
  7. "outputs": [
  8. {
  9. "data": {
  10. "text/html": [
  11. "<div>\n",
  12. "<style scoped>\n",
  13. " .dataframe tbody tr th:only-of-type {\n",
  14. " vertical-align: middle;\n",
  15. " }\n",
  16. "\n",
  17. " .dataframe tbody tr th {\n",
  18. " vertical-align: top;\n",
  19. " }\n",
  20. "\n",
  21. " .dataframe thead th {\n",
  22. " text-align: right;\n",
  23. " }\n",
  24. "</style>\n",
  25. "<table border=\"1\" class=\"dataframe\">\n",
  26. " <thead>\n",
  27. " <tr style=\"text-align: right;\">\n",
  28. " <th></th>\n",
  29. " <th>time</th>\n",
  30. " <th>amount</th>\n",
  31. " <th>exch</th>\n",
  32. " <th>price</th>\n",
  33. " <th>server_time</th>\n",
  34. " <th>side</th>\n",
  35. " <th>ticker</th>\n",
  36. " </tr>\n",
  37. " </thead>\n",
  38. " <tbody>\n",
  39. " <tr>\n",
  40. " <td>0</td>\n",
  41. " <td>1531094401700852527</td>\n",
  42. " <td>0.0801</td>\n",
  43. " <td>bits</td>\n",
  44. " <td>6706.6001</td>\n",
  45. " <td>0</td>\n",
  46. " <td>na</td>\n",
  47. " <td>btc_usd</td>\n",
  48. " </tr>\n",
  49. " <tr>\n",
  50. " <td>1</td>\n",
  51. " <td>1531094401780298519</td>\n",
  52. " <td>0.0284</td>\n",
  53. " <td>bits</td>\n",
  54. " <td>6706.6099</td>\n",
  55. " <td>0</td>\n",
  56. " <td>na</td>\n",
  57. " <td>btc_usd</td>\n",
  58. " </tr>\n",
  59. " <tr>\n",
  60. " <td>2</td>\n",
  61. " <td>1531094402305708472</td>\n",
  62. " <td>0.0050</td>\n",
  63. " <td>btfx</td>\n",
  64. " <td>6707.0000</td>\n",
  65. " <td>0</td>\n",
  66. " <td>na</td>\n",
  67. " <td>btc_usd</td>\n",
  68. " </tr>\n",
  69. " <tr>\n",
  70. " <td>3</td>\n",
  71. " <td>1531094403455657797</td>\n",
  72. " <td>0.0050</td>\n",
  73. " <td>btfx</td>\n",
  74. " <td>6706.7002</td>\n",
  75. " <td>0</td>\n",
  76. " <td>na</td>\n",
  77. " <td>btc_usd</td>\n",
  78. " </tr>\n",
  79. " <tr>\n",
  80. " <td>4</td>\n",
  81. " <td>1531094403592663872</td>\n",
  82. " <td>0.0658</td>\n",
  83. " <td>btfx</td>\n",
  84. " <td>6705.8999</td>\n",
  85. " <td>0</td>\n",
  86. " <td>na</td>\n",
  87. " <td>btc_usd</td>\n",
  88. " </tr>\n",
  89. " </tbody>\n",
  90. "</table>\n",
  91. "</div>"
  92. ],
  93. "text/plain": [
  94. " time amount exch price server_time side ticker\n",
  95. "0 1531094401700852527 0.0801 bits 6706.6001 0 na btc_usd\n",
  96. "1 1531094401780298519 0.0284 bits 6706.6099 0 na btc_usd\n",
  97. "2 1531094402305708472 0.0050 btfx 6707.0000 0 na btc_usd\n",
  98. "3 1531094403455657797 0.0050 btfx 6706.7002 0 na btc_usd\n",
  99. "4 1531094403592663872 0.0658 btfx 6705.8999 0 na btc_usd"
  100. ]
  101. },
  102. "execution_count": 1,
  103. "metadata": {},
  104. "output_type": "execute_result"
  105. }
  106. ],
  107. "source": [
  108. "df = pd.read_csv('/xfs/trades.csv', nrows=1024 * 1024)\n",
  109. "df.head()"
  110. ]
  111. },
  112. {
  113. "cell_type": "code",
  114. "execution_count": 2,
  115. "metadata": {},
  116. "outputs": [
  117. {
  118. "data": {
  119. "text/html": [
  120. "<div>\n",
  121. "<style scoped>\n",
  122. " .dataframe tbody tr th:only-of-type {\n",
  123. " vertical-align: middle;\n",
  124. " }\n",
  125. "\n",
  126. " .dataframe tbody tr th {\n",
  127. " vertical-align: top;\n",
  128. " }\n",
  129. "\n",
  130. " .dataframe thead th {\n",
  131. " text-align: right;\n",
  132. " }\n",
  133. "</style>\n",
  134. "<table border=\"1\" class=\"dataframe\">\n",
  135. " <thead>\n",
  136. " <tr style=\"text-align: right;\">\n",
  137. " <th></th>\n",
  138. " <th>time</th>\n",
  139. " <th>amount</th>\n",
  140. " <th>exch</th>\n",
  141. " <th>price</th>\n",
  142. " <th>server_time</th>\n",
  143. " <th>side</th>\n",
  144. " <th>ticker</th>\n",
  145. " </tr>\n",
  146. " <tr>\n",
  147. " <th>time</th>\n",
  148. " <th></th>\n",
  149. " <th></th>\n",
  150. " <th></th>\n",
  151. " <th></th>\n",
  152. " <th></th>\n",
  153. " <th></th>\n",
  154. " <th></th>\n",
  155. " </tr>\n",
  156. " </thead>\n",
  157. " <tbody>\n",
  158. " <tr>\n",
  159. " <td>2018-07-09 00:00:01.700852527+00:00</td>\n",
  160. " <td>1531094401700852527</td>\n",
  161. " <td>0.0801</td>\n",
  162. " <td>bits</td>\n",
  163. " <td>6706.6001</td>\n",
  164. " <td>0</td>\n",
  165. " <td>na</td>\n",
  166. " <td>btc_usd</td>\n",
  167. " </tr>\n",
  168. " <tr>\n",
  169. " <td>2018-07-09 00:00:01.780298519+00:00</td>\n",
  170. " <td>1531094401780298519</td>\n",
  171. " <td>0.0284</td>\n",
  172. " <td>bits</td>\n",
  173. " <td>6706.6099</td>\n",
  174. " <td>0</td>\n",
  175. " <td>na</td>\n",
  176. " <td>btc_usd</td>\n",
  177. " </tr>\n",
  178. " <tr>\n",
  179. " <td>2018-07-09 00:00:02.305708472+00:00</td>\n",
  180. " <td>1531094402305708472</td>\n",
  181. " <td>0.0050</td>\n",
  182. " <td>btfx</td>\n",
  183. " <td>6707.0000</td>\n",
  184. " <td>0</td>\n",
  185. " <td>na</td>\n",
  186. " <td>btc_usd</td>\n",
  187. " </tr>\n",
  188. " <tr>\n",
  189. " <td>2018-07-09 00:00:03.455657797+00:00</td>\n",
  190. " <td>1531094403455657797</td>\n",
  191. " <td>0.0050</td>\n",
  192. " <td>btfx</td>\n",
  193. " <td>6706.7002</td>\n",
  194. " <td>0</td>\n",
  195. " <td>na</td>\n",
  196. " <td>btc_usd</td>\n",
  197. " </tr>\n",
  198. " <tr>\n",
  199. " <td>2018-07-09 00:00:03.592663872+00:00</td>\n",
  200. " <td>1531094403592663872</td>\n",
  201. " <td>0.0658</td>\n",
  202. " <td>btfx</td>\n",
  203. " <td>6705.8999</td>\n",
  204. " <td>0</td>\n",
  205. " <td>na</td>\n",
  206. " <td>btc_usd</td>\n",
  207. " </tr>\n",
  208. " </tbody>\n",
  209. "</table>\n",
  210. "</div>"
  211. ],
  212. "text/plain": [
  213. " time amount exch \\\n",
  214. "time \n",
  215. "2018-07-09 00:00:01.700852527+00:00 1531094401700852527 0.0801 bits \n",
  216. "2018-07-09 00:00:01.780298519+00:00 1531094401780298519 0.0284 bits \n",
  217. "2018-07-09 00:00:02.305708472+00:00 1531094402305708472 0.0050 btfx \n",
  218. "2018-07-09 00:00:03.455657797+00:00 1531094403455657797 0.0050 btfx \n",
  219. "2018-07-09 00:00:03.592663872+00:00 1531094403592663872 0.0658 btfx \n",
  220. "\n",
  221. " price server_time side ticker \n",
  222. "time \n",
  223. "2018-07-09 00:00:01.700852527+00:00 6706.6001 0 na btc_usd \n",
  224. "2018-07-09 00:00:01.780298519+00:00 6706.6099 0 na btc_usd \n",
  225. "2018-07-09 00:00:02.305708472+00:00 6707.0000 0 na btc_usd \n",
  226. "2018-07-09 00:00:03.455657797+00:00 6706.7002 0 na btc_usd \n",
  227. "2018-07-09 00:00:03.592663872+00:00 6705.8999 0 na btc_usd "
  228. ]
  229. },
  230. "execution_count": 2,
  231. "metadata": {},
  232. "output_type": "execute_result"
  233. }
  234. ],
  235. "source": [
  236. "df.index = pd.to_datetime(df['time'], utc=True)\n",
  237. "df.head()"
  238. ]
  239. },
  240. {
  241. "cell_type": "code",
  242. "execution_count": 3,
  243. "metadata": {},
  244. "outputs": [
  245. {
  246. "data": {
  247. "text/plain": [
  248. "(Timestamp('2018-07-09 00:00:01.700852527+0000', tz='UTC'),\n",
  249. " Timestamp('2018-07-11 19:27:34.453569864+0000', tz='UTC'),\n",
  250. " Timedelta('2 days 19:27:32.752717'))"
  251. ]
  252. },
  253. "execution_count": 3,
  254. "metadata": {},
  255. "output_type": "execute_result"
  256. }
  257. ],
  258. "source": [
  259. "df.index[0], df.index[-1], df.index[-1] - df.index[0]"
  260. ]
  261. },
  262. {
  263. "cell_type": "code",
  264. "execution_count": 4,
  265. "metadata": {},
  266. "outputs": [
  267. {
  268. "data": {
  269. "text/plain": [
  270. "1.000578250137079"
  271. ]
  272. },
  273. "execution_count": 4,
  274. "metadata": {},
  275. "output_type": "execute_result"
  276. }
  277. ],
  278. "source": [
  279. "start = \"2018-07-10T04:00:00Z\" # <- randomly selected start/end \n",
  280. "end = \"2018-07-10T05:00:00Z\" # important point is, 1h span\n",
  281. "\n",
  282. "target_hr = (df.index >= start) & (df.index < end)\n",
  283. "assert target_hr.sum() > 0\n",
  284. "\n",
  285. "of_btcusd = df['ticker'] == 'btc_usd'\n",
  286. "assert of_btcusd.sum() > 0\n",
  287. "\n",
  288. "of_bmex = df['exch'] == 'bmex'\n",
  289. "of_gdax = df['exch'] == 'gdax'\n",
  290. "\n",
  291. "assert of_bmex.sum() > 0\n",
  292. "assert of_gdax.sum() > 0\n",
  293. "\n",
  294. "bmex_hr = df.loc[target_hr & of_bmex & of_btcusd, ['price', 'amount']]\n",
  295. "gdax_hr = df.loc[target_hr & of_gdax & of_btcusd, ['price', 'amount']]\n",
  296. "\n",
  297. "bmex_size_wt_price = (\n",
  298. " (bmex_hr['price'] * bmex_hr['amount']).sum()\n",
  299. " / bmex_hr['amount'].sum()\n",
  300. ")\n",
  301. "\n",
  302. "gdax_size_wt_price = (\n",
  303. " (gdax_hr['price'] * gdax_hr['amount']).sum()\n",
  304. " / gdax_hr['amount'].sum()\n",
  305. ")\n",
  306. "\n",
  307. "ratio = bmex_size_wt_price / gdax_size_wt_price # <- final answer (for start,end hr at least)\n",
  308. "ratio"
  309. ]
  310. },
  311. {
  312. "cell_type": "code",
  313. "execution_count": 5,
  314. "metadata": {},
  315. "outputs": [
  316. {
  317. "data": {
  318. "text/plain": [
  319. "((2714, 2), (1305, 2))"
  320. ]
  321. },
  322. "execution_count": 5,
  323. "metadata": {},
  324. "output_type": "execute_result"
  325. }
  326. ],
  327. "source": [
  328. "bmex_hr.shape, gdax_hr.shape"
  329. ]
  330. },
  331. {
  332. "cell_type": "code",
  333. "execution_count": 6,
  334. "metadata": {},
  335. "outputs": [
  336. {
  337. "data": {
  338. "text/plain": [
  339. "(6642.42451802828, 6638.585754905495)"
  340. ]
  341. },
  342. "execution_count": 6,
  343. "metadata": {},
  344. "output_type": "execute_result"
  345. }
  346. ],
  347. "source": [
  348. "bmex_size_wt_price, gdax_size_wt_price"
  349. ]
  350. },
  351. {
  352. "cell_type": "code",
  353. "execution_count": null,
  354. "metadata": {},
  355. "outputs": [],
  356. "source": []
  357. }
  358. ],
  359. "metadata": {
  360. "kernelspec": {
  361. "display_name": "Python 3",
  362. "language": "python",
  363. "name": "python3"
  364. },
  365. "language_info": {
  366. "codemirror_mode": {
  367. "name": "ipython",
  368. "version": 3
  369. },
  370. "file_extension": ".py",
  371. "mimetype": "text/x-python",
  372. "name": "python",
  373. "nbconvert_exporter": "python",
  374. "pygments_lexer": "ipython3",
  375. "version": "3.7.5"
  376. }
  377. },
  378. "nbformat": 4,
  379. "nbformat_minor": 2
  380. }