9. Synchronizing positions in BFS using excel
- Stefan Willebrand
Problem
If you are using BFS to report and keep track of your fund holdings and your fund holds a number of positions which updates frequently you will have to do a lot of manual work each day when you synchronize your holdings. You have to enter the trades and the money transfers manually so that BFS reflects what is going on in the “real world”.
Solution
A solution for this is a small application that we call PositionSynchronizer. This is a console application that runs locally on your machine and collects csv files (exported from Excel) for the instrument positions and one csv file for cash positions. This means that you only have to keep two excel files for each fund, one for positions and one for cash. In the Excel file you keep one row per position with some additional details which enables the PositionSynchronizer to update or create missing instruments and update current prices etc. When you change or remove a position from the file BFS will be synchronized and create transactions in the system to make the BFS positions come inline.
Documentation
When you first run the application it will create a new folder on your C-drive named positionsynchronizer and then it will show you a message that it cannot find any files in there. Simply close the application and add your files to the folder. The documentation below describes the different files and how to work with them.
Positions
This is the file that holds the positions in financial instruments for a certain account. Each file holds positions for a single fund identified by an account number in BFS and you can only have one file called positions.csv in the folder at a time so if you want to update positions in BFS for multiple funds you simply have to run the application several times and make sure the right csv-files are in the folder.
Let's look at the contents from the file, you can download an example file below.
Description of rows and columns that matter:
Row 1
Column | Description |
|---|---|
A | This is a text that tells you that the content of the next column should be the account number in BFS which the positions relate to. |
B | Here you should enter the BFS account number. |
C | This is a text that tells you that the content of the next column should be the balance date that applies to the positions in the file. |
D | Here you should enter the date in the local format of your operating system that represents the date of the positions in the file. |
On row 2 the headers for each data point are described and the table below describes the content that should be entered in each. The title of each field does not matter but only the position (which column) the content is written.
Title | Description |
|---|---|
Feed | This is a free text string where you can describe from which datafeed you have received the price quote. This is not read into BFS |
Symbol | This is a free text string where you can enter a unique symbol for the financial instrument. |
Description | This is a free text string that describes the name of the financial instrument. |
ISIN | This is a free text string that should contain 12 characters that represents the ISIN code of the financial instrument. |
Country | This is the country of where the financial instrument is issued. This is not read into BFS. |
CCY | This is the ISO currency code for the financial instrument. |
LastPrice | This is a digit field in decimal format that represents the price of the financial instrument that you would like to use in the currency of the instrument. |
LastPrice (SEK) | This is a digit field in decimal format that represents the price of the financial instrument in another currency of your choice, that is not read into BFS and is for your use only. |
Quantity | This is a digit field in decimal format that represents how many units of the financial instrument that the position consists of. |
Value (SEK) | This is a digit field in decimal format that represents the value of the position in any currency that you would want to see it in, this is not read into BFS but for your use only. |
MarketplaceName | This is the name of the default marketplace where the financial instrument is traded. This is used for creating the execution interface of the instrument in BFS which currently has to be done manually once for the BFS system until the API function for creating a marketplace is finalized. To configure the marketplaces in BFS you navigate to System Settings and Places: 
|
MIC | This is the marketplace code and is used to look up the right marketplace for the instrument in BFS when it is created so this must be the same MIC as you have entered for the Marketplace in BFS when you created it. |
ExchangeCode | This a free text string that should contain the shortcode for the exchange where the instrument is traded. |
TaxCountry | This is the ISO code for the country where the financial instrument is issued. |
PriceDecimals | This is an integer digit value that represents how many decimals should be used for the price of the instrument. |
PriceDate | This is a date value that represents which date the LastPrice was captured. |
Multiplier | This is a decimal digit value that represents the multiplier of the financial instrument which is described here: Stocks and here: Bonds |
QuantityDecimals | This is an integer digit value that represents how many decimals should be used for each unit of the instrument, for example if the number of shares should always be traded in full units this value should be 0. |
CfiCode | This is a free text string that represents Classification of Financial Instruments (CFI Code): ISO 10962 with the instruction found here: http://www.tfiic.org/SiteContent/TH/Info/CFI%20Code_EN.pdf |
InstrumentType | This is an integer digit value that represents the instrument type in BFS as described here: Instrument |
CustodyAccountId | This is a free text field that should contain the so called GUID or id of the custody account in BFS that is used for the financial instrument since this is entered on the execution interface that is created automatically for the instrument. This is an account of the type Custody Account that you have created for the House and that should mirror a real account that you have at your custodian. To get the id of the account simply navigate to the house view and select the info icon and then in System Information you find the id. 
|
Cashpositions
The file called cashpositions holds values for the cash that should be represented in the BFS account. The cashpositions file could hold the positions for multiple accounts so there is no need to have more than one cashpositions file in Excel, you can enter all your cash positions there.
Row 1
Column | Description |
|---|---|
A | This is a text that tells you that the content of the next column should be the balance date that applies to the positions in the file. |
B | Here you should enter the date in the local format of your operating system that represents the date of the positions in the file. |
On row 2 the headers for each data point are described and the table below describes the content that should be entered in each. The title of each field does not matter but only the position (which column) the content is written.
Title | Description |
|---|---|
BfsAccountNumber | This is a free text string which represents the account number in BFS that should reflect the position. |
CurrencyCode | This is the ISO code of the currency that should apply to the cash position. |
Balance | This is a decimal value representing how much the cash position should be in. |
Export the csv-files
For the PositionSynchronizer to read the files they need to be exported from Excel in csv format. This is done by simply selecting Save As in Excel and then the CSV format in the dropdown below the file name.
Before doing this it is very important that the field separator in Windows is set to semicolon and that is done by navigating to the control panel, then Region and then additional settings:
Where it says “List separator” at point 3 it is very important that “;” is selected.
With the csv-files created place them into the folder “positionsynchronizer” that was created by the application.
Now run the application and it should perform all the steps automatically.
© 2009 - 2023 Huddlestock Technologies AB All rights reserved | Huddlestock Terms of use | Support portal