profit_history.py

# -*- coding: utf-8 -*-

# pip3 install xlrd python-dateutil

import xlrd
from datetime import datetime
from dateutil.parser import parse as date_parse
from collections import defaultdict

# 入力ファイル
INPUT = "CollateralHistory_20180908.xls"
# 期間
DATE_START = datetime(2018, 9, 1, 0, 0) 
DATE_END = datetime(2018, 9, 30, 23, 59)
# 対象
PAIR_FILTER = ['FX_BTC_JPY']

book = xlrd.open_workbook(INPUT, on_demand=True)
profits = defaultdict(lambda: defaultdict(lambda: 0))

for sheet in book.sheets():
    header =sheet.row(0)
    assert(header[0].value == '日時' and
           header[1].value == '操作' and
           header[2].value == 'ペア' and
           header[4].value == '変動量')
    for i in range(1, sheet.nrows):
        cols = sheet.row(i)
        pair = cols[2].value
        if PAIR_FILTER:
            if pair not in PAIR_FILTER:
                continue
        t = date_parse(cols[0].value)
        day = t.strftime('%m-%d')
        if not (DATE_START <= t and t <= DATE_END):
            continue
        if cols[1].value == '決済':
            profits[day][pair] += cols[4].value
        elif cols[1].value == 'SFD':
            profits[day]['SFD'] += cols[4].value

pairs = sorted({k for v in profits.values() for k in v.keys()})
total = defaultdict(lambda: 0)
fmt = "%-16s" + "%16d" * len(pairs)
print(("%-16s" * (len(pairs) + 1)) % tuple(['DATE'] + list(pairs)))
for day in sorted(profits.keys()):
    values = []
    for pair in pairs:
        v = profits[day].get(pair, 0)
        total[pair] += v
        values.append(v)
    print(fmt % tuple([day] + values))
print(fmt % tuple(['TOTAL'] + [total[pair] for pair in pairs]))