/
Quarterly Capital Accounts Updates (02.24)

Quarterly Capital Accounts Updates (02.24)

Owned by Martin Zahner
Last updated: May 14, 2024
6 min read

Updating fund data

In the Data Management menu navigate to Fund Data Input.

 

Navigation Tabs

 

 

  • Bulk Lock/Unlock: used to make the database writeable for quarters which will be updated and to lock again after updates are complete

  • Infidem Input Sheet: used to update fund data where the investment is made via single investment vehicle in which the investors are participants or where only a single investor or GmbH has a holding in the fund (i.e. fund manager issues single statement per quarter)

  • Direct/GmbH Input: used to update fund data where multiple investments are made directly per investor or via GmbH per investor (i.e. fund manager issues separate statement per investor per quarter). Note: In this case it is essential that the same investor/GmbH be used for all funds!

  • SMA Input: as per single investor input but limited to sub-funds of separately managed accounts

  • Values by Investor: Read only display of quarterly statements per investor after the pro-rata matrix multiplication of shareholding percentage to input data

  • Daily Input: Provides the option to input contributions and distributions only on a daily basis.

  • Daily Input by Investor: Read only display of daily contributions/distributions per investor (pro-rata per shareholding percentage)

  • Projections and splits: Ability to update algorithm used for cashflow projections and regional and industry sector splits for funds without asset level data. Also the ability to switch on and off asset level reporting per fund

Bulk Lock/ Unlock

 

This form allows an entire year or individual quarter to be locked or unlocked for either the entire portfolio or groupings of funds (e.g. single holding, multiple holding).

before attempting to load data the fund/quarter combination must be unlocked. For an individual fund input this can be done on the form itself by clicking the “”Unlock” field. This action can also be performed in bulk for multiple funds and periods via the “Bulk Lock/Unlock” screen. The convention in the Apliqo LPPM web application is to display writable fields with a grey background. Read only fields have a white background. Therefore it is easy to see as a user if you have write access to a field.

Fund Data Input

  1. The “Fund data input” screen allows to

    1. enter manually or

    2. using the Excel upload

    3. API Solutions are also possible, currently supported is Canoe

  2. Fund Quarter needs to be added. image-20240408-094809.png

  3. For all funds in the new quarter the NAV can be rolled forward image-20240408-094843.png

  4. The funds need to be unlocked before importing

  5. The Values by Investor Data can be feed

    1. for a individual fund

    2. for all funds

  6. Update Investor Data image-20240408-094734.png
    For Direct/GmbH Input: As the input is done on with investment data for the “template investor”, it is necessary to manually initiate the “update data” calculation for the holdings of the other investors.

 

The fund data input is very similar for the different fund categories:

  • Infidem input

  • Direct/GmbH input
    The only difference to Infidem input is that from a database perspective the data is input to a “template investor” and then transformed via matrix multiplication of the shareholding table into individual investor statements for the remaining investors. (see below for instructions)

  • SMA input
    The same “template investor” should be used as was used for the “parent” fund.
    For SMA inputs you may also need to ensure that the SMA “sub-fund” fund ID has been set up in the database as records will not be able to be loaded to any fund ID which the database does not recognize.

Input for a single fund

Updating a single fund can be easily done via copy/paste from Excel into the web form.

 

  1. Check if investment is unlocked for reporting quarter

  2. Turn off zero suppression (to unhide the quarters which don’t yet have data)

  3. Add additional quarters (if necessary)

  4. Copy/Paste from Excel into input sheet (column order should match Excel template)

 

 

Upload from Excel is also possible for an individual quarter or multiple quarters. Requires a specially formatted excel file.

 

Single fund upload template

Input for multiple funds

Using the Upload from Excel facility it is however possible to upload data for multiple funds all at once using the Multiple fund upload form. This would allow for all funds with single holding to be updated in one action for a single quarter or multiple quarters. (Upload template attached below).

 

Upload from Excel is also possible for an individual quarter or multiple quarters. Requires a specially formatted excel file.

 

Multi fund upload template

 

 

For upload via Excel template the required format of the Excel files is identical for “Infidem Input”, “Direct/GmbH input” and “SMA input” All that is required is to ensure the correct fund name is used in the fund ID column.

Special Case: “Template Investor” for Direct/GmbH input

 

 

Check the successful execution with “values by investor” report

Values by investor

The “Values by Investor” screen is read only and does not accept data input. This screen is provided to check data input and displays the final values which have been committed to the database for use in all following portfolio calculations, reports and projection calculations. The format in terms of the column layout is identical to the previous input sheets and also matches the Excel format used and should therefore be easy to reconcile.

On this screen you can review statements per investor per quarter for any fund (including SMA investments) regardless of the investment vehicle type.

Projections & Splits

The “Projection and Splits” screen is used to monitor and adjust the assumptions for cashflow projections and regional and sector splits.