Revolutionizing Cash Flow Forecasts - A Paradigm Shift in Purchase and Redemption Predictions

Cashflow-Forecasting

Background

The investment funds of Yu'ebao are mainly used to purchase short-term, low-risk money market funds. These funds mainly invest in low-risk investment instruments such as government bonds, central bank bills, and bank deposits. Therefore, the risk is relatively low, making it suitable for users who have short-term capital management needs and are more conservative in their investment preferences.

The main source of income for Yu'ebao comes from the money market fund it invests in. Investors can check their daily earnings through the Alipay app and withdraw funds to their Alipay account at any time for use. However, it should be noted that since money market funds are market investments, their returns are not stable and may be affected by factors such as market interest rates and economic conditions, which pose certain investment risks. When using Yu'ebao, investors should carefully understand the product's risk and return characteristics, and make wise investment decisions based on their own risk tolerance and investment goals. At the same time, the earnings of Yu'ebao are also subject to regulatory policies and product regulations, and investors should pay close attention to changes in relevant policies and regulations.

Project Overview

Project Objectives

1. Cash Flow Forecasting: Develop machine learning models to predict future cash inflows (purchases) and outflows (redemptions) based on historical user transaction data.

2. Enhanced Funds Management: Provide AFSG with actionable insights to optimize its funds management strategies and decision-making.

3. Business Efficiency: Improve operational efficiency by aligning cash inflow and outflow predictions with actual user behavior.

Impact & Expected Results

Potential benefits

1. Optimization of investment decisions: Accurate predictions of fund inflows and outflows can help investors better understand market demand and redemption situations, thereby optimizing capital allocation and investment decisions. For example, if large inflows are predicted, investors can increase their subscription amount in a timely manner; if large outflows are predicted, investors can consider adjusting their redemption plan to avoid possible market risks.

2. Improve capital operation efficiency: Accurately predicting the inflow and outflow of funds can help fund managers better plan and manage funds, thereby improving capital operation efficiency. For example, based on the predicted results, a reasonable fund investment plan can be arranged to avoid the situation of idle or insufficient funds, thus maximizing the return and utilization of funds.

3. Reduce risks: The inflow and outflow of funds are crucial for the stable operation of currency funds. Accurate predictions can avoid potential risks caused by unstable inflows and outflows, such as redemption pressure and insufficient funds. At the same time, it can also help investors better cope with market volatility and risks, and make wiser investment decisions.

4. Enhance competitive advantages: Accurately predicting fund inflows and outflows can help financial institutions better understand market demand and investor behavior, thereby optimizing product design and service strategies, and enhancing competitive advantages. For example, based on predicted results, product subscription and redemption rules can be flexibly adjusted to attract more investors, increase fund size and management fee income.

Submission

Description & Data inspection

1. User profile table

2. Purchase and redemption sheet

3. Yields ratetable

4. Shanghai Interbank Offered Rate (Shibor) Table

Evaluation

1. Calculating the relative error of purchase and redemption:

1. The scoring function f(*) (monotonous decreasing) is used to evaluate the purchase and redemption error. Purchase prediction score is related to the error Purchase i ; so does redemption. For example, if the purchase error $Purchase_i$ =0 on ith day, 10 points are obtained for this day. If $Purchase_i$ > 0.3 , the score is 0.

2. Finally, Total Score =

Project Phases

1. Data Processing & EDA

• Time Series Analysis and Visualization

• Difference in Purchase and Redemption Total Amounts from Monday to Sunday

• Month Feature Analysis

• Date Feature Analysis

• Holiday Analysis

• Analysis of Large Anomalous Transactions

• Correlation Analysis

• Analysis of Bank and Alipay Interest Rates

2. Model Baseline

1. Calculating Proportional Factors: Divide each day's count by the weekly average to obtain proportions.

2. Calculating Median: Calculate the median for each day across weeks.

3. Final Cyclical Factors: The resulting median values serve as a robust set of cyclical factors.

# Import necessary libraries
import pandas as pd  # Import pandas library for data processing
import numpy as np   # Import numpy library for numerical calculations
import datetime      # Import datetime library for date and time operations

