Skip to content

Instantly share code, notes, and snippets.

@arms22

arms22/Coincheck年間取引損益計算（標準フォーマット用）.ipynb

Created January 14, 2022 02:41
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save arms22/659b06af72757f94cf432dce43c22a26 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save arms22/659b06af72757f94cf432dce43c22a26 to your computer and use it in GitHub Desktop.
Download ZIP
Coincheck年間取引損益計算（標準フォーマット用）
Display the source blob
Display the rendered blob
Raw
Coincheck年間取引損益計算（標準フォーマット用）.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {
"scrolled": false
},
"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>取引種別</th>\n",
" <th>取引形態</th>\n",
" <th>通貨ペア</th>\n",
" <th>増加通貨名</th>\n",
" <th>増加数量</th>\n",
" <th>減少通貨名</th>\n",
" <th>減少数量</th>\n",
" <th>約定価格/数量</th>\n",
" <th>単価</th>\n",
" <th>手数料通貨</th>\n",
" <th>手数料数量</th>\n",
" <th>送付元アドレス</th>\n",
" <th>送付先アドレス</th>\n",
" <th>登録番号</th>\n",
" <th>社名</th>\n",
" <th>備考</th>\n",
" </tr>\n",
" <tr>\n",
" <th>取引日時</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2021-03-09 12:05:11</th>\n",
" <td>入金</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>JPY</td>\n",
" <td>158000.00000</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-03-09 14:05:10</th>\n",
" <td>購入</td>\n",
" <td>媒介</td>\n",
" <td>BTC/JPY</td>\n",
" <td>BTC</td>\n",
" <td>0.01600</td>\n",
" <td>JPY</td>\n",
" <td>93755.16800</td>\n",
" <td>93755.16800</td>\n",
" <td>5859698.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-03-09 14:05:32</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>BTC/JPY</td>\n",
" <td>JPY</td>\n",
" <td>9205.32919</td>\n",
" <td>BTC</td>\n",
" <td>0.00157</td>\n",
" <td>9205.32919</td>\n",
" <td>5863267.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-03-09 14:05:51</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>BTC/JPY</td>\n",
" <td>JPY</td>\n",
" <td>84581.25741</td>\n",
" <td>BTC</td>\n",
" <td>0.01443</td>\n",
" <td>84581.25741</td>\n",
" <td>5861487.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-03-09 14:06:05</th>\n",
" <td>購入</td>\n",
" <td>媒介</td>\n",
" <td>BTC/JPY</td>\n",
" <td>BTC</td>\n",
" <td>0.01600</td>\n",
" <td>JPY</td>\n",
" <td>93798.41600</td>\n",
" <td>93798.41600</td>\n",
" <td>5862401.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 取引種別 取引形態 通貨ペア 増加通貨名 増加数量 減少通貨名 減少数量 \\\n",
"取引日時 \n",
"2021-03-09 12:05:11 入金 NaN NaN JPY 158000.00000 NaN NaN \n",
"2021-03-09 14:05:10 購入 媒介 BTC/JPY BTC 0.01600 JPY 93755.16800 \n",
"2021-03-09 14:05:32 売却 媒介 BTC/JPY JPY 9205.32919 BTC 0.00157 \n",
"2021-03-09 14:05:51 売却 媒介 BTC/JPY JPY 84581.25741 BTC 0.01443 \n",
"2021-03-09 14:06:05 購入 媒介 BTC/JPY BTC 0.01600 JPY 93798.41600 \n",
"\n",
" 約定価格/数量 単価 手数料通貨 手数料数量 送付元アドレス 送付先アドレス \\\n",
"取引日時 \n",
"2021-03-09 12:05:11 NaN NaN NaN NaN NaN NaN \n",
"2021-03-09 14:05:10 93755.16800 5859698.0 NaN NaN NaN NaN \n",
"2021-03-09 14:05:32 9205.32919 5863267.0 NaN NaN NaN NaN \n",
"2021-03-09 14:05:51 84581.25741 5861487.0 NaN NaN NaN NaN \n",
"2021-03-09 14:06:05 93798.41600 5862401.0 NaN NaN NaN NaN \n",
"\n",
" 登録番号 社名 備考 \n",
"取引日時 \n",
"2021-03-09 12:05:11 関東財務局長 第00014号 コインチェック株式会社 NaN \n",
"2021-03-09 14:05:10 関東財務局長 第00014号 コインチェック株式会社 NaN \n",
"2021-03-09 14:05:32 関東財務局長 第00014号 コインチェック株式会社 NaN \n",
"2021-03-09 14:05:51 関東財務局長 第00014号 コインチェック株式会社 NaN \n",
"2021-03-09 14:06:05 関東財務局長 第00014号 コインチェック株式会社 NaN "
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#損益データ読み込み\n",
"df = pd.concat([\n",
" pd.read_csv('standard_xxxxxxx_2021_1.csv', index_col=\"取引日時\", parse_dates=True, dtype={'手数料通貨':str}),\n",
" pd.read_csv('standard_xxxxxxx_2021_2.csv', index_col=\"取引日時\", parse_dates=True, dtype={'手数料通貨':str}),\n",
" pd.read_csv('standard_xxxxxxx_2021_3.csv', index_col=\"取引日時\", parse_dates=True, dtype={'手数料通貨':str}),\n",
"])\n",
"df.head(5)"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {},
"outputs": [],
"source": [
"# 売買方向\n",
"df['side'] = 0\n",
"df.loc[df['取引種別']=='購入','side'] = +1\n",
"df.loc[df['取引種別']=='売却','side'] = -1"
]
},
{
"cell_type": "code",
"execution_count": 34,
"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>取引種別</th>\n",
" <th>取引形態</th>\n",
" <th>通貨ペア</th>\n",
" <th>増加通貨名</th>\n",
" <th>増加数量</th>\n",
" <th>減少通貨名</th>\n",
" <th>減少数量</th>\n",
" <th>約定価格/数量</th>\n",
" <th>単価</th>\n",
" <th>手数料通貨</th>\n",
" <th>手数料数量</th>\n",
" <th>送付元アドレス</th>\n",
" <th>送付先アドレス</th>\n",
" <th>登録番号</th>\n",
" <th>社名</th>\n",
" <th>備考</th>\n",
" <th>side</th>\n",
" </tr>\n",
" <tr>\n",
" <th>取引日時</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2021-12-30 10:13:37</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>MONA/JPY</td>\n",
" <td>JPY</td>\n",
" <td>19040.158400</td>\n",
" <td>MONA</td>\n",
" <td>139.9600</td>\n",
" <td>19040.158400</td>\n",
" <td>136.04</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" <td>-1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-12-30 10:17:45</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>MONA/JPY</td>\n",
" <td>JPY</td>\n",
" <td>662.500000</td>\n",
" <td>MONA</td>\n",
" <td>5.0000</td>\n",
" <td>662.500000</td>\n",
" <td>132.50</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" <td>-1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-12-30 10:19:01</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>MONA/JPY</td>\n",
" <td>JPY</td>\n",
" <td>95151.518000</td>\n",
" <td>MONA</td>\n",
" <td>718.4500</td>\n",
" <td>95151.518000</td>\n",
" <td>132.44</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" <td>-1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-12-30 10:19:03</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>MONA/JPY</td>\n",
" <td>JPY</td>\n",
" <td>6622.000000</td>\n",
" <td>MONA</td>\n",
" <td>50.0000</td>\n",
" <td>6622.000000</td>\n",
" <td>132.44</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" <td>-1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021-12-30 10:20:15</th>\n",
" <td>売却</td>\n",
" <td>媒介</td>\n",
" <td>MONA/JPY</td>\n",
" <td>JPY</td>\n",
" <td>5632.501028</td>\n",
" <td>MONA</td>\n",
" <td>42.5287</td>\n",
" <td>5632.501028</td>\n",
" <td>132.44</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" <td>-1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 取引種別 取引形態 通貨ペア 増加通貨名 増加数量 減少通貨名 減少数量 \\\n",
"取引日時 \n",
"2021-12-30 10:13:37 売却 媒介 MONA/JPY JPY 19040.158400 MONA 139.9600 \n",
"2021-12-30 10:17:45 売却 媒介 MONA/JPY JPY 662.500000 MONA 5.0000 \n",
"2021-12-30 10:19:01 売却 媒介 MONA/JPY JPY 95151.518000 MONA 718.4500 \n",
"2021-12-30 10:19:03 売却 媒介 MONA/JPY JPY 6622.000000 MONA 50.0000 \n",
"2021-12-30 10:20:15 売却 媒介 MONA/JPY JPY 5632.501028 MONA 42.5287 \n",
"\n",
" 約定価格/数量 単価 手数料通貨 手数料数量 送付元アドレス 送付先アドレス \\\n",
"取引日時 \n",
"2021-12-30 10:13:37 19040.158400 136.04 NaN NaN NaN NaN \n",
"2021-12-30 10:17:45 662.500000 132.50 NaN NaN NaN NaN \n",
"2021-12-30 10:19:01 95151.518000 132.44 NaN NaN NaN NaN \n",
"2021-12-30 10:19:03 6622.000000 132.44 NaN NaN NaN NaN \n",
"2021-12-30 10:20:15 5632.501028 132.44 NaN NaN NaN NaN \n",
"\n",
" 登録番号 社名 備考 side \n",
"取引日時 \n",
"2021-12-30 10:13:37 関東財務局長 第00014号 コインチェック株式会社 NaN -1 \n",
"2021-12-30 10:17:45 関東財務局長 第00014号 コインチェック株式会社 NaN -1 \n",
"2021-12-30 10:19:01 関東財務局長 第00014号 コインチェック株式会社 NaN -1 \n",
"2021-12-30 10:19:03 関東財務局長 第00014号 コインチェック株式会社 NaN -1 \n",
"2021-12-30 10:20:15 関東財務局長 第00014号 コインチェック株式会社 NaN -1 "
]
},
"execution_count": 34,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 取引履歴\n",
"executions = df[df['side']!=0]\n",
"executions.tail(5)"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"scrolled": true
},
"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>取引種別</th>\n",
" <th>取引形態</th>\n",
" <th>通貨ペア</th>\n",
" <th>増加通貨名</th>\n",
" <th>増加数量</th>\n",
" <th>減少通貨名</th>\n",
" <th>減少数量</th>\n",
" <th>約定価格/数量</th>\n",
" <th>単価</th>\n",
" <th>手数料通貨</th>\n",
" <th>手数料数量</th>\n",
" <th>送付元アドレス</th>\n",
" <th>送付先アドレス</th>\n",
" <th>登録番号</th>\n",
" <th>社名</th>\n",
" <th>備考</th>\n",
" <th>side</th>\n",
" </tr>\n",
" <tr>\n",
" <th>取引日時</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2021-03-09 12:05:11</th>\n",
" <td>入金</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>JPY</td>\n",
" <td>158000.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>関東財務局長 第00014号</td>\n",
" <td>コインチェック株式会社</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 取引種別 取引形態 通貨ペア 増加通貨名 増加数量 減少通貨名 減少数量 約定価格/数量 単価 \\\n",
"取引日時 \n",
"2021-03-09 12:05:11 入金 NaN NaN JPY 158000.0 NaN NaN NaN NaN \n",
"\n",
" 手数料通貨 手数料数量 送付元アドレス 送付先アドレス 登録番号 \\\n",
"取引日時 \n",
"2021-03-09 12:05:11 NaN NaN NaN NaN 関東財務局長 第00014号 \n",
"\n",
" 社名 備考 side \n",
"取引日時 \n",
"2021-03-09 12:05:11 コインチェック株式会社 NaN 0 "
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 入出金履歴\n",
"deposits_and_withdrawals = df[df['side']==0]\n",
"deposits_and_withdrawals"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"購入 10663423570.663076 売却 10663800264.303284 売却-購入 376693.64020729065\n"
]
}
],
"source": [
"# 損益計算\n",
"buy_cost = executions[executions['side']==+1]['約定価格/数量'].sum()\n",
"sell_cost = executions[executions['side']==-1]['約定価格/数量'].sum()\n",
"print('購入', buy_cost, '売却', sell_cost, '売却-購入', sell_cost-buy_cost)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"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.7.10"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment