Using an Excel template to import and value a portfolio

In this tutorial we’ll see how to use the Import Data dashboard to set up a new portfolio, master some instruments and holdings for the portfolio, and upload market prices for those instruments.

We can then create a valuation recipe and value the portfolio using the LUSID web app.

Note
To complete this tutorial, you must have suitable permissions. This can most easily be achieved by assigning your LUSID user the built-in lusid-administrator role. This should already be the case if you are the domain owner.

You can download the following Excel file to use a pre-populated set of data for the templates in steps 1-4:

Import Data Tutorial

35.37 KB

Step 1: Setting up a new portfolio

The first step is to import the necessary data to create our portfolio in LUSID. To do this:

Sign in to the LUSID web app as a user with suitable permissions.
From the top left menu, navigate to Data Management > Import Data.
Type or select the data type Lusid.Portfolio from the list and click the Download Excel template button.
Populate the template with your portfolio data, including:
- A PortfolioScope and unique PortfolioCode within the scope.
- Upsert as WriteAction. This creates a new portfolio if the portfolio scope and code pairing does not yet exist in LUSID, or updates the record if it does.
- A PortfolioType; for this tutorial we’ll specify Transaction. Read more.
- A BaseCurrency to which foreign currency transactions can be normalised upon provision of a trade to portfolio rate. This must be an ISO 4217 currency code, for example GBP.
- A CreatedDate prior to today (the default). This is important if you intend to load historical activity.
- Optionally, an instrument scope, transaction type scope, corporate action source and sub-holding keys; read more. You can also add custom properties to the template; see how.
Tip
The following can help you populate the Excel template with data:
- Mandatory columns are highlighted in red.
- The column name specifies its required data type.
- You can hover over a column name to view a description and list of allowed values (if required).
Read more about using templates in the LUSID web app.
Save the populated template and upload it to LUSID using the Upload file button.

The table of results displays the upserted data. You can inspect the WriteError, WriteErrorCode and WriteErrorDetail columns to learn more about any errors that may have occurred.

Step 2: Mastering equity and bond instruments

Now that we’ve created a portfolio, we can use the same method to upsert some bond and equity instruments.

To master equity instruments:

On the Import Data dashboard, select Lusid.Instrument.Equity and click Download Excel template.
Populate the template with your equity instrument data, including:
- A Scope (data partition) in which to create each instrument.
- At least one unique identifier for the instrument.
- Upsert as WriteAction. This creates a new instrument if the unique identifier does not yet exist within the scope, or updates the record if it does.
- The instrument currency under DomCcy.
Read more about equities in LUSID.
Save the populated template and upload it to LUSID using the Upload file button.

The table of results is as follows:

To master bond instruments, repeat the above steps, this time selecting Lusid.Instrument.Bond before clicking Download Excel template.

The bond data should include:

A Scope (data partition) in which to create each instrument.
At least one unique identifier for the instrument.
Upsert as WriteAction. This creates a new instrument if the unique identifier does not yet exist within the scope, or updates the record if it does.
The instrument currency under DomCcy.
The bond CouponRate as a decimal rather than a percentage, so for example a bond paying 0.375% should have a value of 0.00375.
The principal. For this tutorial, we’ll set the value to 1.
The accrual StartDate.
The bond MaturityDate.
Values for FlowConventionsCurrency, FlowConventionsDayCountConvention, FlowConventionsPaymentCalendars, FlowConventionsPaymentFrequency, FlowConventionsRollConvention. Read more about bonds and flow conventions in LUSID.

The table of results is as follows:

Step 3: Uploading quotes

We can also use the Import Data dashboard to load quotes for equities, bonds, and FX rates. To load your quotes into LUSID:

On the Import Data dashboard, select Lusid.Instrument.Quote and click Download Excel template.
Populate the template with your pricing data, including:
- A QuoteScope in which to upload each quote.
- The financial data vendor as Provider.
- An InstrumentIdType and InstrumentId that together constitute an instrument identifier linking the quote to an instrument mastered in LUSID.
- Upsert as WriteAction. This creates a new quote if it does not yet exist within the scope, or updates the record if it does.
- A QuoteEffectiveAt date from which the quote is valid.
- A QuoteType such as Price or Rate.
- A Value and Unit pair, constituting the actual price or rate and a currency (for price) or currency pair (for rate).
- A Field appropriate for the provider. For this tutorial, we’ll specify mid. Read more about quote provider fields and values.
Save the populated template and upload it to LUSID using the Upload file button.

Part of the table of results is as follows:

Step 4: Importing transactions

The last data we need to upload via the Import Data dashboard are transactions for our portfolio. To load transactions into LUSID:

On the Import Data dashboard, select Lusid.Portfolio.Txn and click Download Excel template.
Populate the template with your transaction data, including:
- Upsert as WriteAction. This creates a new transaction if it does not yet exist, or updates the record if it does.
- The transaction PortfolioScope and PortfolioCode.
- At least one unique identifier that resolves to an instrument mastered in LUSID.
- A TxnId that uniquely identifies the transaction within the portfolio.
- The trade Type, for example FundsIn or Buy.
- The TotalConsideration, specifying the trade amount in the settlement currency.
- The TransactionDate, TradePrice, TradeCurrency and number of Units.
- The SettlementDate and SettlementCurrency.
  Read more about creating transactions in LUSID.
Save the populated template and upload it to LUSID using the Upload file button.

The table of results is as follows:

Step 5: Configuring a recipe and performing a portfolio valuation

Now that we’ve loaded all of the necessary data into LUSID, we can create a recipe and perform a valuation on our portfolio.

To create a recipe, we can navigate to Data Management > Recipes in the LUSID web app and provide the following JSON to create a new recipe:

Click to expand

{
   "scope": "DataImporter",
   "code": "EquityBondTutorialRecipe",
   "market": {
     "marketRules": [
       {
         "key": "Quote.Figi.*",
         "supplier": "Edi",
         "dataScope": "DataImporter",
         "quoteType": "Price",
         "field": "mid"
       },
      {
         "key": "Quote.Isin.*",
         "supplier": "DataScope",
         "dataScope": "DataImporter",
         "quoteType": "Price",
         "field": "mid"
       },
      {
         "key": "Fx.CurrencyPair.*",
         "supplier": "Lusid",
         "dataScope": "DataImporter",
         "quoteType": "Rate",
         "field": "mid",
         "quoteInterval": "2D.0D"
       }
     ]
   },
"pricing": {
        "modelRules": [
            {
                "supplier": "Lusid",
                "modelName": "SimpleStatic",
                "instrumentType": "Bond"
            }
        ]
},
   "description": "A recipe to value a multi-region portfolio containing equities and bonds"
 }