The Sub Accounts Client Database (SubAccountsClientDB) Data Block is a System Data Block and is expected to be edited. By default, this Data Block retrieves data from Assette’s Demo Snowflake instance.
The Sub Accounts Client Database (SubAccountsClientDB) Data Block retrieves subaccount data along with associated attributes like strategy and vehicle from Assette’s Demo Snowflake instance. It first fetches subaccount relationships, then merges them with account details and attribute values to generate a comprehensive view of each subaccount. The Sub Accounts Client Database (SubAccountsClientDB) Data Block processes the data, fills in missing fields, and organizes the output by adding strategy and vehicle details for each subaccount. Finally, it returns the structured subaccount information in the response. This Data Block is expected to be edited in order to retrieve data from the client’s source.
Data Retrieval: The Sub Accounts Client Database (SubAccountsClientDB) Data Block performs the following actions:
- Retrieves subaccount relationships from the
Source_SubAccountsRelationshiptable based onAccountCode. - Retrieves account details from the
Source_ExtractAccountsDetailstable and merges them with the subaccount data. - Fetches attribute values from the
Source_AccountAttributesValuestable, filtering forStrategyandVehicleattributes, and merges them with the account details.
Data Processing:
- The Sub Accounts Client Database (SubAccountsClientDB) Data Block fills missing fields like
AccountID,Code,Name,Category,Manager,Strategy,Vehicle, etc., with default values. - For each subaccount, it adds the
StrategyandVehicleattribute values (if available) by matching thePORTFOLIOCODE. - The Data Block sorts the resulting data by
InceptionDateand renames key columns for consistency (e.g.,CODEtoCode,NAMEtoName).
General Info #
The following table shows the default fields of the Data Block.
| Field | Value |
|---|---|
| Name | SubAccountsClientDB |
| Block Category | Transform |
| Block Type | Python |
| Data Category | None |
| Output Type | Data Table |
Dependencies #
The following table shows the default dependencies of the Data Block.
| Data Block | Description |
|---|---|
| CalculationEnv | Used to import Python modules and libraries (e.g., “import numpy as np”) [Link] |
| Source_SubAccountsRelationship | The Source Sub-Accounts Relationship Data Block (Source_SubAccountsRelationship) Data Block retrieves portfolio group associations from the DEMO_DB database. It returns the MEMBERPORTFOLIOCODE as Code and the PortfolioCode as GroupAccountCode based on a specified AccountCode. The Source Sub-Accounts Relationship Data Block (Source_SubAccountsRelationship) Data Block filters the data using the provided AccountCode and returns the associated portfolio details. This Data Block is expected to be edited. [Link] |
| Source_ExtractAccountsDetails | Source Extract Accounts Details is a Transform Data Block used to extract data related to the account master from client’s source data (e.g., Snowflake). This Data Block is expected to be edited by the client and therefore the name and output is subject to change. [Link] |
| Source_AccountAttributesValues | The Source Account Attributes Values Data Block is used to interface with a client’s data source (e.g., Snowflake) and must be edited by the end user during implementation. [Link] |
Columns #
None
Example Definition #
AccountCode=""
if(params.__len__()>0):
if "Code" in params :
AccountCode=params["Code"]
outputlist=[]
data_df = pd.DataFrame(read("Source_SubAccountsRelationship",{"AccountCode":AccountCode})["data"])
Accountdata_df= pd.DataFrame(read("Source_ExtractAccountsDetails",{})["data"])
AccountDetails_df = pd.merge(data_df, Accountdata_df, left_on="CODE", right_on="PORTFOLIOCODE", how="inner")
account_list = data_df["CODE"].unique()
# print(AccountDetails_df)
AttributeData_df = pd.DataFrame(read("Source_AccountAttributesValues", {})["data"])
# Pivot the DataFrame to get "Strategy" and "Vehicle" as columns
AttributeData_df = AttributeData_df.loc[AttributeData_df["ATTRIBUTETYPE"].isin(["Strategy","Vehicle"])]
# print(AttributeData_df)
AttributeData_df = AttributeData_df.loc[AttributeData_df["PORTFOLIOCODE"].isin(account_list)]
AccountDetails_df["Strategy"] = None
AccountDetails_df["Vehicle"] = None
for row_index, row_item in AccountDetails_df.iterrows():
strategy_data = AttributeData_df.loc[(AttributeData_df["PORTFOLIOCODE"] == row_item["CODE"]) & (AttributeData_df["ATTRIBUTETYPE"] == "Strategy")]
vehicle_data = AttributeData_df.loc[(AttributeData_df["PORTFOLIOCODE"] == row_item["CODE"]) & (AttributeData_df["ATTRIBUTETYPE"] == "Vehicle")]
strategy_data.reset_index(inplace=True)
vehicle_data.reset_index(inplace=True)
if len(strategy_data) > 0:
AccountDetails_df.loc[row_index, "Strategy"] = strategy_data.at[0,"ATTRIBUTETYPECODE"] + "|" + strategy_data.at[0,"ATTRIBUTETYPEVALUE"]
else:
AccountDetails_df.loc[row_index, "Strategy"] = ""
if len(vehicle_data) > 0:
AccountDetails_df.loc[row_index, "Vehicle"] = vehicle_data.at[0,"ATTRIBUTETYPECODE"] + "|" + vehicle_data.at[0,"ATTRIBUTETYPEVALUE"]
else:
AccountDetails_df.loc[row_index, "Vehicle"] = ""
# Merge with base_df on "PORTFOLIOCODE"
base_df = AccountDetails_df
print(base_df.columns)
if base_df.shape[0]>0:
if "AccountID" not in base_df.columns:
base_df["AccountID"]=""
if "CODE" not in base_df.columns:
base_df["CODE"]=""
if "NAME" not in base_df.columns:
base_df["NAME"]=""
if "PORTFOLIOCATEGORY" not in base_df.columns:
base_df["PORTFOLIOCATEGORY"]=""
if "Manager" not in base_df.columns:
base_df["Manager"]=""
if "Strategy" not in base_df.columns:
base_df["Strategy"]=""
if "Vehicle" not in base_df.columns:
base_df["Vehicle"]=""
if "OPENDATE" not in base_df.columns:
base_df["OPENDATE"]=""
if "GroupAccountId" not in base_df.columns:
base_df["GroupAccountId"]=0
if "GROUPACCOUNTCODE" not in base_df.columns:
base_df["GROUPACCOUNTCODE"]=""
base_df.rename(columns={
"CODE": "Code",
"NAME": "Name",
"PORTFOLIOCATEGORY": "Category",
"OPENDATE": "InceptionDate",
"GROUPACCOUNTCODE":"GroupAccountCode"
}, inplace=True)
base_df.sort_values(by="InceptionDate", ascending=True, inplace=True)
for i in range(0, base_df.shape[0]):
base_df.loc[i,"ID"]=i
outputlist.append({ "AccountID":base_df.loc[i,"AccountID"],"Code":base_df.loc[i,"Code"],"Name":base_df.loc[i,"Name"],"Category":base_df.loc[i,"Category"]
,"Manager":base_df.loc[i,"Manager"],"Strategy":base_df.loc[i,"Strategy"],"Vehicle":base_df.loc[i,"Vehicle"],"InceptionDate":base_df.loc[i,"InceptionDate"],"GroupAccountId":base_df.loc[i,"GroupAccountId"],"GroupAccountCode":base_df.loc[i,"GroupAccountCode"]})
# Append data by AccountCode
response["data"] = base_df.to_dict(orient="records")Example Request #
The following code shows an example request using the default values of the Data Block.
{
"Code": "1120"
}Example Response #
The following code shows an example response using the default values of the Data Block. The default data source is the Assette Snowflake Demo Instance. The response may be shortened for brevity.
{
"data": [
{
"Code": "bbdlcvmodel",
"GroupAccountCode": "1120",
"PORTFOLIOCODE": "bbdlcvmodel",
"Name": "Fortpoint Artists Retirement Plan",
"INVESTMENTSTYLE": "NULL",
"Category": "Individual Account",
"InceptionDate": "2010-06-01",
"PERFORMANCEINCEPTIONDATE": "2010-06-01",
"TERMINATIONDATE": null,
"BASECURRENCYCODE": "USD",
"BASECURRENCYNAME": "US Dollar",
"PRODUCTCODE": "PRDK01",
"Strategy": "DLCV|Diversified Large Cap Value",
"Vehicle": "",
"AccountID": "",
"Manager": "",
"GroupAccountId": 0,
"ID": 0
},
{
"Code": "lwdlcvxmodel",
"GroupAccountCode": "1120",
"PORTFOLIOCODE": "lwdlcvxmodel",
"Name": "Charles1296",
"INVESTMENTSTYLE": "NULL",
"Category": "Individual Account",
"InceptionDate": "2016-01-31",
"PERFORMANCEINCEPTIONDATE": "2016-01-31",
"TERMINATIONDATE": null,
"BASECURRENCYCODE": "USD",
"BASECURRENCYNAME": "US Dollar",
"PRODUCTCODE": "PRDK01",
"Strategy": "DLCV|Diversified Large Cap Value",
"Vehicle": "",
"AccountID": "",
"Manager": "",
"GroupAccountId": 0,
"ID": 1
}
],
"errors": [],
"success": true,
"logs": []
}