KNOWLEDGEBASE

Articles in this section

Importing Cost Values

An integration with the Import Data Type Cost Values (Time Phased) loads the monthly Budget, Forecast and Actual values of costs that already exist, from an Excel spreadsheet. Each row is one value: the cost, the series, the month, and the amount. The integration is set up from the initiative's Tools > Integrations, by clicking New Integration, leaving the Adapter as Excel, and selecting Cost Values (Time Phased) as the Import Data Type. The values appear on each cost's page and in the Cost Ledger.

This article covers both the Advanced and Simple Models. It needs the Integrations permission on the initiative, as set out in Before you start. The import never creates a cost. To create costs, see Importing Costs.

This is different from Administration > Integrations, which connects third-party applications. See Connecting a Third-Party Application.

Before you start

Who can import

Importing needs both of the following:

  • Enable Integrations turned on for the initiative. On the Advanced Model, it is a setting on the initiative's classification, under General Configuration. See Configuring Initiative Classifications (Advanced Model). On the Simple Model, it is under the program's Configuration > Features. See Program Features.
  • The Integrations permission set to Full on a team role held on the initiative. See Team Roles. A Full Admin, or a user whose admin role sets Hierarchy to Full, can import without a team role. See Admin Roles.

What must exist first

The import writes values onto costs. Set up the following before importing:

Where to set up the integration

Set the integration up on the highest initiative that holds every cost in the file. A cost on an initiative outside that initiative's hierarchy is skipped. On the Simple Model, set it up on the program.

File limits

  • The file is an Excel file (.xlsx) of 5 MB or less.
  • The sheet has up to 2,000 rows. Because each value is a row, one cost with 12 months of Actual and 12 months of Forecast takes 24 rows. A sheet with more than 2,000 rows can't be run, so split it into several sheets or files.
  • Only one sheet is read each time the integration runs. The sheet is chosen when the file is uploaded.

Preparing the spreadsheet

Use one row for each value, not one column for each month. A cost with 12 months of Actual values takes 12 rows, with the same cost and series on each. A row for the Actual of one cost in March 2027, for example, holds the cost's External ID, Actual, the date 1 March 2027, and the amount. The columns can be in any order, but each integration remembers the mapping by column position, so keep the order the same from one run to the next.

Required columns

Every row needs all of the following:

  • Cost — the cost's ID or External ID. The cost's name and cost category aren't needed.
  • Series — Budget, Forecast or Actual. Capital letters don't matter. Where the series names are translated in your instance, the translated names are accepted.
  • Start Date — a date in the month the value is for. The date is moved to the first day of its month.
  • Value — a number. A blank cell is an error, so enter 0 for no spend.

Rules for the values

  • Values are monthly. There are no quarterly or yearly values.
  • The month must fall within the cost's Start Date and End Date. A row outside them is skipped.
  • Use one row for each cost, series and month.

Months missing from the file

The file doesn't need a value for every month. Amplify fills the months from the cost's start date to the last month in the file for that series with 0 where the cost has no value yet. Values the cost already has are kept, and months after the last month in the file aren't changed. A month that is in the file replaces the cost's existing value for that month.

Creating the integration

To create the integration:

  1. Open the initiative the integration is set up on.
  2. Navigate to Tools > Integrations. The initiative's integrations are listed.
  3. Click New Integration.
  4. Enter a Name for the integration.
  5. Select Cost Values (Time Phased) as the Import Data Type.
  6. Click Create.

The Adapter is always Excel.

Uploading the spreadsheet

To upload the spreadsheet:

  1. On the initiative's integrations, click Run... next to the integration. A window opens.
  2. Where the integration has been run before, select Upload New to choose a file, or Use Previous to use the last file again.
  3. Choose the file.
  4. Select Header? where the first row holds column headings.
  5. Select the Sheet that holds the values.
  6. Click Continue. The session page opens, with the rows from the sheet.

The session page runs a preview straight away. Map the columns as described in Mapping the columns, then click Preview again to check the new mapping.

Mapping the columns

