Using Spreadsheets For Test Data With veriFIX
During my time testing trading software using veriFIX I have devised ways to inject instrument codes or ISINs into tests. My favourite being an excel spreadsheet and a database driver.
Whilst available ISINs can be gleaned from SecurityList messages and MarketData, I prefer during my testing to keep a record of all available instruments in the system. Conversely I like to know about all data in my test environments.
For this example I will show how to set up veriFIX to read ISINs from a spreadsheet. (This can be extended to include prices, order types and much much more).
For this you will need:
veriFIX
Microsoft Excel
CSV JDBC Driver
1- Download the CSV JDBC driver jar from here https://sourceforge.net/projects/csvjdbc/
2- Place the CSV JDBC driver jar into the lib folder in your veriFIX program folder
3- Add location of CSV JDBC driver to class path e.g C:\Itiviti\VeriFIX 6.5\lib\csvjdbc-1.0-31.jar; (instructions on how to do this can be found on the internet)
4- Create a folder on your C drive called TestData
5- Create a excel csv file in the TestData folder, with a colum header TestNum and a column header SecurityID. Populate the TestNum column with incrementing numbers and the SecurityID column with ISINS that exist in your system.
7- Click 'Try Connection' - you should get a positive result. If the connection fails check file names and locations
8- In veriFIX create System Variables SecurityID and TestNum. For the value in TestNum put 1, and for the SecurityID value enter a ISIN from your test system data
9- Create a scenario in veriFIX and call it 'InitialiseData'
10- Under the scenario Advanced tab click SQL to open a query builder
11- Populate the tab as follows:
12- In the scenario home tabclick 'Set Variable'
13- Populate the Set Variable Command as follows:
14- Save this scenario and create a new one called Send Order
15- In the Send Order scenario click 'Sub Scenario'
16- In this tab select the InitialiseData scenario you just created to add it to the Send Order scenario
17- Click 'Send' in the Send Order scenario
18- From the list select the message type your system uses for sending orders (New Order Single, IOI etc) and add it to the Send Order scenario
19- Double click the send message you have added to the Send Order scenario
20- Clik the Custom Override tab and Add Row
21- Populate the row as follows:
22- Save the Send Order scenario and run
What is this doing?:
1- Each time you run the scenario the CSV JDBC driver will use the value in the system variables TestNum to access the row of the csv file which corresponds to the TestNum
2- The SecurityID value in the csv file is copied from the csv file to the system variable SecurityID
3- The send message in the Send Order scenario takes the SecurityID value from system variables and places it in tag 48 of the outgoing message
4- The TestNum in system variables is incremented so next time the scenario is run the next row in the csv file is accessed
Whilst available ISINs can be gleaned from SecurityList messages and MarketData, I prefer during my testing to keep a record of all available instruments in the system. Conversely I like to know about all data in my test environments.
For this example I will show how to set up veriFIX to read ISINs from a spreadsheet. (This can be extended to include prices, order types and much much more).
For this you will need:
veriFIX
Microsoft Excel
CSV JDBC Driver
1- Download the CSV JDBC driver jar from here https://sourceforge.net/projects/csvjdbc/
2- Place the CSV JDBC driver jar into the lib folder in your veriFIX program folder
3- Add location of CSV JDBC driver to class path e.g C:\Itiviti\VeriFIX 6.5\lib\csvjdbc-1.0-31.jar; (instructions on how to do this can be found on the internet)
4- Create a folder on your C drive called TestData
5- Create a excel csv file in the TestData folder, with a colum header TestNum and a column header SecurityID. Populate the TestNum column with incrementing numbers and the SecurityID column with ISINS that exist in your system.
6- In veriFIX under the Database tab tell veriFIX what driver to use and the location of the file the driver will interact with
7- Click 'Try Connection' - you should get a positive result. If the connection fails check file names and locations
8- In veriFIX create System Variables SecurityID and TestNum. For the value in TestNum put 1, and for the SecurityID value enter a ISIN from your test system data
9- Create a scenario in veriFIX and call it 'InitialiseData'
10- Under the scenario Advanced tab click SQL to open a query builder
11- Populate the tab as follows:
12- In the scenario home tabclick 'Set Variable'
13- Populate the Set Variable Command as follows:
14- Save this scenario and create a new one called Send Order
15- In the Send Order scenario click 'Sub Scenario'
16- In this tab select the InitialiseData scenario you just created to add it to the Send Order scenario
17- Click 'Send' in the Send Order scenario
18- From the list select the message type your system uses for sending orders (New Order Single, IOI etc) and add it to the Send Order scenario
19- Double click the send message you have added to the Send Order scenario
20- Clik the Custom Override tab and Add Row
21- Populate the row as follows:
22- Save the Send Order scenario and run
What is this doing?:
1- Each time you run the scenario the CSV JDBC driver will use the value in the system variables TestNum to access the row of the csv file which corresponds to the TestNum
2- The SecurityID value in the csv file is copied from the csv file to the system variable SecurityID
3- The send message in the Send Order scenario takes the SecurityID value from system variables and places it in tag 48 of the outgoing message
4- The TestNum in system variables is incremented so next time the scenario is run the next row in the csv file is accessed
Comments
Post a Comment