.png){kind=logo}
Developing the Performance Report
Developing the Performance Report
Article summary
Did you find this summary helpful?
Thank you for your feedback
Developing the Performance Report
In this example we look at how to create the below Performance report for Assette Capital. It illustrates performance returns for different performance periods for a given account against a benchmark.
The Component Inventory analysis corresponding to the above requirement is illustrated below.
Data Blocks
To extract the required data, use multiple dependency blocks for this performance object as shown below to handle the necessary business logic. Note that the interface block settings are the same as the previous interface blocks developed for the previous data objects.
Interface Blocks:
DEMO_SnowFlakePreCalPerformanceDetailsExtract: Use to extract Pre-Calculated Portfolio Performance
DEMO_SnowFlakePreCalBenchmarkPerformanceExtract: Use to extract Pre-Calculated Benchmark Performance
DEMO_SnowflakeAccountBenchmarkPerformance: Use to extract benchmark performance for account specific periods such as inception to date, fiscal year to date etc.
DEMO_SnowFlakeBenchmarkAssociationDetailsExtractWithoutRank: Use to extract all benchmarks which associated to given Portfolio.
DEMO_AccountbyPurpose: Use to switch account depending on Purpose and ShowComposite data setting. This block already explained under Attribution Object.
DEMO_GetRepCompositeAccountbyProduct: Use to get Performance and Non-Performance (Rep) Accounts from Product Master for given Portfolio Account.
DEMO_SnowFlakePortfolioGeneralInformationDetailsExtract: Use to extract Portfolio General Information to populate required output variables.
DEMO_SnowFlakeBenchmarkGeneralInformationDetailsExtract: Use to extract Benchmark General Information to populate required output variables.
DEMO_GetFiscalYearEndExtract: Use to get Fiscal Year for given Portfolio from Attribute Master.
Environment Blocks:
PythonEnvForDateCalculation: Use to import Date calculation related to python Libraries.
CalculationEnv: Use to import Panda and NumPy Libraries.
Calculation Blocks:
ast_period_code_map
ast_fn_GetValidPeriods
Transformation Blocks:
DEMO_GetFiscalYearEnd: Use to convert Fiscal Year which extract using ‘DEMO_GetFiscalYearEndExtract ‘into required date time format for calculation.
DEMO_Pre Calculated Performance Transform: Use to process data to transfer required performance output, using all interface blocks and Calculations blocks.
Definition of “DEMO_Pre Calculated Performance Transform”
object_blueprint = {
"ENTITYCODE": "",
"HISTORYDATE": "",
"CURRENCYCODE": "",
"CURRENCY": "",
"PERFORMANCECATEGORY": "",
"PERFORMANCECATEGORYNAME": "",
"PERFORMANCETYPE": "",
"PERFORMANCEINCEPTIONDATE": "",
"QUARTERTODATE": "",
"YEARTODATE": "",
"PRIORQUARTER1": "",
"PRIORQUARTER2": "",
"PRIORQUARTER3": "",
"PRIORQUARTER4": "",
"PRIORCALENDARYEAR1": "",
"PRIORCALENDARYEAR2": "",
"PRIORCALENDARYEAR3": "",
"PRIORCALENDARYEAR4": "",
"PRIORCALENDARYEAR5": "",
"PRIORCALENDARYEAR6": "",
"PRIORCALENDARYEAR7": "",
"PRIORCALENDARYEAR8": "",
"PRIORCALENDARYEAR9": "",
"PRIORCALENDARYEAR10": "",
"MONTH1": "",
"MONTHS3": "",
"MONTHS6": "",
"MONTHS9": "",
"YEAR1": "",
"YEAR2CUMULATIVE": "",
"YEAR3CUMULATIVE": "",
"YEAR4CUMULATIVE": "",
"YEAR5CUMULATIVE": "",
"YEAR6CUMULATIVE": "",
"YEAR7CUMULATIVE": "",
"YEAR8CUMULATIVE": "",
"YEAR9CUMULATIVE": "",
"YEAR10CUMULATIVE": "",
"YEAR12CUMULATIVE": "",
"YEAR15CUMULATIVE": "",
"YEAR20CUMULATIVE": "",
"YEAR25CUMULATIVE": None,
"YEAR30CUMULATIVE": None,
"INCEPTIONTODATECUMULATIVE": "",
"YEAR2ANNUALIZED": "",
"YEAR3ANNUALIZED": "",
"YEAR4ANNUALIZED": "",
"YEAR5ANNUALIZED": "",
"YEAR6ANNUALIZED": "",
"YEAR7ANNUALIZED": "",
"YEAR8ANNUALIZED": "",
"YEAR9ANNUALIZED": "",
"YEAR10ANNUALIZED": "",
"YEAR12ANNUALIZED": "",
"YEAR15ANNUALIZED": "",
"YEAR20ANNUALIZED": "",
"YEAR25ANNUALIZED": None,
"YEAR30ANNUALIZED": None,
"INCEPTIONTODATEANNUALIZED": "",
"FISCALYEARTODATE": None,
"PRIORFISCALQUARTER1": None,
"PRIORFISCALQUARTER2": None,
"PRIORFISCALQUARTER3": None,
"PRIORFISCALQUARTER4": None,
"PRIORFISCALYEAR1": None,
"PRIORFISCALYEAR2": None,
"PRIORFISCALYEAR3": None,
"PRIORFISCALYEAR4": None,
"PRIORFISCALYEAR5": None,
"PRIORFISCALYEAR6": None,
"PRIORFISCALYEAR7": None,
"PRIORFISCALYEAR8": None,
"PRIORFISCALYEAR9": None,
"PRIORFISCALYEAR10": None,
"MONTHTODATE": "",
"PRIORMONTH1": None,
"PRIORMONTH2": None,
"PRIORMONTH3": None,
"MOSTRECENTCALENDARYEAR": None
}
portfolio_perf_params = {
"AccountCode":params["AccountCode"],
"AsofDate": params["AsofDate"],
"CurrencyCode": params["CurrencyCode"]
}
portfolio_benchmark_association_params = {
"AccountCode": params["AccountCode"]
}
benchmark_perf_params = {
"BenchmarkCode":"",
"AsofDate": params["AsofDate"],
"CurrencyCode": params ["CurrencyCode"]
}
date_pattern = ""
if "DatePattern" in params.keys():
date_pattern = params["DatePattern"].strip()
if len(date_pattern) == 0:
date_pattern = '%Y-%m-%d'
output_list = []
# Read PreCalculated Performance Details extraction block to retrieve Performance data
PerformanceData = read("DEMO_SnowFlakePreCalPerformanceDetailsExtract", params=portfolio_perf_params)['data']
if len(PerformanceData) > 0:
portfolio_performance_df = pd.DataFrame(PerformanceData)
#Renaming PORTFOLIOCODE as ENTITYCODE
portfolio_performance_df = portfolio_performance_df.rename(columns={"PORTFOLIOCODE": "ENTITYCODE"})
performance_inception_date = portfolio_performance_df.loc[0, "PERFORMANCEINCEPTIONDATE"]
# response["data"] = portfolio_performance_df.to_dict(orient='records')
# Read Benchmark Association Details extraction block to retrieve Benchmark Association
portfolio_benchmark_association = read("DEMO_SnowFlakeBenchmarkAssociationDetailsExtractWithoutRank", params=portfolio_benchmark_association_params)['data']
# Read PreCalculated Account Benchmark Performance extraction block
portfolio_benchmark_performance_df = pd.DataFrame(read("DEMO_SnowflakeAccountBenchmarkPerformance", params=portfolio_perf_params)['data'])
portfolio_benchmark_performance_df["PERFORMANCECATEGORYNAME"] = ""
benchmark_perf_df = pd.DataFrame
benchmark_performance = pd.DataFrame
if len(portfolio_benchmark_association)> 0:
for benchmark_index, benchmark in enumerate(portfolio_benchmark_association):
#Get the benchmark code that associated with the potfolio.
benchmark_perf_params["BenchmarkCode"] = benchmark["BENCHMARKCODE"]
# Read PreCalculated Benchmark Performance extraction block to retrieve Benchmark Performance data
benchmark_performance = pd.DataFrame(read("DEMO_SnowFlakePreCalBenchmarkPerformanceExtract", benchmark_perf_params)['data'])
benchmark_rank = "Index " + str(benchmark["RANK"])
#updating PERFORMANCECATEGORYNAME field in the portfolio_benchmark_performance_df
portfolio_benchmark_performance_df.loc[portfolio_benchmark_performance_df['BENCHMARKCODE'] == benchmark["BENCHMARKCODE"], "PERFORMANCECATEGORYNAME"] = benchmark_rank
if not benchmark_performance.empty:
benchmark_performance["PERFORMANCECATEGORYNAME"] = benchmark_rank
if benchmark_index == 0:
benchmark_perf_df = benchmark_performance
else:
benchmark_perf_df = pd.concat([benchmark_perf_df, benchmark_performance])
benchmark_perf_df = benchmark_perf_df.reset_index(drop=True)
benchmark_perf_df["PERFORMANCECATEGORY"] = "Benchmark"
benchmark_perf_df["PERFORMANCETYPE"] = np.nan
benchmark_perf_df = benchmark_perf_df.rename(columns={"BENCHMARKCODE": "ENTITYCODE"})
portfolio_benchmark_performance_df["PERFORMANCECATEGORY"] = "Benchmark"
portfolio_benchmark_performance_df["PERFORMANCETYPE"] = np.nan
portfolio_benchmark_performance_df = portfolio_benchmark_performance_df.rename(columns={"BENCHMARKCODE": "ENTITYCODE"})
portfolio_benchmark_performance_df = portfolio_benchmark_performance_df.drop(["PORTFOLIOCODE", "HISTORYDATE",
"PERFORMANCECATEGORYNAME", "PERFORMANCECATEGORY", "PERFORMANCETYPE", "CURRENCYCODE", "CURRENCY"], axis=1)
#Merge both benchmark and Account benchmark data frame and create final result frame
final_result_df = pd.merge(benchmark_perf_df, portfolio_benchmark_performance_df, on="ENTITYCODE")
final_result_df["PERFORMANCEINCEPTIONDATE"] = performance_inception_date
column_list = list(object_blueprint.keys())
# Extract the columns that only specified in the blueprint
final_result_df = final_result_df[column_list]
# response["text"] = column_list
# response["values"] = final_result_df.to_dict(orient='records')
portfolio_performance_df = portfolio_performance_df[column_list]
if params["SuppressNotApplicablePeriods"].lower() == "yes":
# Drop the column which has missing value
portfolio_performance_df = portfolio_performance_df.dropna(axis=1, how='all')
period_code_map = read("ast_period_code_map")
period_list = ""
for period_code in period_code_map.keys():
if len(period_code_map[period_code]) > 0:
period_list += period_code + ","
period_list = period_list[:-1]
valid_period_params = {"AsofDate": str(params["AsofDate"]), "InceptionDate": str(performance_inception_date),
"FiscalYearEnd": str(params["FiscalYearEnd"]),
"SuppressNotApplicablePeriods": params["SuppressNotApplicablePeriods"].lower(),
"SuppressDuplicatePeriods": params["SuppressDuplicatePeriods"].lower(),
"PeriodList":period_list,"DatePattern":date_pattern}
valid_period_result = pd.DataFrame(read("ast_fn_GetValidPeriods", valid_period_params)['data'])
valid_period_list = valid_period_result['period'].unique()
# get the period column names sent by the client
valid_period_columns = []
for valid_period in valid_period_list:
valid_period_columns.append(period_code_map[valid_period])
meta_data_columns = ["ENTITYCODE", "HISTORYDATE", "CURRENCYCODE", "CURRENCY", "PERFORMANCECATEGORY",
"PERFORMANCECATEGORYNAME", "PERFORMANCETYPE", "PERFORMANCEINCEPTIONDATE"]
final_frame_columns = meta_data_columns + valid_period_columns
for column_item in final_result_df.columns:
if column_item not in final_frame_columns:
final_result_df = final_result_df.drop(columns=[column_item])
for column_item in portfolio_performance_df.columns:
if column_item not in final_frame_columns:
portfolio_performance_df = portfolio_performance_df.drop(columns=[column_item])
if params["SuppressNullPeriods"].lower() == 'yes':
portfolio_performance_df = portfolio_performance_df.dropna(axis=1, how='all')
for column_item in final_result_df.columns:
if column_item not in portfolio_performance_df:
final_result_df = final_result_df.drop(columns=[column_item])
final_result_df = pd.concat([final_result_df, portfolio_performance_df])
output_list = final_result_df.to_dict(orient='records')
response["data"] = output_listDetails of Calculations Blocks
1. ast_period_code_map Block
This block is used to map the Performance period with the period name which received from client data source side.
Definition of ast_period_code_map Block
{
"QTD": "QUARTERTODATE",
"YTD": "YEARTODATE",
"MRQ": "",
"MRY": "MOSTRECENTCALENDARYEAR",
"PQ1": "PRIORQUARTER1",
"PQ2": "PRIORQUARTER2",
"PQ3": "PRIORQUARTER3",
"PQ4": "PRIORQUARTER4",
"PY1": "PRIORCALENDARYEAR1",
"PY2": "PRIORCALENDARYEAR2",
"PY3": "PRIORCALENDARYEAR3",
"PY4": "PRIORCALENDARYEAR4",
"PY5": "PRIORCALENDARYEAR5",
"PY6": "PRIORCALENDARYEAR6",
"PY7": "PRIORCALENDARYEAR7",
"PY8": "PRIORCALENDARYEAR8",
"PY9": "PRIORCALENDARYEAR9",
"PY10": "PRIORCALENDARYEAR10",
"1MT": "MONTH1",
"3MT": "MONTHS3",
"6MT": "MONTHS6",
"9MT": "MONTHS9",
"12MT": "YEAR1",
"2YC": "YEAR2CUMULATIVE",
"3YC": "YEAR3CUMULATIVE",
"4YC": "YEAR4CUMULATIVE",
"5YC": "YEAR5CUMULATIVE",
"6YC": "YEAR6CUMULATIVE",
"7YC": "YEAR7CUMULATIVE",
"8YC": "YEAR8CUMULATIVE",
"9YC": "YEAR9CUMULATIVE",
"10YC": "YEAR10CUMULATIVE",
"12YC": "YEAR12CUMULATIVE",
"15YC": "YEAR15CUMULATIVE",
"20YC": "YEAR20CUMULATIVE",
"25YC": "YEAR25CUMULATIVE",
"30YC": "YEAR30CUMULATIVE",
"ITD": "INCEPTIONTODATECUMULATIVE",
"2YA": "YEAR2ANNUALIZED",
"3YA": "YEAR3ANNUALIZED",
"4YA": "YEAR4ANNUALIZED",
"5YA": "YEAR5ANNUALIZED",
"6YA": "YEAR6ANNUALIZED",
"7YA": "YEAR7ANNUALIZED",
"8YA": "YEAR8ANNUALIZED",
"9YA": "YEAR9ANNUALIZED",
"10YA": "YEAR10ANNUALIZED",
"12YA": "YEAR12ANNUALIZED",
"15YA": "YEAR15ANNUALIZED",
"20YA": "YEAR20ANNUALIZED",
"25YA": "YEAR25ANNUALIZED",
"30YA": "YEAR30ANNUALIZED",
"ITDA": "INCEPTIONTODATEANNUALIZED",
"FYTD": "FISCALYEARTODATE",
"MRFQ": "",
"PFQ1": "PRIORFISCALQUARTER1",
"PFQ2": "PRIORFISCALQUARTER2",
"PFQ3": "PRIORFISCALQUARTER3",
"PFQ4": "PRIORFISCALQUARTER4",
"PFY1": "PRIORFISCALYEAR1",
"PFY2": "PRIORFISCALYEAR2",
"PFY3": "PRIORFISCALYEAR3",
"PFY4": "PRIORFISCALYEAR4",
"PFY5": "PRIORFISCALYEAR5",
"PFY6": "PRIORFISCALYEAR6",
"PFY7": "PRIORFISCALYEAR7",
"PFY8": "PRIORFISCALYEAR8",
"PFY9": "PRIORFISCALYEAR9",
"PFY10": "PRIORFISCALYEAR10",
"MTD": "MONTHTODATE"
}2. ast_fn_GetValidPeriods: This function will return the begin, end dates for a list of periods. The function can also be used to eliminate duplicate periods based on AsofDate or inapplicable periods based on an inception date.
Definition of ast_fn_GetValidPeriods
suppress_not_applicable = params["SuppressNotApplicablePeriods"]
suppress_duplicate_periods = params["SuppressDuplicatePeriods"]
period_list = params["PeriodList"].split(",")
date_pattern = ""
if "DatePattern" in params.keys():
date_pattern = params["DatePattern"].strip()
if len(date_pattern) == 0:
date_pattern = '%Y-%m-%d'
period_func_params = {"AsofDate": params["AsofDate"], "PeriodCode": "", "InceptionDate": params["InceptionDate"], "FiscalYearEnd": params["FiscalYearEnd"],"DatePattern": date_pattern}
inception_date = datetime.datetime.strptime(params["InceptionDate"],'%Y-%m-%d')
period_frame = pd.DataFrame(columns=["period", "begin_date", "end_date"])
period_index = 0
for period_code in period_list:
period_func_params["PeriodCode"] = period_code
period_begin_end_dates = read("ast_fn_GetBeginEndDates", period_func_params)["data"]
begin_date = datetime.datetime.strptime(period_begin_end_dates[0]["begin_date"], '%Y-%m-%d')
end_date = datetime.datetime.strptime(period_begin_end_dates[0]["end_date"], '%Y-%m-%d')
period_frame.at[period_index, "period"] = period_code
period_frame.at[period_index, "begin_date"] = begin_date
period_frame.at[period_index, "end_date"] = end_date
period_index += 1
if suppress_not_applicable == "yes":
period_frame = period_frame.loc[period_frame['begin_date'] >= inception_date]
if suppress_duplicate_periods == "yes":
# period_frame = period_frame.drop_duplicates(["begin_date","end_date"])
rank_mapping = {"MTD": 1, "QTD": 2, "YTD": 3, "12MT": 4}
period_frame["RANK"] = period_frame["period"].map(rank_mapping)
period_frame["RANK"].fillna("", inplace=True)
selected_periods = ["QTD", "MTD", "YTD", "12MT"]
filtered_period_frame = period_frame[period_frame["period"].isin(selected_periods)]
period_frame = period_frame[~period_frame["period"].isin(selected_periods)]
filtered_period_frame.sort_values(by=["RANK", "begin_date"], ascending=[True, True], inplace=True)
filtered_period_frame.drop_duplicates(subset=["begin_date", "end_date"], keep="first", inplace=True)
period_frame = pd.concat([filtered_period_frame, period_frame], ignore_index=True)
period_frame = period_frame.drop(columns=["RANK"])
period_frame['begin_date'] = period_frame['begin_date'].astype(str)
period_frame['end_date'] = period_frame['end_date'].astype(str)
# print(period_frame)
output_list = period_frame.to_dict(orient='records')
response['data'] = output_list
# response['data'] = period_func_paramsData Object
To create the ‘Performance’ Data Object we can use the Data Block ‘DEMO_Performance’
Click on ‘Create New’ under ‘Data Objects’ tab from Developer Center
Select the options as shown below and hit ‘Create”.
Give an appropriate name.
Set your Data Block
Set the Data Settings one by one
Set the Column name one by one as needed.
Note: We need to create an extra column called ‘Name’ and the instructions are given below.
Column ‘Name’ setup is shown below.
Dynamic Value – conditions above
If - PERFORMANCECATEGORY = "Asset Class" and PERFORMANCECATEGORYNAME = "Total Portfolio" and PERFORMANCETYPE = "Portfolio Gross" Then - {{PortfolioName}} (Gross)
Else If - PERFORMANCECATEGORY = "Asset Class" and PERFORMANCECATEGORYNAME = "Total Portfolio" and PERFORMANCETYPE = "Portfolio Net" Then - {{PortfolioName}} (Net)
Else If - PERFORMANCECATEGORY = "Benchmark" and PERFORMANCECATEGORYNAME = "Index 1" Then - {{Index1}}
Else If - PERFORMANCECATEGORY = "Benchmark" and PERFORMANCECATEGORYNAME = "Index 2" Then - {{Index2}}
Else If - PERFORMANCECATEGORY = "Excess Return" and PERFORMANCECATEGORYNAME = "Index 1" Then - Excess Index 1
Else If - PERFORMANCECATEGORY = "Excess Return" and PERFORMANCECATEGORYNAME = "Index 2" Then - Excess Index 2
Else - {{value}}
Set up the column ‘Since Inception’ – to show with dynamic date.
Go to Row Type and set the rows with conditions as shown below.
Conditions as below
If - PERFORMANCECATEGORY = "Asset Class" and PERFORMANCECATEGORYNAME = "Total Portfolio" and PERFORMANCETYPE = "Portfolio Gross" Then - Portfolio Gross
Else If - PERFORMANCECATEGORY = "Asset Class" and PERFORMANCECATEGORYNAME = "Total Portfolio" and PERFORMANCETYPE = "Portfolio Net" Then - Portfolio Net
Else If - PERFORMANCECATEGORY = "Benchmark" and PERFORMANCECATEGORYNAME = "Index 1" Then - Index 1
Else If - PERFORMANCECATEGORY = "Benchmark" and PERFORMANCECATEGORYNAME = "Index 2" Then – Index 1
Else If - PERFORMANCECATEGORY = "Excess Return" and PERFORMANCECATEGORYNAME = "Index 1" Then - Excess Index 1
Else If - PERFORMANCECATEGORY = "Excess Return" and PERFORMANCECATEGORYNAME = "Index 2" Then - Excess Index 2
Go to Legend and set the Title column to ‘Name’
.png "image(273).png")
Authoring Smart page (Performance - Table)
Insert a native PowerPoint table as shown below (10 Columns by 6 Rows table structure)
Open Fabrication panel as shown below.
Set the Columns, Rows and Data Settings as shown below.
Authoring Smart page (Performance - Chart)
Insert a bar Chart
Set the Series and Categories
Set the Data settings.
