Formatting and Importing Text (.csv) Files
Overview
Optuma provides global end-of-day data, as well as the ability to import data from US Federal Reserve FRED database, but if you can’t find what you require or have your own data then it can be imported in to Optuma when saved in a .csv format.
Note
There must be a file for each dataset in a time series, so if you have 5 datasets then you will need 5 separate files, and Optuma will use the name of the file as the symbol code and name of the chart.
For an example, visit this forum post.
Formatting the .csv File
When importing a .csv file into Optuma it is important to ensure all rows of data are uniformly formatted and the file must be sorted in ascending order, i.e. earliest first.
The following format settings are recommended, especially for the date:
- Date: dd/mm/yyyy or mm/dd/yyyy. When opened in Notepad the leading zeroes should display eg 06/04/1980 and not 6/4/1980.
- Time: hh:mm:ss
- O/H/L/C: Depending on the code they should all be set to between 0 and 5 decimal places. The important thing is that all 4 columns are set to use the same decimal settings.
- Volume: 0 Decimal Settings
- OI: 0 Decimal Settings
Note
Not all these columns are required. Obviously, a date column would be required, but it’s possible just to include one price column (eg, the close) if necessary.
Once the .csv file has been formatted they should look like the following, depending on the number of columns in your file:
Note
Once you have formatted and saved the .csv file, if you re-open the file in Excel some of the formatting changes you’ve made will be reset, and you will need to redo them. If you need to open the .csv file after setting up the formatting it is recommended you use Wordpad or Notepad.
Importing the .csv File
Note: Be sure to save the .csv files in a unique folder that only contains the .csv files, eg Documents/Optuma/CSV Files. All .csv files in that folder must have the same format and same columns. If you are importing multiple files with different formats then they will have to be saved in separate folders.
-
Open Optuma and left-click on the Data menu.
-
In the menu displayed, left-click the Import Local Data option to open this window:
-
By default, the C:\ drive will be selected. This will search all subfolders for compatible data. If you would prefer to select a specific folder, such as Documents/Optuma/CSV Files, you can left-click the Change button.
-
Select the time frame the data is stored in, EOD or Intraday.
-
Set the format of the Data files to Comma delimited (*.csv)
-
Enter a name and short name you would like the Exchange to display as in the Data File Manager.
-
Click the Import button.
-
Optuma will scan your system for compatible data. Once completed your data will be ready to access.
Opening a Chart from a .csv File for the First Time
The first time you open a chart using a .csv data file, you will be prompted to enter the format settings:
Click the Next button and you will be taken to a page where the format settings can be entered:
The following options are available:
- Delimiter: Comma, Tab, or Space. (For a .csv file Comma is used).
- Currency: Select the currency of the data used in the .csv file (AUD, for example).
- Date Format: Type the same format in this box as it appears in the Data in File example, as outlined in the image above.
- Time Format: If using the recommended settings listed above, set to hh:mm:ss
-
Field Order: This section allows you to set the order of the columns as they are set out in the .csv file. A list of compatible column options is listed in the Allowed Fields section below Field Order. Some changes may be required to use the .csv file in Optuma. As an example, if the data is for an EOD time frame only, and no Time column is included, you can remove the Time item from the Field Order section.
- Data in File: Shows you a sample row from the .csv file for you to reference against as you setup the formatting options.
Once the settings have been adjusted to use the same formatting options as the .csv file, left-click the Finish button. A chart will then display using the data contained within the .csv file.
Reimporting Data
If the imported data isn’t showing correctly check the formatting is consistent in the source file by opening in Notepad (not Excel).
Delete the MTAPS.dat file created in the folder where the .csv files have been saved (this is the formatting file created when the file is first opened). Open the chart again, and you will be prompted to confirm the format again, and a new MTAPS.dat file will be created.
Removing Imported Data
To remove an imported exchange right-click on the name in the Security Selection window and click on Remove Custom Exchange:
