[Python] Function in Python Task

Apologies if I've not previously added much details for people to help. I've now added more details that I think are relevant. I'm stuck at Question 3 with figuring out what's wrong with my code. However, some questions have been answered with codes written prior question 3. See below.

Question 1: Download two files called: • Quarterly MySuper statistics from September 2020 to September 2024 • Quarterly MySuper statistics backseries September 2013 to June 2020 From these two files, write a function to create a single combined dataset of quarterly MySuper product statistics. The relevant data is contained in Table 1a and Table 2a of both spreadsheets. The combined dataset should contain the following variables: Period, MySuper product name, Fund name, Fund ABN, Fund Trustee, Fund public offer status, Fund type, Total Assets, Total investment fees and costs (rep member), Net investment return, Net return (rep member) – Quarterly.The function should take as an input the two files and return a single data frame with the relevant variables.

#QUESTION 1 Codes

def Mysuper_dataset():
files = ["20241127 Quarterly MySuper statistics from September 2020 to September 2024.xlsx",
"Quarterly MySuper statistics backseries September 2013 to June 2020.xlsx"] # Input files

sheets = ["Table 1a", "Table 2a"] # Relevant data to extract

# Dataframe for storage
all_data = []

for file in files:
for sheet in sheets:
try:
df = pd.read_excel(file, sheet_name=sheet)
df["Workbook"] = file # Track source file
df["Sheet"] = sheet # Track source sheet
all_data.append(df)
except Exception as e:
print(f"Couldn't read {sheet} from {file}: {e}")

# Combine all data into one dataframe
if all_data:
combined_df = pd.concat(all_data, ignore_index=True)

# Normalize column names (remove non-alphanumeric characters and strip spaces)
combined_df.columns = combined_df.columns.str.replace(r'[^A-Za-z0-9_]', '', regex=True)
combined_df.columns = combined_df.columns.str.strip()

# Print out the columns of the combined dataframe to check if data was loaded correctly
print("Columns in the Combined DataFrame:")
print(combined_df.columns.tolist()) # Show all column names loaded into the dataframe

# Specify the selected columns required
relevant_columns = ['Period', 'MySuper_product_name', 'Fund_name', 'Fund_ABN', 'Fund_Trustee',
'Fund_public_offer_status', 'Fund_type', 'Fund_trustee','Total_assets',
'Total_investment_fees_and_costs_rep_member', 'Net_investment_return',
'Net_return_rep_member_Quarterly']

# Check if selected columns exist in the dataframe
valid_columns = [col for col in relevant_columns if col in combined_df.columns]

if valid_columns:
combined_data = combined_df[valid_columns]

# Print selected columns that exist in the DataFrame
print("Selected Columns that Exist in DataFrame:")
print(valid_columns)

return combined_data
else:
print("None of the selected columns exist in the combined data.")
return None
else:
print("No data found.")
return None

# Run the function
combined_data = Mysuper_dataset()

# Check first few rows if data exists
if combined_data is not None:
print(combined_data.head(10))


NOTE: THIS IS A SAMPLE INPUT DATA USED FOR ANSWERING THE PRECEDING QUESTIONS


combined_data = {
'Period' [2022-12-31, 2023-07-30, 2021-02-25],
'MySuper_product_name':['ADF MySuper', 'BUSSQ MySuper','MySuper'],
'Fund_name':['Prime Super', 'NGS Super', 'Rei Super'],
'Fund_ABN':['98765432100', '12242568945', '45658642306'],
'Fund_public_offer_status':['public offer', 'Non public offer', 'public offer'],
'Fund_type':['Industry', 'Retail', 'Public Sector'],
'Fund_trustee':['Diversa Trustees Limited', 'Vision Super Pty Ltd', 'CBA'],
'Total_assets':['170.5', '1,807.1', '131,949.3'], # In million dollars
'Total_investment_fees_and_costs_rep_member': ['-0.37', '-0.06','0.30'], # In %
'Net_investment_return ': ['-8.25', '9.28','2.35'], # In %
'Net_return_rep_member_Quarterly':['-6.31', '9.19','-0.97'] # In %
}



Question 2: The Explanatory Notes tab of the Quarterly MySuper statistics spreadsheet provides the following mathematical formulas for calculating 1-, 3- and 5-year annualised net returns:

part of Question 2 (a) Write a function that calculates annualised net returns for an arbitrary time period in years depending on the length of the input vector or array. The function should take as input a vector/array of quarterly returns and return the annualised net return for the correct number of years. For example, if the function takes as input 4 quarterly return figures, it should return the 1-year annualised net return, if it takes as input 12 quarterly return figures it should return the 3-year annualised net return.

#QUESTION 2a Codes:
def annual_returns(Net_return_rep_member_Quarterly):

# Find out how many quarters we have
num_quarters = len(Net_return_rep_member_Quarterly)

# Calculate how many years the input represents
years = num_quarters / 4

# Calculate the total return over all quarters
total_returns = np.prod([1 + r for r in Net_return_rep_member_Quarterly]) - 1

# calculate the annual return
annualized = (1 + total_returns) ** (1 / years) - 1

return annualized


Question 3 (This is the question that I'm currently stuck at):

There are systematic differences in the net return performance of each type of fund (Retail, Corporate, Industry, Public Sector). Write a function that returns the total asset weighted average annualised seven-year net return for each MySuper product fund type over the period to June 2023 using the clean dataset from Section 1 and the annualised return function you wrote for Section 2.

Question 3 codes:

def weighted_average_by_fund_type(combined_data):
# Group by Fund_type
grouped = combined_data.groupby('Fund_type')

# Initialize a list to store results
weighted_averages = []

# Loop through each group (fund type)
for fund_type, group in grouped:
# Calculate the total assets for the current fund type
total_assets = group['Total_assets'].sum()

# Calculate the weighted average return for the current fund type
weighted_avg_return = (group['Net_return_rep_member_Quarterly'] * group['Total_assets']).sum() / total_assets

# Store the result as a tuple (Fund_type, Weighted Average Return)
weighted_averages.append((fund_type, weighted_avg_return))

# Convert results to DataFrame for better presentation
weighted_avg_df = pd.DataFrame(weighted_averages, columns=['Fund_type', 'Weighted_Avg_Return'])

return weighted_avg_df

# Run the function to get the weighted average annualized return
combined_data = Mysuper_dataset()

if combined_data is not None:
weighted_avg_by_fund_type = weighted_average_by_fund_type(combined_data)
print(weighted_avg_by_fund_type)


This function gives me zero as answer. See image below Which doesn't look right.



Thank you.

Continue reading...