finsec_fund_analysis_step05.py

# --- Functions to support pandas lambda function ---
def calc_pct_bought_sold(this_share_count, last_share_count):
    if this_share_count > 0 and last_share_count == 0:
        return 100
    elif this_share_count == 0 and last_share_count == 0:   # In the rare circumstances that both this share count and last share count are zero return 0.
        return 0
    else:
        return ((this_share_count/last_share_count)-1)*100
 
def calc_dollar_bought_sold(count_bought_sold, last_holding_val, last_share_count, this_holding_val, this_share_count):
    if this_share_count == 0:
        if last_share_count == 0:   # In the rare circumstances that both this share count and last share count are zero return 0.
            return 0
        else:
            return count_bought_sold * (last_holding_val/last_share_count)
    else:
        return count_bought_sold * (this_holding_val/this_share_count)

# --- Function for creating a processed dataframe ---
def process_dataframe(this_qtr_df, last_qtr_df):
    this_qtr_df = this_qtr_df[['Manager Name', 'Ticker', 'Holding value','Share or principal amount count', 'Portfolio percentage']].groupby(['Manager Name', 'Ticker'],as_index=False).sum()
    last_qtr_df = last_qtr_df[['Manager Name', 'Ticker', 'Holding value','Share or principal amount count', 'Portfolio percentage']].groupby(['Manager Name', 'Ticker'],as_index=False).sum()

    processed_dataframe = pd.merge(last_qtr_df, this_qtr_df, how='outer', left_on=['Manager Name', 'Ticker'], right_on=['Manager Name', 'Ticker'], suffixes=['_last', '_this'])
    processed_dataframe = processed_dataframe.fillna({'Holding value_last':0,
       'Share or principal amount count_last':0, 
       'Portfolio percentage_last':0,
       'Holding value_this':0, 
       'Share or principal amount count_this':0,
       'Portfolio percentage_this':0})  # Fillna with zeroes to ensure calculations work. 
    processed_dataframe['pct bought/sold holdings'] = processed_dataframe.apply(lambda x: calc_pct_bought_sold(x['Share or principal amount count_this'],x['Share or principal amount count_last']), axis=1)
    processed_dataframe['pct bought/sold holdings weighted by portfolio pct invested'] = processed_dataframe.apply(lambda x: x["pct bought/sold holdings"] * (x["Portfolio percentage_this"]/100), axis=1)
    processed_dataframe['# bought/sold holdings'] = processed_dataframe['Share or principal amount count_this'] - processed_dataframe['Share or principal amount count_last']
    processed_dataframe['$ bought/sold holdings'] = processed_dataframe.apply(lambda x: calc_dollar_bought_sold(x['# bought/sold holdings'], x['Holding value_last'],x['Share or principal amount count_last'], x['Holding value_this'],x['Share or principal amount count_this']), axis=1)

    ticker_dataframe = processed_dataframe[['Ticker', 'Holding value_last','Share or principal amount count_last', 'Portfolio percentage_last','Holding value_this', 'Share or principal amount count_this','Portfolio percentage_this', 'pct bought/sold holdings','pct bought/sold holdings weighted by portfolio pct invested','# bought/sold holdings','$ bought/sold holdings']].groupby('Ticker',as_index=False).agg(value_held_last=('Holding value_last', 'sum'), count_held_last=('Share or principal amount count_last','sum'), mean_portfolio_pct_last=('Portfolio percentage_last','mean'),value_held_this=('Holding value_this', 'sum'), count_held_this=('Share or principal amount count_this','sum'), avg_portfolio_pct_this=('Portfolio percentage_this','mean'), avg_pct_bought_sold=('pct bought/sold holdings','mean'),avg_bought_sold_weight_by_portfolio_pct=('pct bought/sold holdings weighted by portfolio pct invested','mean'),total_bought_sold=('# bought/sold holdings','sum'), dollars_bought_sold=('$ bought/sold holdings','sum')).sort_values(by=['dollars_bought_sold'], ascending=False)
    
    return processed_dataframe, ticker_dataframe


# --- Create a processed dataframe ---
processed_dataframe, ticker_dataframe = process_dataframe(all_this_qtr_holdings, all_last_qtr_holdings)