Back

Python Leverage Data Merge

Python

Pandas - Python
    


#######################################


import pandas as pd
import os
map_data = {'Ticker Symbol': ['IAF', 'FAX', 'FAP', 'FCO', 'ACP', 'AGD', 'AOD', 'AWP', 'JEQ', 'IFN', 'AEF', 'ASGI', 'VFL', 'HQH', 'THQ', 'THW', 'HQL'],'Fund Name': ['abrdn Australia Equity Fund, Inc.', 'abrdn Asia-Pacific Income Fund, Inc.', 'abrdn Asia-Pacific Income Fund, Inc. 198C', 'abrdn Global Income Fund, Inc.', 'abrdn Income Credit Strategies Fund', 'abrdn Global Dynamic Dividend', 'abrdn Total Dynamic Dividend', 'abrdn Global Premier Property', 'abrdn Japan Equity Fund, Inc.', 'The India Fund Inc', 'abrdn Emerging Markets Equity Income Fund, Inc.', 'abrdn Global Infrastructure Income Fund', 'abrdn Nat Muni Inc Fund', 'abrdn Healthcare Investors', 'abrdn Healthcare Opportunities Fund', 'abrdn World Healthcare Fund', 'abrdn Life Sciences Investors'],'Fund ID': ['1956', '197C', '198C', '896C', 'ACAD', 'ALPA', 'ALPB', 'ALPD', 'DRBR', 'DRBX', 'DRBZ', 'DRCY', 'DRKE', 'TNOO', 'TNO1', 'TNO2', '19E7'],'Fund Code': ['SC1956', 'SC197A', 'SC198A', 'SC8965', 'FS9993', 'PP5055', 'PP5056', 'AGLP', 'FS7131', 'FS9509', 'FS2442', 'DRCY', 'ZLFT', 'L36R', 'XMY9', '327X', '274Z']}
map_df = pd.DataFrame(map_data)
print(map_df)
# Read all Excel files from the "input" folder
input_folder = "input"
file_list = [f for f in os.listdir(input_folder) if f.endswith('.xlsx')]
all_data = []
# Read each file, preserve required columns, and append to the all_data list
cols_to_keep = ['Ticker', 'Asset', 'NAV', 'Leverage', 'Market Price', 'Premium Discount', 'Assets Grossed UP', 'Income', 'Date As Of']
for file in file_list:
    file_path = os.path.join(input_folder, file)
    data = pd.read_excel(file_path)
    data = data[cols_to_keep]
    all_data.append(data)
# 
# # Combine all_data into a single DataFrame and add the three blank columns
combined_data = pd.concat(all_data, axis=0, ignore_index=True)
combined_data['ST Cap Gain Dist'] = 0
combined_data['Capstock Dollars (NET)'] = 0
combined_data['LT Cap Gain Dist'] = 0
max_data = pd.DataFrame()
# Group by Ticker and get the highest date value for each ticker 
max_data = combined_data.groupby('Ticker')['Date As Of'].max().reset_index()
# Rename columns
max_data.columns = ['Ticker Symbol', 'MaxDate']
max_data.loc[max_data['Ticker Symbol'] == 'DRBX', 'Ticker Symbol'] = 'IFN'
combined_data.rename(columns = {'Ticker': 'Ticker Symbol', 'Asset':'Net Assets', 'Assets Grossed UP':'Gross Assets', 'Date As Of': 'Date', 'Leverage': 'Leverage $'},inplace=True)
combined_data.loc[combined_data['Ticker Symbol'] == 'DRBX', 'Ticker Symbol'] = 'IFN'
returns_df = combined_data


print("\nTicker and Max Dates:") 
print(max_data)
print("\nReturns Data")
print(returns_df)

# #######NET PROCEEDS############
import xlrd
# Define the folder path
np_folder_path = "NP_Daily"
# # Get all the .xls files in the folder
np_file_list = [f for f in os.listdir(np_folder_path) if f.endswith('.XLS')]
# # Initialize an empty DataFrame to store the combined_data
np_combined_data = pd.DataFrame()
# # Loop through the files, read the data, and append to the new combined_data DataFrame
for np_file in np_file_list:
    np_file_path = os.path.join(np_folder_path, np_file)
    np_temp_data = pd.read_excel(np_file_path, usecols=['Ticker Symbol' 'Fund ID', 'CNAV Total Net Assets Leverage $','Gross Assets', 'CNAV NAV', 'Market Price', 'Premium Discount', 'Capstock Dollars (NET)', 'ST Cap Gain Dist', 'LT Cap Gain Dist', 'CNAV Income Distribution''Date'], engine='xlrd')
    np_combined_data = np_combined_data.append(np_temp_data, ignore_index=True)


np_combined_data['Ticker Symbol'] = np_combined_data['Ticker Symbol'].apply(lambda x: x[1:-1])
np_combined_data.loc[np_combined_data['Fund ID'] == '197C', 'Ticker Symbol'] = 'FAX'
np_combined_data.loc[np_combined_data['Fund ID'] == 'ACAC', 'Fund ID'] = 'ACAD'
np_combined_data.loc[np_combined_data['Ticker Symbol'] == 'AGI', 'Ticker Symbol'] = 'ASGI' 
np_combined_data.loc[np_combined_data['Ticker Symbol'] == 'DRBX', 'Ticker Symbol'] = 'IFN'

print(np_combined_data)
result_df = pd.merge(np_combined_data, max_data, on='Ticker Symbol', how='left') 
filtered_df = result_df.dropna (subset=['MaxDate'])
filtered_df['Date'] = pd.to_datetime(filtered_df['Date'])
filtered_df['MaxDate'] = pd.to_datetime(filtered_df['MaxDate'])
# Convert string dates to datetime
filtered_df['GreaterDate'] = 'No'
# Update 'GreaterDate' values to 'Yes' based on the condition
filtered_df.loc[filtered_df['Date'] > filtered_df['MaxDate'], 'GreaterDate'] = 'Yes'
result_df = filtered_df.loc[filtered_df['GreaterDate'] == 'Yes']

result_df = result_df.drop('GreaterDate', axis=1)
result_df = result_df.drop('MaxDate', axis=1)
result_df = result_df.drop('Fund ID', axis=1)
result_df.rename(columns = {'CNAV Total Net Assets':'Net Assets', 'CNAV NAV': 'NAV', 'CNAV Income Distribution':'Income'}, inplace=True)
result_df.loc[result_df['Ticker Symbol'] == 'DRBX', 'Ticker Symbol'] = 'IFN' 
net_proceeds_df = result_df



net_proceeds_df = result_df
print("NET PROCEEDS")
print(net_proceeds_df)
frames = [returns_df, net_proceeds_df]
final = pd.concat(frames)
print("FINAL")
print(final.head (40))
final_join = pd.merge(final, map_df, on='Ticker Symbol', how='left')
final_join["Capital Activity"] = final_join['Capstock Dollars (NET)'] + final_join['LT Cap Gain Dist']+ final_join['ST Cap Gain Dist']
final_join = final_join.drop('LT Cap Gain Dist',axis=1)
final_join = final_join.drop('ST Cap Gain Dist', axis=1)
final_join = final_join.drop('Capstock Dollars (NET)', axis=1)
final_join.sort_values(by=['Date'], ascending=False)
final_join = final_join[['Ticker Symbol', 'Fund Name', 'Fund ID', 'Fund Code', 'Net Assets', 'Gross Assets', 'NAV', 'Leverage $','Market Price', 'Premium Discount', 'Income', 'Capital Activity','Date']]
print(final_join.head(40))
final_join.to_excel('final_output.xlsx', index=False)