Skip to main content

Uploading Prices in Bulk

This guide explains how to upload prices series in bulk using a CSV (Comma-Separated Values) file.

DECAF allows adding, modifying and deleting market price observations one-by-one. However, users may find themselves in a situation whereby they need to do so for many price observations.

In such cases, users may wish to prepare a CSV file using a spreadsheet editor (such as Microsoft Excel, Libreoffice or OpenOffice), and update price observations at once.

Steps are:

  1. Prepare the CSV file with required columns and value format.
  2. Upload the CSV file to DECAF using Cafelatte.

Preparing the CSV File​

Users can prepare a CSV file using a plain text editor (such as Notepad), or more comfortably, using a spreadsheet editor (such as Microsoft Excel, Libreoffice or OpenOffice).

Required columns are:

  1. date The date of the price observation. The format must be exactly YYYY-MM-DD as in 2020-12-31. Other formats are not accepted.
  2. symbol The symbol of the instrument. This must be an exact match: There shouldn't be any extra spaces, all characters must follow the casing of the original instrument symbol. This means, if the instrument symbol is AAPL US Equity, then the value of this column must be exactly AAPL US Equity, but not AAPL us equity.
  3. close The closing price of the observation. This value must be in the form of a number with no more decimal digits than 12.

Note that column headers are also case-sensitive. For example, user must ensure that the column header reads symbol, not Symbol.

A screenshot from Microsoft Excel is as follows:

You can download the Sample Microsoft Excel file to start with.

Once you are finished with preparing the data, then you need the save the file as a CSV (Comma-Separated Values) file. Requirements are:

  1. The date format should be preserved.
  2. The delimiter should be , (comma).
  3. The file should not contain BOM character (this is very unlikely if you are using a recent version of Microsoft Excel).

As a test, you can try to open the file using a plain text editor, such as Notepad, and see a format as follows:

"date","symbol","close"
2020-12-31,"ABC US Equity",100.22
2020-12-31,"XYZ US Equity",99.21
2021-01-04,"ABC US Equity",100.29
2021-01-04,"XYZ US Equity",98.97

Uploading the CSV File​

First, go to Extras -> Prices pages from the main navigation bar. On the upper right corner of the panel, you will see a button with title Bulk Upload. Click on this button.

If you want the system to skip invalid rows, make sure that the "Skip Invalid Records" checkbox is checked. Otherwise, uncheck the checkbox.

Click the "Choose one or more files to upload" button and choose one or more files you want to upload to the system from your computers local storage.

Once you have closed the dialog box, the system will automatically start importing the data.

Once finished, you will see a summary in the table showing the file size, status of the import operation, number of series involved, number of records created or modified, etc.

Notes​

There is theoretically no limit to the number of observations uploaded. However, we advise you to not to exceed 50,000 records per file. You can still create multiple files and upload at once if you have more observations.

Resources​

Download sample Microsoft Excel file to start with

Download sample CSV file exported from Microsoft Excel File