The columns are mapped on the session page that opens once the spreadsheet is uploaded. To map the columns:

  1. Click Edit Mapping. A header appears above each column.
  2. Select the Type for a column.
  3. Set the fields that appear for that type.
  4. Repeat for each column that is imported.

The mapping saves as you go. It is kept on the integration and applies by column position the next time the integration runs. A column left as No Action is ignored.

Mapping types

  • Cost — matches the cost the value belongs to. Map one column only.
  • Attribute Assignment — puts the value into one field of the series point, chosen from the Attribute list: Series, Start Date or Value. Map one column to each.
  • No Action — leaves the column out of the import, such as a column of cost names kept for reference.

The mapping needs a Cost column and one column each for Series, Start Date and Value. Until it has all four, the mapping shows as invalid, a message says the minimum requirement hasn't been met, and Preview and Import... are disabled.

Match on Attribute

For the Cost column, Match on Attribute sets which field of the cost the cell is compared with: ID or External ID. Capital letters and spaces around an External ID are ignored. There is no fallback. A row whose cost isn't found is skipped.

Previewing and importing

Previewing the import

The preview checks every row against Amplify and saves nothing. A red icon on a cell marks a problem. Hover over it to read the message. An amber icon marks a warning. The preview shows every row as Create, including a row that will replace a value the cost already has.

A row is skipped where:

  • the cost isn't found, or is on an initiative outside the integration's hierarchy.
  • the series isn't Budget, Forecast or Actual, or isn't switched on for the cost's cost category.
  • the month is outside the cost's Start Date and End Date, or the date can't be read.
  • the value isn't a number.
  • the series is Budget and the budget is locked. The row shows an amber warning that Budget values have been locked. The budget is locked where the cost category has Prevent modifications to the Budget on, or where a stage locks it.

Where rows fail, a banner says how many rows were unsuccessful. Click View these rows only. to show just those rows, and Show all rows. to return to the full list. Fix the file or the mapping, then preview again.

Import... is disabled while a preview, import or mapping change is running, and until the mapping is complete. Rows with errors don't disable it.

Importing the values

To import the values:

  1. Click Import.... A confirmation opens.
  2. Click Confirm.
  3. Wait for the banner to report that the import has been completed.
  4. Click End Session. The integrations list opens.

Rows with errors aren't imported, but every other row is. To import the failed rows, fix them and import a file that holds only those rows. Re-importing rows that already imported does no harm: it writes the same values again.

Until the session is ended, the integration shows Terminate in place of Run... and can't be run again. If a session was left in progress, click Terminate on the integrations list to end it.

To check the result, open a cost and view its Plan tab, or switch the Costs Register to its ledger view. See Managing Costs.

Updating values later

The same integration can be run again with a new file. There is no separate update setting, because every row writes the value for its cost, series and month. A row for a month the cost already has replaces that month's value. A row for a new month adds it.

An import can't clear a value. To correct a value, import the right amount over it, or enter 0.

Things to know

Replacing Forecast and Budget with Actual

A cost category can replace Forecast or Budget with Actual values. These are the Replace Forecast with Actual Values and Replace Budget with Actual Values settings, under Replace Series Behaviour on the cost category. See Configuring Cost Categories (Advanced Model).

Where a setting is on and the series is switched on, importing Actual values also replaces the Forecast, or the Budget, for the same months with the Actual values. This applies whether or not the file has Forecast or Budget rows for the cost. A Forecast value in the file for a month that has an Actual is replaced by the Actual.

Locks and approvals

An import writes values that are locked elsewhere in Amplify. It doesn't apply the Lock Past Actuals site setting, or Prevent modifications to the Actuals and Prevent modifications to the Forecast on the cost category, so values in a locked period are overwritten. Imported Actual values are also saved straight away, without the approval workflow for actuals. Check the file covers only the periods you mean to change.

An import can't be undone. Preview every file first, and try a few rows before importing a large file. To correct values afterwards, import the right values over them or edit them in the Cost Ledger.

For an overview of imports, including importing other types of data, see Importing Data.

Related articles