# Define the method to generate base for time series forecast
def generate_base(df: pd.DataFrame, month_index: int) -> pd.DataFrame:
    # Select data for a fixed time period
    total_balance = df.copy()  # Copy the input DataFrame to avoid modifying the original data
    total_balance = total_balance[['date', 'total_purchase_amt', 'total_redeem_amt']]  # Select specific columns
    total_balance = total_balance[(total_balance['date'] >= pd.to_datetime('2014/3/1')) & (total_balance['date'] < pd.to_datetime('2014/{}/1'.format(month_index)))]  # Filter data for the specific time period

    # Add timestamp information
    total_balance['weekday'] = total_balance['date'].dt.weekday  # Add weekday information
    total_balance['day'] = total_balance['date'].dt.day          # Add day information
    total_balance['week'] = total_balance['date'].dt.week        # Add week information
    total_balance['month'] = total_balance['date'].dt.month      # Add month information

    # Calculate next-day factors
    mean_of_each_weekday = total_balance[['weekday'] + ['total_purchase_amt', 'total_redeem_amt']].groupby('weekday', as_index=False).mean()  # Calculate mean purchase and redeem amounts for each weekday
    for name in ['total_purchase_amt', 'total_redeem_amt']:
        mean_of_each_weekday = mean_of_each_weekday.rename(columns={name: name+'_weekdaymean'})  # Add suffix to differentiate next-day factors
    mean_of_each_weekday['total_purchase_amt_weekdaymean'] /= np.mean(total_balance['total_purchase_amt'])  # Calculate next-day factor for purchase amount
    mean_of_each_weekday['total_redeem_amt_weekdaymean'] /= np.mean(total_balance['total_redeem_amt'])    # Calculate next-day factor for redeem amount

    # Merge next-day factors into the original dataset
    total_balance = pd.merge(total_balance, mean_of_each_weekday, on='weekday', how='left')  # Merge next-day factors into the original dataset

    # Calculate frequency of next-days appearing on (1~31) days
    weekday_count = total_balance[['day', 'weekday', 'date']].groupby(['day', 'weekday'], as_index=False).count()  # Count occurrences of each day on different weekdays
    weekday_count = pd.merge(weekday_count, mean_of_each_weekday, on='weekday')  # Merge the results with next-day factors

    # Weight next-day factors based on frequency to obtain day factors
    weekday_count['total_purchase_amt_weekdaymean'] *= weekday_count['date'] / len(np.unique(total_balance['month']))  # Calculate weighted day factors for purchase amount
    weekday_count['total_redeem_amt_weekdaymean'] *= weekday_count['date'] / len(np.unique(total_balance['month']))    # Calculate weighted day factors for redeem amount
    day_rate = weekday_count.drop(['weekday', 'date'], axis=1).groupby('day', as_index=False).sum()  # Calculate the sum of weighted factors for each day

    # Obtain base by subtracting the date residuals from the mean of all dates in the training set
    day_mean = total_balance[['day'] + ['total_purchase_amt', 'total_redeem_amt']].groupby('day', as_index=False).mean()  # Calculate mean purchase and redeem amounts for each day
    day_pre = pd.merge(day_mean, day_rate, on='day', how='left')  # Merge the weighted factors with the mean for each day
    day_pre['total_purchase_amt'] /= day_pre['total_purchase_amt_weekdaymean']  # Adjust purchase amount by purchase next-day factor
    day_pre['total_redeem_amt'] /= day_pre['total_redeem_amt_weekdaymean']        # Adjust redeem amount by redeem next-day factor

    # Generate test set data
    for index, row in day_pre.iterrows():
        if month_index in (2, 4, 6, 9) and row['day'] == 31:
            break
        day_pre.loc[index, 'date'] = datetime.datetime(2014, month_index, int(row['day']), 0, 0, 0)  # Generate test set date based on month and day

    # Generate final forecast by multiplying base with next-day factors
    day_pre['weekday'] = day_pre['date'].dt.weekday  # Add weekday information
    day_pre = day_pre[['date', 'weekday'] + ['total_purchase_amt', 'total_redeem_amt']]  # Select necessary columns
    day_pre = pd.merge(day_pre, mean_of_each_weekday, on='weekday')  # Merge next-day factors
    day_pre['total_purchase_amt'] *= day_pre['total_purchase_amt_weekdaymean']  # Adjust purchase amount based on purchase next-day factor
    day_pre['total_redeem_amt'] *= day_pre['total_redeem_amt_weekdaymean']        # Adjust redeem amount based on redeem next-day factor

    day_pre = day_pre.sort_values('date')[['date'] + ['total_purchase_amt', 'total_redeem_amt']]  # Sort by date and select necessary columns
    return day_pre  # Return the generated time series forecast results DataFrame

3. Feature Engineering

• is_weekend: Indicates whether the day falls on a weekend (Saturday or Sunday).

• is_holiday: Indicates whether the day is a holiday.

• is_firstday_of_holiday: Indicates whether the day is the first day of a holiday period.

• is_lastday_of_holiday: Indicates whether the day is the last day of a holiday period.

• is_firstday_of_work: Indicates whether the day is the first workday after a holiday period.

• is_work: Indicates whether it's a workday (neither a holiday nor a weekend).

• is_gonna_work_tomorrow: Indicates whether the next day is a workday.

• is_worked_yestday: Indicates whether the previous day was a workday.

• is_lastday_of_workday: Indicates whether the day is the last workday before a holiday.

• is_work_on_sunday: Indicates whether it's a Sunday that requires working.

• is_firstday_of_month: Indicates whether the day is the first day of a month.

• is_secday_of_month: Indicates whether the day is the second day of a month.

• is_premonth, is_midmonth, is_tailmonth: Indicate whether the day belongs to the early, mid, or late period of a month, respectively.

• is_first_week, is_second_week, is_third_week, is_fourth_week: Indicate whether the week belongs to the first, second, third, or fourth week of the month, respectively.

• dis_to_nowork and dis_from_nowork: Calculate the distance to the next non-working day and the distance from the last non-working day for each data point.

• dis_to_work and dis_from_work: Calculate the distance to the next working day and the distance from the last working day for each data point.

• dis_to_holiday and dis_from_holiday: Calculate the distance to the next holiday and the distance from the last holiday for each data point.

• dis_to_holiendday and dis_from_holiendday: Calculate the distance to the next last day of holiday and the distance from the last last day of holiday for each data point.

• dis_from_startofmonth: Calculate the distance from the start of the month for each data point.

• dis_from_middleofmonth: Calculate the distance from the middle of the month (15th day) for each data point.

• dis_from_middleofweek: Calculate the distance from the middle of the week (Wednesday, represented by 3) for each data point.

• dis_from_endofweek: Calculate the distance from the end of the week (Sunday, represented by 6) for each data point.

4. Model Development & Comparison