Spreadsheet Importing

What Is Spreadsheet Importing?
Creating a Spreadsheet Ready for Import
Accounts Spreadsheet Import
Asset/Holding Spreadsheet Import
Transaction Spreadsheet Import
Uploading the Spreadsheet in the Admin Portal (Back Office)

---

What Is Spreadsheet Importing?

A convenient method for entering Accounts, Asset/Holdings, and Transactions into Copia, through the Admin Portal (Back Office). 

This feature is particularly beneficial when you need to import a large amount of data into Copia and is often used to speed up the initial onboarding process.

Creating a Spreadsheet Ready for Import

You can use your own spreadsheet template, or Copia's templates.  If you're using your own spreadsheet, pay special attention to the columns headers and requirements listed below each type of import. You'll have an opportunity to match these when you upload your sheet.

Generally, we recommend importing your portfolio in three steps. 

1. Import accounts (after building your entity map) 
2. Import assets/holdings (because holdings need accounts)
3. Import transactions (because transactions need holdings/accounts) 
   
   

Accounts Spreadsheet Import

The Account Importer is used to load in a list of accounts into the system.

• These accounts must be connected to an existing entity and a manager. (If there is no manager please indicate "Self-Managed" as shown in the examples.)

• It is important that the account name matches the same name you are using in other spreadsheet uploads where you are connecting holdings and transactions to the account.

• Please also note that you must select the account types from the list of specified options.
  

• Finally, do not change the ordering of the tabs in the spreadsheet, we will only look at the first tab

   

Accounts Fields

1. Account*: A name or number that uniquely represents the account. This is used for linking between accounts, holdings and transactions as part of data imports. It is very important that whatever you use here is consistent across spreadsheet uploads. This is how we connect the data together.

2. Nickname : A short name for visual display if you don't want to display the account name. This is primarily for display. If the account name is something that doesn't make it clear what the account contains, this is a good way to provide clarity. It shouldn't be needed in most cases because for an import you select the Account name.

3. Type*: The type of accounts. This must be selected from the dropdown pre-populated with account types that we use in the system. If you don't see an applicable account type you may select "other".

4. Entity*: The exact name of the entity that owns the account and the assets in the account. The name of the entity that owns the account and/or the assets in the account.  Please create the entity first and copy the name.

5. Last 4*: The last 4 digits of the account number (or similar). This is primarily for display. It is useful to display the last 4 digits of the account number or some other unique identifier to help distinguish between similarly named accounts.
6. Manager*: The exact name of the manager responsible for the assets in the account. You can re-map this later. If the assets are managed internally use Self-Managed as the manager. This manager must already exist in the system.
   
   

Asset/Holding Import

The Asset/Holding Importer is used to load in a list of holdings into the system.

• These holdings must be connected to an existing account and optionally, a Manager. (A default Manager is inherited from the account that the Asset is held in.)

• If the Asset does not yet exist in the system the importer will produce a WARNING which you can override by selecting the holdings you wish to import.

• It is important that the asset name matches the same name you are using in other spreadsheet uploads where you are connecting holdings and transactions to the same asset.

• It is important that the account name matches the same name you are using in other spreadsheet uploads where you are connecting holdings and transactions to the same account.

• Please also note that you must select the asset class from the list of specified options.

• Finally, do not change the ordering of the tabs in the spreadsheet, we will only look at the first tab.

Asset/Holding Fields

1. Asset Name*: A name that uniquely represents the asset. This is used for display and as a way to connect holdings of the same asset in the system. It is also used in the same way by the transactions spreadsheet.
2. Asset Class*: The class of the asset from the Copia Asset Classes, once imported into the system.
3. Holding Name: Individual Name for the Holding. The is a way to distinguish between multiple holdings of the same asset that may be held in separate Accounts or by different Entities.
4. Short Name: A shorter version of the asset name for display purposes. This is used for display in several places in the application and especially relevant if the asset name is long. This can be particularly useful for things like bonds, options and other types of assets the include details in the name of the asset.
5. Ticker: Symbol, Ticker, ISIN, CUSIP or similar unique id. This is used to determine if two holdings are of the same asset. If this field is excluded we will lookup related assets based on the asset name field.
6. Account*:  A name or number that uniquely represents the account. This is used for linking between accounts, holdings and transactions as part of data imports. It is very important that whatever you use here is consistent across spreadsheet uploads. This is how we connect the data together.
7. Manager*: The default manager for assets held in the account. This manager must already exist in the system.  If the assets are managed internally use Self-Managed as the manager.  You can re-map this later.
8. Market Value: The current value of the asset. In some cases this will be used to produce a market observation for the current value of the asset if there isn't a better method of calculating its value. 
9. Price: The amount paid for a single unit of the asset. The cost basis for the asset is calculated by multiplying the price per unit times the number of units in the account. Please load in transactions instead of using a blended price if there are multiple lots.
10. Units*: (Only required for holdings with units.) The number of units of the asset that comprise the given holding. Units are used in multiple places including calculations of cost basis and validations to determine if the data in the system is complete. It is very important that the number of units is correct and that it matches the associated units purchased via the imported transactions.
11. Currency*: The currency that the asset is held in. This allows us to value the asset in its original currency and then translate that currency into the office currency for aggregate reporting if they differ.
12. Inception Date*: Format YYYY-MM-DD. The date that this holding of the asset was purchased. This is used in several places to validate the transactional information is correct and to provide a window to be used when calculating market value and metrics during the ownership period.
13. Exit Date: Only provide for disposed assets. Format YYYY-MM-DD. The date this holding was sold, if it has been disposed. This is used in several places to validate the transactional information is correct and to provide a window to be used when calculating market value and metrics during the ownership period.
14. K-1?: Is this asset K-1 eligible? When this is enabled the system will show the Holding in the K-1 lens and will warn you when a K-1 has not been received. You may dismiss the warning at any time. Will assume "no" if not provided.
15. Capital Committed: Amount of capital committed if the asset is subject to capital calls. When provided this will allow the Holding to be tracked in the Capital Call lens. Will assume asset has no commitment if this is not set.
16. Domiciled Country: The county where the asset is held. This is stored on the asset.

