<jupyter_start><jupyter_text>QUAAF Returns  Package imports<jupyter_code>import math #Various math
import numpy as np #Linear algebra
import matplotlib.pyplot as plt #Basic plotting package
import seaborn as sns #Fancy plotting package
import pandas as pd #Dataframes
import statistics #Stats
sns.set_style('darkgrid')<jupyter_output><empty_output><jupyter_text>Data loading<jupyter_code>#Copy all the current holding funds from original data set and 
#Paste them into a new excel sheet. Name it as "QUAAF-data.xlsx".

#Loading in the spread sheet using pandas
fund = pd.read_excel('/Users/user/Desktop/QUAAF-data.xlsx',header=3,index_col=2);

#Checking the loaded data
fund.head();
fund.info();<jupyter_output><class 'pandas.core.frame.DataFrame'>
Index: 123 entries, nan to nan
Data columns (total 16 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   Name        107 non-null    object
 1   Unnamed: 1  106 non-null    object
 2   Unnamed: 3  105 non-null    object
 3   Fund A      5 non-null      object
 4   Fund B      106 non-null    object
 5   Fund C      106 non-null    object
 6   Fund D      56 non-null     object
 7   Fund E      106 non-null    object
 8   Fund F      33 non-null     object
 9   Fund G      30 non-null     object
 10  Fund H      27 non-null     object
 11  Fund I      19 non-null     object
 12  Fund J      14 non-null     object
 13  Fund K      14 non-null     object
 14  Fund L      106 non-null    object
 15  Fund M      106 non-null    object
dtypes: object(16)
memory usage: 16.3+ KB<jupyter_text>Data Cleaning<jupyter_code>#Dropping first 3 rows
fund1 = fund.drop(fund.index[1:3])
fund1.info()
#Drop first 3 columns
fund2 = fund1.drop(fund.columns[0:3],axis=1)
fund2.info()
#Checking the dataframe now
fund2.head().append(fund2.tail())
#Replacing NaNs with 0s (No position = $0 position)
fund2 = fund2.fillna(0)
fund2<jupyter_output><empty_output><jupyter_text>Defining a function to calculate returns<jupyter_code>#Using pythons datetime package
import datetime 

#Calculating how many months have passed since January, April (transition periods)
current_month = fund2.index[-1]

#Defining January & April start dates (2019/2020)
jan = datetime.datetime(2019, 12, 31)
april = datetime.datetime(2020,5,29)

#Calcuating differences
delta_jan = (current_month - jan)
delta_april = (current_month - april)
months_since_jan = int(delta_jan.days)//30
months_since_april = int(delta_april.days)//30
#Using pythons defaultdict function from the collections package
from collections import defaultdict

#https://www.accelebrate.com/blog/using-defaultdict-python
#Change the cumulative since inception formula
#Change the content of the output
#Find a way to upoad the input automatically
# find a way to combine the scotiastatement summary to this python file (i.e. consolidate)

def get_return(fund):
    fund_returns = defaultdict(pd.DataFrame)
    fund_return = pd.DataFrame(fund2[fund].dropna()) #pull a fund out of data frame without NA value
    fund_return = fund_return[(fund_return != 0).all(1)].copy() #clean 0 value 
    fund_return["monthly_return"] = fund_return[fund].pct_change() #calculate monthly return
    fund_return["return_since_jan"] = fund_return[fund]/fund_return[fund].shift(months_since_jan)-1 #return as of Jan 1st
    fund_return["return_since_april"] = fund_return[fund]/fund_return[fund].shift(months_since_april)-1 #return as of Apr 1st
    fund_return["3m_return"] = fund_return[fund]/fund_return[fund].shift(3)-1 #3 month return
    fund_return["6m_return"] = fund_return[fund]/fund_return[fund].shift(6)-1 #6 month return
    fund_return["1y_return"] = fund_return[fund]/fund_return[fund].shift(12)-1 #1 year return
    fund_return["2y_return"] = fund_return[fund]/fund_return[fund].shift(24)-1 #2 year return
    fund_return["5y_return"] = fund_return[fund]/fund_return[fund].shift(60)-1 #5 year return
    fund_return["cumulative_return"] = ((1 + fund_return["monthly_return"]).cumprod() - 1) #cumulative return, and total return
    fund_returns[fund] = fund_return
    return fund_returns[fund]<jupyter_output><empty_output><jupyter_text>Compiling returns in a list of dfs<jupyter_code>#Extracting fund names from df
my_funds = list(fund2.columns.values) 
my_funds

#Using a for loop to run get_return on each fund in my_funds & store each df in a list
returns_list = []
for i in my_funds:
    returns = get_return(i)
    returns_list.append(returns)
#Checking our list of dataframes (first df in list)
returns_list[0]<jupyter_output><empty_output><jupyter_text>Producing risk summary df<jupyter_code>#Using a for loop to select the last row in every df in returns_list
tails_list = []
for i in returns_list:
    tails = i.tail(1)
    tails_list.append(tails)
#Checking the results (first 3 funds)
tails_list[1]
#Converting list of dfs to a single df
summary_df = pd.concat(tails_list)

#Checking our final df
summary_df<jupyter_output><empty_output><jupyter_text>Cleaning up summary_df<jupyter_code>#Changing the first column of every df in tails to "position_value"
for i in tails_list:
    i.columns = ['Position_Value','Monthly_Return','Return_Since_April','Return_Since_Jan','3M_Return','6M_Return','1Y_Return','2Y_Return','5Y_Return','Cumulative_Return']
    
#Creating a new summary df
summary_df_fixed = pd.concat(tails_list)

#Checking the results
summary_df_fixed
#Converting list of position names my_funds to a numpy array
name_array = np.array(my_funds)

#Adding position names to the end summary_df_fixed
summary_df_fixed['Position_Name'] = name_array

#Checking the results
summary_df_fixed
#Reordering the columns so "Position_Name" is the first column
summary_df_final = summary_df_fixed[['Position_Name','Position_Value','Monthly_Return','Return_Since_April','Return_Since_Jan','3M_Return','6M_Return','1Y_Return','2Y_Return',
                                      '5Y_Return','Cumulative_Return']]

#Checking the result
summary_df_final<jupyter_output><empty_output><jupyter_text>Assuming all out of date positions have been reduced to 0 (and hence should be removed)<jupyter_code>#Reordering df by index (in this case date)
summary_df_final = summary_df_final.sort_index()

#Using some loops to drop all outdated positions from the final df
for i in range (0,len(summary_df_final.index)):
    if summary_df_final.index[0] != summary_df_final.index[1]:
        summary_df_final = summary_df_final.drop(summary_df_final.index[0])

#Checking the results
summary_df_final<jupyter_output><empty_output><jupyter_text>Viz for monthly report<jupyter_code>#Visualizing our positions by value
plt1 = summary_df_final.plot(x="Position_Name",y="Position_Value", 
                      kind = 'bar',figsize = (20,6), color = "green",
                      legend = False, title = 'Holdings as of September 30th 2020')
plt1.set(xlabel = "Position Name", ylabel = "Position Value [CAD]");
#By this past months return
plt2 = summary_df_final.plot(x="Position_Name",y="Monthly_Return", 
                      kind = 'bar',figsize = (20,6), color = "green",
                      legend = False, title = 'YTD Returns for the Month Ending September 30th 2020')
plt2.set(xlabel = "Position Name", ylabel = "Monthly Return");
#By return since April 1st, 2020
plt3 = summary_df_final.plot(x="Position_Name",y="Return_Since_April", 
                      kind = 'bar',figsize = (20,6), color = "green",
                      legend = False, title = 'Returns Since April 1st, 2020 [As of September 30th 2020]')
plt3.set(xlabel = "Position Name", ylabel = "Cumulative Return");
#By return since Jan 1, 2020
plt4 = summary_df_final.plot(x="Position_Name",y="Return_Since_Jan", 
                      kind = 'bar',figsize = (20,6), color = "green",
                      legend = False, title = 'Returns Since Jan 1st, 2020 [As of September 30th 2020]')
plt4.set(xlabel = "Position Name", ylabel = "Cumulative Return");
#By return to date
plt5 = summary_df_final.plot(x="Position_Name",y="1Y_Return", 
                      kind = 'bar',figsize = (20,6), color = "green",
                      legend = False, title = 'Cumulative Returns as of September 30th 2020')
plt5.set(xlabel = "Position Name", ylabel = "Cumulative Return");<jupyter_output><empty_output><jupyter_text>Fancier visualizations<jupyter_code>#Position values vs time
plt6 = fund2.plot(figsize = (20,10), title = 'Holdings Over Time',lw=3)
plt6.set(xlabel = "Year",ylabel = "Position Value [CAD]");<jupyter_output><empty_output><jupyter_text>Exporting to excel<jupyter_code>#Exporting summary_df_final to an excel spreadsheet 
summary_df_final.to_excel('Monthly_Risk_Summary.xlsx')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10534790109258195, 'fraction_numerical': 0.03001725129384704, 'mean_word_length': 4.418068535825546, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '411948', 'license': 'MIT', 'n_tokens_mistral': 3018, 'n_tokens_neox': 2793, 'n_words': 783, 'path': 'automating_fund_tracking.ipynb', 'repo_name': 'BrandonToushan/Finance', 'size': '8695'}