HOW TO IMPORT TRADES FROM INTERACTIVE BROKERS WEBSITE
And set up the spreadsheet
1. Log into you Interactive Brokers account
2. Go to Performance & Report
3. Click Flex Queries
4. Click the + button to create new Activity Flex Query
5. Name It
6. Scroll lower and click on Trades
7. Select all and confirm at the bottom of page
8. Select format as csv
9. SET PERIOD TO LAST 365 DAYS
10. Change date format to dd/mm/yyyy
11. check if the values are the same and confirm at the bottom of page
12. Click create at the bottom of next page and you will get a message that query has been created
13. Click the small arrow next to created query to run it
You can run this query every time you want to export data. No need to create a new one.
14. Chose your period (you can import one year of data in-between dates) and set format to csv
Make sure the dates exported don’t overlap. For example if you first time exported dates for up to 22nd of February 2021, then next export must start from 23rd of February.
15. Now you need to wait a bit for it to create it. You can refresh it by that little refresh button
16. When its ready – click the arrow button and it should download
for Non english language users
If your computer language is set to any other language than English, you might need to change it to English. Preferably English, United Kingdom.
This is due to a different date and number formats. For example English, UK date format is 24/12/2023 but German is 24.12.2023. And that can cause errors within workbook.
Most common error for this:
Solution:
Would be to change the computer regional language settings to English, United Kingdom or English, United States
Spreadsheet 2.0 set up
This way you can import all of your data, from day 1 up to today. You can export data by date range (every range is up to one year of data).
Lets open the spreadsheet and start importing data!
1. Go to buttons sheet
2. Click Delete data button.
Do this only first time when you open the spreadsheet or when you want to delete all of your data from DATA sheet.
You will get a popup message: Enter the row number to delete from. Enter 4
It will delete all data below row 4 in DATA sheet.
This way you can also delete some unwanted data at the end of the spreadsheet. Just enter the nr of row you want to delete the data from. It will delete the chosen row and everything underneath.
3. Click Import trades, chose your csv file, confirm
4. Click Web trades
You will get a popup message: There’s already data here. Do you want to replace it? Click OK
NEXT THERE WILL BE A POP UP ASKING THE ROW NUMBER. WHEN YOU FIRST RUN IT, IT MUST BE 2. WE JUST WRITE 2 AND CONFIRM.
It will run all of the necessary operations. It might take some time, dependable on quantity of the data. When it finishes compiling, you will receive a confirmation message.
5. nEXT WE PREPARE DATA SHEET FOR NEXT IMPORT.
Make sure the dates exported don’t overlap. For example if you first time exported dates for up to 22nd of February 2021, then next export must start from 23rd of February.
Its is not necessary to do this step if you are closing the trades the same day.
We go to Buttons sheet and click “Prepare” button. Use it only when adding more data.
It should bring you to the DATA sheet now. We are looking for the first row what is not closed and write down the row number.
In my case it’s row 44. Please remember your number, we will need it on next import.
6. We click import trades again but now select the next batch. And remember not to overlap the dates
Now within the popup window asking for row number, enter your row number. In my case it was 44, so I enter 44
Compiling trades is quite time consuming process, but with the row entry, we can save some minutes.
You need to do steps 5 and 6 every time you want to import new data.
Once all of the import is done click The refresh button in buttons sheet.
You also can find a REFRESH ALL button in MAIN sheet.
Features and usage
To add your start balance and daily risk %, we go to DAshboard2 sheet
Enter the desired values.
I find risk % to be useful, when you are browsing trades in DATA sheet.
Losses what are over that time Account balance chosen % mark, will be displayed in red colour.
Lets explore some more options available in DATA sheet.
As you can see in picture above there is many statistics indicators available.
Trade % Gain – what will show your gain on trade
Gross PNL – your gross PNL at the time. This will start adding PNL values to chosen start balance.
Acc Gain
Hold Time – Its a hold time through out the same day. If you close the trade next day it will show up in the next indicator.
Days Held – How many days trade was held
Also here we can add our Demons, Strategies and Option strategies
We can edit Demons and strategies in their home sheets. See lower for more info.
The SHOW button on top of spreadsheet will show hidden Columns what are necessary for code to run smoothly. You can choose to show the rows or leave them hidden.
Next we can edit demons and strategies. Also option strategies.
Editing is simple. Just open their home tabs and edit the name.
Demons tab contains one “Perfect trade” strategy. Its counting the values separately from other Demons. I found it useful to bring in some sunshine from time to time lol.
We can edit Option Strategies in Database tab. A20:A30 range, marked in slightly orange colour.
Main Sheet or Stylish part of The Spreadsheet 2.0
At the top of the page you can see All time statistics data. There is many metrics included in it.
All the bottom part of the Sheet is controllable by Timeline. Timeline can be viewable by Years, Quarters, Months and even days. To change the time range, we choose how we want to sort it, by Years, Quarters, Months or Days and click and drag to select the required time range.
I found it most useful to sort it by months. But if you got very large quantities of data, Years and Quarters will be useful too.
And then there is Magnificent, lower part of the MAIN sheet
I have selected the Date range from December 2022 to January 2023
Every time you change the dates, you have to refresh everything by using Green refresh button, next to daily stats chart.
It is required to do so, to refresh all of Slicer tabs, located at the outer sides of bottom part of sheet. Slicers are like a filters to data located in MAIN page.
At the very bottom you can see all of trades from the date range selected. I found it quite useful to display it in the same page. So you don’t have to go back to DATA sheet to check the trades out.
Dont forget to reset slicers after using them.
And make sure you click the reset button after every change in date range, so slicers can update.