Transaction Spreadsheet Import

The Transaction Importer is used to load in a list of transactions that will be associated with holdings of assets in a specified account.

• These transactions must be connected to an existing asset, holding and account.

• It is important that the account name matches the same name you are using in other spreadsheet uploads where you are connecting holdings and transactions to the account.

• Please also note that you must select the transaction types from the list of specified options.

• Finally, do not change the ordering of the tabs in the spreadsheet, we will only look at the first tab.

1. Asset Name*: The exact name that uniquely represents the asset. This is used for display and as a way to connect holdings of the same asset in the system. It is also used in the same way by the transactions spreadsheet
2. Holding Name: Individual Name for the Holding. The is a way to distinguish between multiple holdings of the same asset that may be held in separate Accounts or by different Entities.
3. Amount*:  The value of the transaction in the specified currency. The value of the transaction will be factored into Cost Basis, Performance and Metrics calculations, Market Value and other calculations.
4. Units*: (Only required for certain transaction types on holdings that require units) The number of units involved in the transaction. If the transaction includes a number of units and it is a purchase or sale type of transaction this will result in the a change to the number of units in the holding in the specified account.
5. Currency*: The currency that the asset is held in. This allows us to value the asset in its original currency and then translate that currency into the office currency for aggregate reporting if they differ.
6. Date*:  Format YYYY-MM-DD. The date of the transaction for metrics purposes. This is used in the calculation of metrics so that it is important the the date here is the date you would like to use for performance and metrics calculations.
7. Account*:  The exact name or number that uniquely represents the account. This is used for linking between accounts, holdings and transactions as part of data imports. It is very important that whatever you use here is consistent across spreadsheet uploads. This is how we connect the data together.
8. Notes: A descriptive note that explains the transaction.
9. Ticker: Exact Symbol, Ticker, ISIN, CUSIP or similar unique id. This is used to determine if two holdings are of the same asset. If this field is excluded we will lookup related assets based on the asset name field.
10. Short Name: A shorter version of the asset name for display purposes. This is used for display in several places in the application and especially relevant if the asset name is long. This can be particularly useful for things like bonds, options and other types of assets the include details in the name of the asset.
11. Type*: The transaction type. This must be selected from the dropdown pre-populated with transaction types that we use in the system. The transaction type is very important because it determines if the transaction will impact the market value, cost basis, and metrics.
12. Last 4: The last for digits of the account number (or similar). This is primarily for display. It is useful to display the last 4 digits of the account number or some other unique identifier to help distinguish between similarly named accounts.
    
    

Uploading the Spreadsheet in the Admin Portal (Back Office)

1. Importer: Currently, the only way to upload the spreadsheet is via the back office. There is a tab that says Importer.
2. Spreadsheet Importer: This is the tab where you can drag files into to upload spreadsheets
3. Spreadsheet Type: In the Spreadsheet Importer tab, you will need to choose the type of spreadsheet import you are importing. Please note that you can only import one type at a time, and the available options are Assets, Transactions, and Accounts.
4. Drag File Here: Drag the File into this box and you will get a screen as shown below
5. Import History: In the Import History tab, you can access a comprehensive view of all imports performed for a specific office. It provides descriptions of each import and gives you the option to undo any import if needed. This feature is displayed as shown below.

Spreadsheet Import Screen:

Once you have dragged a file into the Drag File Here screen, a new screen will appear. On this screen, you can easily match the fields in your excel workbook with the corresponding fields in Copia. In most cases, the fields will automatically fill in.

After clicking next on the previous screen you will get to a review screen where upon review you must click the Submit button in the top right hand corner. In this example, there are no errors; however, errors will be highlighted in red.

Import History Screen:

The Import History Tab provides a comprehensive view of all imports performed for a specific office. Additionally, you have the ability to undo any import by clicking on the three dots, which has an option for removing all data associated with that particular import.