--- title: "Using an Excel template to import and value a portfolio" slug: "importing-and-valuing-a-portfolio-from-an-excel-template" updated: 2025-06-23T16:15:42Z published: 2025-06-23T16:15:42Z canonical: "support.lusid.com/importing-and-valuing-a-portfolio-from-an-excel-template" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # 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. You can download the following Excel file to use a pre-populated set of data for the templates in steps 1-4: [](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/Import Data Tutorial(1).xlsx)Import Data Tutorial35.37 KB[](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/Import Data Tutorial(1).xlsx) ## Step 1: Setting up a new portfolio The first step is to import the necessary data to create our [portfolio](/v1/docs/portfolios) in LUSID. To do this: 1. Sign in to the [LUSID web app](https://www.lusid.com/app) as a user with suitable permissions. 2. From the top left menu, navigate to **Data Management > Import Data**. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(297).png) 3. Type or select the data type `Lusid.Portfolio` from the list and click the **Download Excel template** button. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(299).png) 4. Populate the template with your portfolio data, including: > [!TIP] > 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](/v1/docs/importing-data-using-the-lusid-web-app). ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(300).png) - 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.](/v1/docs/portfolios) - 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](https://en.wikipedia.org/wiki/ISO_4217#Numeric_codes), 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.](/v1/docs/how-do-i-create-a-transaction-portfolio) You can also add custom properties to the template; [see how](/importing-data-using-the-lusid-web-app#adding-or-deleting-properties-from-an-entity-template). 5. Save the populated template and upload it to LUSID using the **Upload file** button. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(301).png) 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. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(302).png) ## 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](/v1/docs/instruments). To master equity instruments: 1. On the **Import Data** dashboard, select `Lusid.Instrument.Equity` and click **Download Excel template**. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(305).png) 2. Populate the template with your equity instrument data, including: [Read more about equities in LUSID](/v1/docs/modelling-equities-in-lusid). ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(306).png) - A `Scope` (data partition) in which to create each instrument. - At least one [unique identifier](/v1/docs/what-are-unique-and-non-unique-identifiers) 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`. 3. Save the populated template and upload it to LUSID using the **Upload file** button. The table of results is as follows: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(307).png) To master bond instruments, repeat the above steps, this time selecting `Lusid.Instrument.Bond` before clicking **Download Excel template**. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(308).png) The bond data should include: - A `Scope` (data partition) in which to create each instrument. - At least one [unique identifier](/v1/docs/what-are-unique-and-non-unique-identifiers) 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](/v1/docs/modelling-fixed-rate-vanilla-bonds-in-lusid). The table of results is as follows: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(309).png) ## Step 3: Uploading quotes We can also use the **Import Data** dashboard to load [quotes](/v1/docs/market-data) for equities, bonds, and FX rates. To load your quotes into LUSID: 1. On the **Import Data** dashboard, select `Lusid.Instrument.Quote` and click **Download Excel template**. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(310).png) 2. 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](/v1/docs/how-do-i-upload-a-market-price-or-fx-spot-rate-to-the-quote-store). ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(311).png) 3. Save the populated template and upload it to LUSID using the **Upload file** button. Part of the table of results is as follows: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(312).png) ## Step 4: Importing transactions The last data we need to upload via the **Import Data** dashboard are [transactions](/v1/docs/transactions) for our portfolio. To load transactions into LUSID: 1. On the **Import Data** dashboard, select `Lusid.Portfolio.Txn` and click **Download Excel template**. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(313).png) 2. 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](/v1/docs/what-are-unique-and-non-unique-identifiers) 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](/v1/docs/how-do-i-create-or-update-a-transaction). ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(314).png) 3. Save the populated template and upload it to LUSID using the **Upload file** button. The table of results is as follows: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(315).png) ## 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](/v1/docs/what-is-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*** ```json {   "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.*.*",         "supplier": "Lusid",         "dataScope": "DataImporter",         "quoteType": "Rate",         "field": "mid",         "quoteInterval": "2D.0D"       }     ]   }, "pricing": {        "modelRules": [            {                "supplier": "Lusid",                "modelName": "BondLookupPricer",                "instrumentType": "Bond"            }        ] },   "description": "A recipe to value a multi-region portfolio containing equities and bonds" } ``` ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(316).png) [Read more on creating recipes](/v1/docs/how-do-i-create-or-generate-a-recipe). Finally, to visualise the results of the valuation, navigate to **Dashboard > Valuations**. Select our **Portfolio** and **Recipe** and set the **Effective** date to **8 Mar 2022** to generate a valuation view: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(320).png)