量化交易之vnpy篇 - 报表自动化 - inter operation private part

    # --- inter operation private part ---
    @classmethod
    def __init_base_date_dataframe(cls, account_id_account_dataframe_dictionary):
        # concat all date dataframe of accounts
        date_dataframe = pandas.concat(
            account_id_account_dataframe_dictionary.values(),
            ignore_index=True
        )

        #  delete repeat data in DATE column
        date_dataframe = cls.__delete_repeat_date(
            source_dataframe=date_dataframe
        )

        concat_total_date_dataframe = pandas.DataFrame()
        concat_total_date_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value] = date_dataframe[
            TQZAutoReportPerAccountDataColumnType.DATE.value]

        return concat_total_date_dataframe

    @classmethod
    def __init_profit_and_loss_total_dataframe(cls, account_id_account_dataframe_dictionary):
        date_dataframe = cls.__init_base_date_dataframe(account_id_account_dataframe_dictionary=account_id_account_dataframe_dictionary)

        profit_and_loss_total_dataframe = pandas.DataFrame()
        profit_and_loss_total_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value] = date_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        profit_and_loss_total_dataframe.set_index(TQZAutoReportPerAccountDataColumnType.DATE.value, inplace=True)

        return profit_and_loss_total_dataframe

    @classmethod
    def __init_balance_fluctuation_singleDay_dataframe(cls, account_id_account_dataframe_dictionary):
        date_dataframe = cls.__init_base_date_dataframe(account_id_account_dataframe_dictionary=account_id_account_dataframe_dictionary)

        balance_fluctuation_singleDay_dataframe = pandas.DataFrame()
        balance_fluctuation_singleDay_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value] = date_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        balance_fluctuation_singleDay_dataframe.set_index(TQZAutoReportPerAccountDataColumnType.DATE.value, inplace=True)

        return balance_fluctuation_singleDay_dataframe

    @classmethod
    def __init_total_balance_following_dataframe(cls, account_id_account_dataframe_dictionary):

        date_dataframe = cls.__init_base_date_dataframe(account_id_account_dataframe_dictionary=account_id_account_dataframe_dictionary)

        total_balance_following_dataframe = pandas.DataFrame()
        total_balance_following_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value] = date_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        total_balance_following_dataframe[TQZAutoReportTotalBalanceFollowingColumnType.DATE_ACCOUNT.value] = date_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        total_balance_following_dataframe.set_index(TQZAutoReportPerAccountDataColumnType.DATE.value, inplace=True)

        return total_balance_following_dataframe

    @classmethod
    def __init_account_id_account_dataframe_dictionary(cls):

        account_id_account_dataframe_dictionary = {}

        for path in os.listdir(TQZAutoReportFilePath.per_account_data_fold()):
            account_id = path.split(".")[0]

            if account_id == "175896":
                continue

            account_id_account_dataframe_dictionary[account_id] = pandas.read_excel(
                io=TQZAutoReportFilePath.per_account_data_fold() + f'/{path}',
                sheet_name=TQZAutoReportSheetType.PER_ACCOUNT_DATA.value
            )

        [per_account_dataframe.drop(per_account_dataframe.index[0], inplace=True) for account_id, per_account_dataframe in account_id_account_dataframe_dictionary.items()]

        return account_id_account_dataframe_dictionary

    @classmethod
    def __delete_repeat_date(cls, source_dataframe):
        """
        Delete repeat data in assign column
        """

        source_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value] = pandas.to_datetime(
            source_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        )

        source_dataframe.index = source_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        del source_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value]
        source_dataframe = source_dataframe.groupby(TQZAutoReportPerAccountDataColumnType.DATE.value).count()
        source_dataframe.sort_index()
        source_dataframe.reset_index(inplace=True)

        return source_dataframe

    @classmethod
    def __sourceData_to_modelList(cls, settlement_jsonfile=None):
        """
        Change source_data to model_list (default order type: account balance)
        """

        account_models = TQZAccountModel.list_to_models(
            account_data_list=TQZJsonOperator.tqz_load_jsonfile(jsonfile=settlement_jsonfile).values(),
            yesterday_accounts_data={}
        )

        account_models_sortedByBalance = sorted(
            account_models,
            key=lambda tqz_account_model: tqz_account_model.balance,
            reverse=True
        )

        return account_models_sortedByBalance

    @classmethod
    def __create_per_account_data_excel(cls, account_all_path, account_balance, account_deposit=0, account_transfer=0, account_bonus=0):
        """
        Create new single account excel
        """

        columns = [
            TQZAutoReportPerAccountDataColumnType.DATE.value,
            TQZAutoReportPerAccountDataColumnType.BALANCE.value,
            TQZAutoReportPerAccountDataColumnType.SHARE.value,
            TQZAutoReportPerAccountDataColumnType.RESULT_PROFIT_AND_LOSS.value,
            TQZAutoReportPerAccountDataColumnType.NET_VALUE.value,
            TQZAutoReportPerAccountDataColumnType.MAX_DRAWDOWN.value,
            TQZAutoReportPerAccountDataColumnType.BALANCE_FLUCTUATION_SINGLE_DAY.value,
            TQZAutoReportPerAccountDataColumnType.NET_VALUE_FLUCTUATION_SINGLE_DAY.value,
            TQZAutoReportPerAccountDataColumnType.AVERAGE_VOLATILITY_SINGLE_DAY.value,
            TQZAutoReportPerAccountDataColumnType.YIELD_RATE_ANNUALIZED.value,
            TQZAutoReportPerAccountDataColumnType.YIELD_RATE_FLUCTUATION_STANDARD_DEVIATION.value,
            TQZAutoReportPerAccountDataColumnType.SHARPE_RATIO.value,
            TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_DEPOSIT.value,
            TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_TRANSFER.value,
            TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_BONUS.value,
        ]

        per_account_dataframe = pandas.DataFrame(columns=columns)

        # default column(first column)
        first_column = len(per_account_dataframe)
        per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.DATE.value] = datetime.date.today() - datetime.timedelta(days=1)
        per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_DEPOSIT.value] = 1000 * 1e4
        per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.BALANCE.value] = per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_DEPOSIT.value]
        per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.SHARE.value] = per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_DEPOSIT.value]
        per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value] = 1
        per_account_dataframe.loc[first_column, TQZAutoReportPerAccountDataColumnType.YIELD_RATE_ANNUALIZED.value] = 1

        per_account_dataframe = cls.__per_account_dataframe_update_last_line(
            per_account_dataframe=per_account_dataframe,
            account_balance=account_balance,
            account_deposit=account_deposit,
            account_transfer=account_transfer,
            account_bonus=account_bonus
        )

        cls.__to_excel(
            dataframe=per_account_dataframe,
            excel_path=account_all_path,
            sheet_name=TQZAutoReportSheetType.PER_ACCOUNT_DATA.value,
            empty_others=False
        )

    @classmethod
    def __per_account_data_updateable(cls):
        """
        Can update per account data or not
        """

        can_update = True
        for path in os.listdir(TQZAutoReportFilePath.source_data_fold()):
            if cls.__source_data_is_update(
                    current_source_data_name=TQZAutoReportFilePath.source_data_fold() + f'/{path}'
            ) is False:
                can_update = False

        if len(os.listdir(TQZAutoReportFilePath.source_data_fold())) is not 1:
            can_update = False

        return can_update

    @classmethod
    def __per_account_dataframe_update_last_line(cls, per_account_dataframe, account_balance, account_deposit, account_transfer, account_bonus):
        """
        Update last line of per account dataframe
        """

        # delete data of current day when today data is exist
        date_list = per_account_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value].values.tolist()
        if TQZAutoReportFilePath.today_string() in date_list:
            per_account_dataframe = per_account_dataframe.drop(index=per_account_dataframe.loc[(per_account_dataframe[TQZAutoReportPerAccountDataColumnType.DATE.value] == TQZAutoReportFilePath.today_string())].index)

        new_row = len(per_account_dataframe)
        # do nothing when balance of previous column is equal to account_balance
        if per_account_dataframe.loc[new_row-1, TQZAutoReportPerAccountDataColumnType.BALANCE.value] == account_balance:
            return per_account_dataframe

        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.DATE.value] = TQZAutoReportFilePath.today_string()
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.BALANCE.value] = account_balance
        if account_deposit != 0:
            per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_DEPOSIT.value] = account_deposit
        if account_transfer != 0:
            per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_TRANSFER.value] = account_transfer
        if account_bonus != 0:
            per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_BONUS.value] = account_bonus

        # 份额
        old_share = per_account_dataframe.loc[new_row-1, TQZAutoReportPerAccountDataColumnType.SHARE.value]
        money_change = account_deposit + (account_transfer * -1)
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.SHARE.value] = old_share + (money_change / per_account_dataframe.loc[new_row - 1, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value])

        # 结算盈亏
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.RESULT_PROFIT_AND_LOSS.value] = account_balance - per_account_dataframe[TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_DEPOSIT.value].sum() + per_account_dataframe[TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_BONUS.value].sum()

        # 净值
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value] = round((account_balance + per_account_dataframe[TQZAutoReportPerAccountDataColumnType.CURRENT_DAY_BONUS.value].sum()) / per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.SHARE.value], 5)

        # 最大回撤
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.MAX_DRAWDOWN.value] = round(max(per_account_dataframe[TQZAutoReportPerAccountDataColumnType.NET_VALUE.value].max() - per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value], 0), 5)

        # 单日权益波动
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.BALANCE_FLUCTUATION_SINGLE_DAY.value] = round(per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.BALANCE.value] - per_account_dataframe.loc[new_row - 1, TQZAutoReportPerAccountDataColumnType.BALANCE.value], 2)

        # 单日净值波动
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.NET_VALUE_FLUCTUATION_SINGLE_DAY.value] = round(per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value] - per_account_dataframe.loc[new_row - 1, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value], 5)

        # 年化收益率
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.YIELD_RATE_ANNUALIZED.value] = round(((per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.NET_VALUE.value] - 1) / len(per_account_dataframe)) * 250, 5)

        # 单日平均波动率
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.AVERAGE_VOLATILITY_SINGLE_DAY.value] = round(per_account_dataframe[TQZAutoReportPerAccountDataColumnType.NET_VALUE_FLUCTUATION_SINGLE_DAY.value].sum() / len(per_account_dataframe), 5)

        # 收益率波动标准差
        per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.YIELD_RATE_FLUCTUATION_STANDARD_DEVIATION.value] = per_account_dataframe[TQZAutoReportPerAccountDataColumnType.AVERAGE_VOLATILITY_SINGLE_DAY.value].std(ddof=0)

        # 夏普比率
        if 0 == per_account_dataframe[TQZAutoReportPerAccountDataColumnType.NET_VALUE_FLUCTUATION_SINGLE_DAY.value].std(ddof=0):
            per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.SHARPE_RATIO.value] = 0
        else:
            per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.SHARPE_RATIO.value] = per_account_dataframe.loc[new_row, TQZAutoReportPerAccountDataColumnType.YIELD_RATE_ANNUALIZED.value] / per_account_dataframe[TQZAutoReportPerAccountDataColumnType.NET_VALUE_FLUCTUATION_SINGLE_DAY.value].std(ddof=0) / math.sqrt(250)

        return per_account_dataframe

    @classmethod
    def __to_excel(cls, dataframe, excel_path, sheet_name, freeze_panes=(1, 0), empty_others=False):
        """
        Write dataframe to excel.
        """

        if empty_others is True:
            currrent_father_path = TQZFilePathOperator.father_path(source_path=excel_path)
            for file_name in os.listdir(currrent_father_path):
                os.remove(path=currrent_father_path + f'/{file_name}')

        excel_writer = pandas.ExcelWriter(path=excel_path)
        dataframe.to_excel(excel_writer, sheet_name=sheet_name, index=False, freeze_panes=freeze_panes)
        excel_writer.save()


if __name__ == '__main__':
    yesterday_accounts_data_jsonfile = "yesterday_accounts_data.json"

    TQZAutoReport.tqz_update(settlement_jsonfile=yesterday_accounts_data_jsonfile)