Uploading Accounts
- Brent Bergh
- Oleg Barshay
To load a batch of Accounts into PropReports, a comma-separated values (CSV) file can be used. Please follow RFC 418 guidelines when creating CSV files. Notably, if you will have field values with commas, the field value must be in quotes.
Click here for an Accounts file CSV Template.
Sample Data
If creating by hand, you can use a text editor or Excel (must save as csv before uploading). For PropReports to recognize the file format, the column heading names must be exact.
Account,Display Name,Clearing ID ACCOUNT100,John Smith,XYZ0001 ACCOUNT200,John Doe,ABC0001
Fields
Name | Mandatory? | Format | Sample | Available in Version | Description |
|---|---|---|---|---|---|
Account | Yes | CHAR(50) |
| All | Name of the account (PropReports Account Id). Anything after the first 30 characters will be ignored. |
| Columns below are optional and can be in any order. However, at least one additional column must be present in order for the file to be recognized. | |||||
| Action | No | CHAR(1) | U | All | If an account is to be created, this value should be a C. To update an existing account, specify a U. If omitted, an update (U) is assumed and an error issued if the account does not exist. |
| Renamed Account | No | CHAR(50) | ABC1234567 | All | When updating an account, this column can be used to change the Account Id. Note that another account with this name must not already exist. |
Display Name | No | CHAR(50) |
| All | The account name you and the traders will see in reports (see Account Names). |
Clearing ID | No | CHAR(50) |
| All | The Clearing Id is the Account Id at your clearing firm (see Account Names ). |
| Clearing Firm Name | No | CHAR(96) | Wedbush | All | The name of the clearing firm where this account clears. If the column is not present, the clearing firm defaults to the configuration of the Generic parser. |
| Aggregation Unit | No | CHAR(50) | ABC123 | All | The Aggregation Unit associated with the account. |
| Currency | No | CHAR(3) | USD | All | Three-character ISO 4217 code for the base currency of the account. If unspecified, PropReports will default to USD. Note: at the moment only setting the currency on a new account is supported. |
| Status | No | CHAR(1) | A | All | Status of the account. Should be one of: U - Unspecified, A - Active, S - Suspended, D - Deactivated. |
| Activation Date | No | DATE | 2021-11-15 | All | If account status is Active, this field can be used to specify when the account was activated. |
| Suspension Date | No | DATE | 2021-11-15 | All | If account status is Suspended, this field can be used to specify when the account was suspended. |
| Deactivation Date | No | DATE | 2021-11-15 | All | If account status is Deactivated, this field can be used to specify when the account was deactivated. |
| Close Date | No | DATE | 2021-12-31 | All | Set the date the account is closed. Note that if this column is present and a value is not set, it will clear the account close date (if any). |
| Source | No | CHAR(32) | Sterling | All | Source of the data being uploaded (see list of source names). If the column is not present for a new account, the source defaults to Generic). |
| Note | No | CHAR(65535) | My Note | All | This will cause a new note to be associated with the account. Multiple lines can be specified following the CSV file format rules (field must start with a " and end in a "). |
| Add Users | No |
| All | This grants the user permission to view the account. Either a User ID or email address can be specified. To specify multiple users, separate values by a pipe (|). | |
| Remove Users | No |
| All | This revokes the user's permission to view the account. The field can contain either a User ID or the email. To specify multiple users, separate values by a pipe (|). Note: if a user is a manager, they may still be able to view the account if they have permission to view any group containing the account. | |
| Add Groups | No | Traders A | All | To add an account to a group, specify the group name. The name must match an existing group. If a group has a category, please use the format "Category Name: Group Name". To add to multiple groups, separate values by a pipe (|). | |
| Remove Groups | No | Traders A | All | To remove an account from a group, specify the group name. The group name must match an existing group. If a group has a category, please use the format "Category Name: Group Name". To remove from multiple groups,separate values by a pipe (|). | |
| Plan Types | No | T | All | Plan type to assign or clear. Valid values are T for trade fees, R for recurring fees and P for payouts (1.12.32+). For multiple entries, separate values by a pipe (|). See below for an example of how to assign a plan. | |
| Plan Categories | No | Commission | All | Name of the plan category being assigned or cleared. When specifying plans, this field is mandatory. Multiple entries should be separated by a semicolon (;). | |
| Plan Names | No | 0.003/shr | All | Exact name of the plan being assigned. If plans are being cleared, then the plan name must be blank. Multiple entries should be separated by a pipe (|). | |
| Plan Start Dates | No | 2022-01-01 | All | The first effective date of the plan being assigned or period being cleared of plans. Can be blank to specify from date of first account activity. Multiple entries should be separated by a pipe (|). | |
| Plan End Dates | No | 2022-01-15 | All | The last effective date of the plan being assigned or period being cleared of plans. Can be blank to specify in perpetuity. Multiple entries should be separated by a pipe (|). | |
| Plan Offset Accounts | No | ACCOUNT1 | All | Optional offset account for a plan assignment. Multiple entries should be separated by a pipe (|). | |
Examples of Assigning and Clearing Plans
NOTE: Use of pipes (|)
- The pipe symbol is used to separate plans when assigning multiple plans as seen in Example 2 below
- In the unlikely event your plan name contains a pipe symbol, you will need to use double double quotes around that plan name in your file. CSV standard removes the first double quote from being seen, hence the second pair of double quotes needed. Please see examples below for plan name : $4.95 tkt| 0.005/shr > 3000 shares
Example 1: Assigning one plan per entry (note how the second entry specifies a plan name with a pipe character).
Account,Plan Type,Plan Category,Plan Name,Plan Start Date,Plan End Date,Plan Offset Account ACCOUNT1,T,Commission,$0.003/shr,7/1/2021,10/31/2021,*FIRM_COMMS ACCOUNT1,T,Commission,""$4.95 tkt| 0.005/shr > 3000 shares"",11/1/2021,,*FIRM_COMMS ACCOUNT1,R,Fee: Daily Interest,10% on Cost,6/1/2021,,*FIRM_INTEREST
Example 2: Assigning those three plans in one line.
Account,Plan Type,Plan Category,Plan Name,Plan Start Date,Plan End Date,Plan Offset Account ACCOUNT2,T|T|R,Commission|Commission|Fee: Daily Interest,""$0.003/shr"|"$4.95 tkt| 0.005/shr > 3000 shares"|"10% on Cost"",7/1/2021|11/1/2021|6/1/2021,10/31/2021||,*FIRM_COMMS|*FIRM_COMMS|*FIRM_INTEREST
Example 3: Clear all commission plans for account ACCOUNT1 from Jul 1, 2021.
Account,Plan Type,Plan Category,Plan Name,Plan Start Date,Plan End Date ACCOUNT1,T,Commission,,7/1/2021,
Enabling the Upload
Before uploading for the first time, you must to make sure the Generic data source is enabled.
Step 1: Navigate to the Upload / Sources page.
Step 2: If the Generic source is in the Disabled Sources list, click Enable next to it to update the configuration.
Uploading the File
To load the file into PropReports, please follow these instructions. Load the file as an "Account file".