Top/Bottom 'N' Holdings - Data Block

• Snowflake Block Setup

Before building the data block, it's essential to break down the required data blocks needed for the selected sample object called "Top/Bottom N Holdings." Since this is a holding object, we need to extract holding data from a data source. After selecting the data source, we need to identify the necessary configuration blocks to connect with the data source.

In this case, we are extracting data from Snowflake. To connect with the Snowflake database, we require two blocks:

• Snowflake Certificate Mapped Data Block: This block is responsible for handling the Snowflake certificate mapping.

• Snowflake Configuration Setting Block: This block will store essential configuration details such as username, database, schema, etc., required for connecting to Snowflake.

Using these two blocks as dependencies, we can create a third block for extracting data. Finally, with the extracted data, we can modify the output as needed using a "Transform Data" block.

Set Up block for Snowflake Certificate Mapped Data Block:

Get certificate details for the relevant environment and db and create block with below setting values.

Block Name: can be give as user want (Preferred without space)

Output Type: Settings

Category: Config

Block Type: Text Settings

Create Snowflake Configuration Setting Block:

Block Name: DEMO_SnowFlakeSettingBlock can be give as user want (Preferred without space)

Output Type: Settings

Category: Config

Block Type: Settings

Note: The certificate block name should be used as ‘certificate’ value in definition section.

Holding data extract block with 2 dependency blocks.

Block Name: DEMO_SnowFlakeHoldingsDetailsExtract

Output Type: Data Table

Category: Extract

Block Type: Snowflake Db Call

Definition: 

{

"type": "table",

"sql": "SELECT PORTFOLIOCODE,CURRENCYCODE,CURRENCY,ISSUERNAME,ISSUENAME,ISSUEDISPLAYNAME,COSTBASIS,QUANTITY,MARKETVALUEWITHOUTACCRUEDINCOME,ACCRUEDINCOME,MARKETVALUE,LOCALMARKETVALUE,UNREALIZEDGAINSLOSSES,DIVIDENDYIELD,ESTIMATEDANNUALINCOME,HOLDINGSPERIOD,PORTFOLIOWEIGHT,PRICE,BOOKVALUE,LOCALBOOKVALUE,ASSETCLASSNAME,ISSUETYPE,ISINCODE,CUSIP,FIGIID,TICKER,RISKCOUNTRYCODE,RISKCOUNTRY,HQCOUNTRYCODE,HQCOUNTRY,ISSUECOUNTRYCODE,ISSUECOUNTRY,REGIONCLASSIFICATIONSCHEME,REGIONNAME,PRIMARYSECTORSCHEME,PRIMARYSECTORNAME,PRIMARYSUBSECTORNAME,PRIMARYINDUSTRYGROUPNAME,PRIMARYINDUSTRYNAME,PRIMARYSUBINDUSTRYNAME,SECONDARYSECTORSCHEME,SECONDARYSECTORNAME,SECONDARYSUBSECTORNAME,SECONDARYINDUSTRYGROUPNAME,SECONDARYINDUSTRYNAME,SECONDARYSUBINDUSTRYNAME,CUSTOMCLASSIFICATION1NAME FROM DEMO_DB.DBO.HOLDINGSDETAILS WHERE PORTFOLIOCODE = ? AND HISTORYDATE = ? AND CURRENCYCODE = ? AND PRIMARYSECTORSCHEME = ?",

"parameters": ["AccountCode", "AsofDate","CurrencyCode","SectorScheme"]

}

Meta Data:

Dependencies:

Holding data transform block

Block Name: DEMO_TopBottomNHoldings

Output Type: Data Table

Category: Transform

Block Type: Python

Definition:

object_blueprint= {

            "PORTFOLIOCODE":"",

            "CURRENCYCODE":"",

            "CURRENCY":"",

            "ISSUERNAME":"",

            "ISSUENAME":"",

            "ISSUEDISPLAYNAME":"",

            "COSTBASIS":"",

            "QUANTITY":"",

            "MARKETVALUEWITHOUTACCRUEDINCOME":"",

            "ACCRUEDINCOME":"",

            "MARKETVALUE":"",

            "ASSETCLASSNAME":"",

            "ISINCODE":"",

            "CUSIP":"",

            "FIGIID":"",

            "TICKER":"",

            "Percentage": "",

            "UnitCost": ""

            }

AccountCode = ""

AsofDate = ""

CurrencyCode=""

SectorScheme=""

WithCash=""

ShowCash=""

DataDisplayFor=""

NoOfRecords = ""

if (params.__len__() > 0):

    AccountCode = params["AccountCode"]

    AsofDate = params["AsofDate"]

    WithCash=params["WithCash"]

    ShowCash=params["ShowCash"]

    CurrencyCode=params["CurrencyCode"]

    SectorScheme=params["SectorScheme"]

    DataDisplayFor=params["DataDisplayFor"]

    NoOfRecords = int(params["NoOfRecords"])

basedata_df = pd.DataFrame(read("DEMO_SnowFlakeHoldingsDetailsExtract",{"AccountCode": AccountCode, "AsofDate": AsofDate, "CurrencyCode":CurrencyCode, "SectorScheme":SectorScheme})["data"])

currencycode=read("DEMO_SnowflakeGetPortfolioBaseCurrency",{"AccountCode": AccountCode})["data"]

baseCurrencyCode=currencycode[0]["BASECURRENCYCODE"]

baseCurrencyName=currencycode[0]["BASECURRENCYNAME"]

if WithCash == "yes":

    data_df = basedata_df

else:

    data_df = basedata_df.loc[basedata_df['ASSETCLASSNAME'] != 'Cash and Equiv.'].reset_index()

# Convert String Market Value into Float

data_df["MARKETVALUE"] = data_df["MARKETVALUE"].astype(float)

data_df["COSTBASIS"] = data_df["COSTBASIS"].astype(float)

data_df["QUANTITY"] = data_df["QUANTITY"].astype(float)

# calculate total marketvalue

totalMV = data_df["MARKETVALUE"].sum()

data_df['TotalMV']=totalMV

data_df['Percentage'] = data_df.apply(lambda row: row["MARKETVALUE"]/row["TotalMV"], axis=1)

data_df['UnitCost'] = data_df.apply(lambda row: row["COSTBASIS"]/row["QUANTITY"], axis=1)

# Depending on the data display requirment select the sorting order

if DataDisplayFor == "Top":

    is_asc = False

else:

    is_asc = True

# sort data set according to the Top/ Bottom

df_sorted = data_df.sort_values(by='MARKETVALUE', ascending=is_asc)

if WithCash == "yes" and ShowCash != "yes":

    df_sorted = df_sorted.loc[df_sorted['ASSETCLASSNAME'] != 'Cash and Equiv.']

# Select no of records by setting

df_sorted = df_sorted.iloc[:NoOfRecords].reset_index()

del_column_list = []

for col_name in df_sorted.columns:

    if col_name not in object_blueprint.keys():

        del_column_list.append(col_name)

df_sorted = df_sorted.drop(del_column_list, axis=1)

output_list = df_sorted.to_dict(orient='records')

# Add output paramets

outputValue = dict()

outputValue["BaseCurrencyCode"] =  baseCurrencyCode

outputValue["BaseCurrencyName"] = baseCurrencyName

response["data"] = output_list

response["values"]= outputValue

Dependency: The Python library related to the 'CalculationEnv' block, and the holding data extraction block become dependencies for this transform block.