Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
consolidate / merge / sync NT8 database between two computers
I am referring to Ninjatrader 8, lifetime license and my FCM used to be Phillip Capital and since end of 2021 it became Ninjatrader Clearing. I am facing the following problem and do not know how to tackle it. Hopefully someone can help?
I have the Lifetime NT8 license and installed it on two computers, my main desktop PC and the notebook. The latter as a backup if my main PC should fail or if I am in the field on business and want to manage my trades on the notebook. Most of the time I trade on desktop computer (A).
Now I want to set up a trade performance analysis and unfortunately I found out that the trades I made on the two computers are stored locally on each computer. I had hoped and thought that this data would be on the 'cloud' of my FCM and streamed to me just like the daily balance, P/L and sort of that. But this is obviously not the case. Now I have tons of trades on computer1 (A) and computer2 (B) separated.
Goal:
I need a synced state of my trade database on desktop computer (A) so all trades are merged together and listed in date order as one would expect.
I have been able to make the following observations so far when trading on desktop computer (A) and notebook computer (B)
when I open NT8 on day 1 on the desktop PC (A) and connect to my FCM, I make 5 trades. At the end of the trade I close NT8 and turn off the computer. I see the trades on that day and also on the following days on computer (A), both under "Executions" and under "Trade Performance".
if I start NT8 on the notebook (B) on day 2 and connect and execute 8 trades there then the same applies to the notebook (B). The trades are visible on the notebook (B) for this day. If I select Day 1 under Trade Performance on the notebook (B), then not a single trade is visible.
on day 3 I start the desktop PC (A) again, open NT8 and connect to my FCM and make 4 trades. Let's say for example I made Total PnL of + $237.41 that day. Now I disconnect from the FCM, whether I leave NT8 open or not doesn't matter at this point. I start Notebook (B) and there NT8 and connect to the FCM. I see now on the notebook (B) also the current day Total PnL = + 237,41 USD. Also under "Executions" I see the 4 trades that I had actually made on the desktop PC (A). So it seems that my FCM is streaming all the info about the trades to me. But this seems to work only within one trading day. After the close of trading and settlement by the FCM, this data is reset, so my PnL is back to 0.00 USD.
So this connecting through both computers has now caused me to have the same balance stored locally on both PCs. This gives me an even more skewed data.
All those trading days on which I was successfully connected to my FCM at least once on both computers are thus stored on both computers. Otherwise only on the respective computer which had the FCM connection.
How the heck am I supposed to consolidate and compile this data? I tried to use the on-board means of Ninjatrader. There you can export e.g. under "Tools" --> "Export" --> "Backup File" only the "Database / Trade History". But if you now try to import this file on the other computer, NT8 warns that existing data will be overwritten. Thus, no consolidation can be guaranteed by the overwriting. This won't work! *sigh*
As I found out by my own research all my trade data should be stored in the file "c:\Users\patricia\Documents\NinjaTrader 8\db\NinjaTrader.sdf" if I am not mistaken. But how could I merge these two files of desktop PC (A) and notebook (B) so that in the following I will be able to succesfully import the "mix" into NT8 ?
Does anyone have an idea or a good advice to solve this problem as uncomplicated as possible? I am glad about every tip and thank you very much in advance.
Note: I am aware of this topic but this is something different because it is not related to trade history.
Can you help answer these questions from other members on NexusFi?
@patricia, maybe you can export your trades (Trade Performance Report > right-mouse click > export) to Excel and merge them there (by adding one list to another and use sort based on Date/Time). Then you can do an extensive analysis and even make pivots.
I think it's better to do this outside NT and not mess with the source data in NT. Good luck.
NinjaTrader.sdf is an SQLCE file, you can use something like Compactview to easily see its structure.
Then write a script which will export the required tables from one machine and another one which will import them.
Ninja will have to be closed during the process, and from what you describe you should export/import the tables Executions, Orders and OrderUpdates.
Sounds promising. Thank you very much for this information. I'll have a look for Compactview and try to find my way through. If anyone else has had experience with it and can assist with further details and help, such as step-by-step instructions for fulfilling this task, I am of course all ears and very grateful.
EDIT 1: Just downloaded and installed Compactview. But when trying to open the file "NinjaTrader.sdf" I get following error (in german):
Translated this means something like:
Ninjatrader8 was closed and I I tried both ways:
- isolating NinjaTrader.sdf by copying it from C:\users\me\Documents\Ninjatrader\db\NinjaTrader.sdf to C:\somewhere\else\NinjaTrader.sdf and open it with CompactView
- in CompactView tried to open directly the database file from Ninjatrader installation folder
same error appears. Any clues?
EDIT 2:
I also downloaded and installed LINQPad7. After installation it updates/installs .NET Framework 6 and downloads some components and sort of libraries or assemblys. But when trying to access the Ninjatrader.sdf (which is 173MB in size) I get the error that this file cannot be open because it is too large.
Damn, how the hell should I access the NT8 database file ?
EDIT 3:
Ah, I get Compactview issue sorted out --> SOLVED! This thread helped. I just downloaded SQLCE 4.0 SP1 as suggested in that thread and after that Compactview was able to open my NinjaTrader.sdf and I see various tables like "AccountItems, Accounts, Executions, Instrument2InstrumentList, InstrumentLists, Instruments, JournalEntries, Logs, MasterInstruments, Orders, OrderUpdates, Positions, Strategies, Strategy2Account, Strategy2Execution, Strategy2Instrument, Strategy2Order, User2Account, User2MarketDataEntitlement, Users, Versions"
Now I need further help in understanding how to proceed for this task. Can anyone assist further? Thanks a bunch in advance!
Hi Mich62 and thank for that suggestion. The trades are based on scalp trading and there are sometimes over 100 trades per day. If I was connected to my FCM with both computers at least once within the same trading day, the FCM streamed the data from the respective computer. This means that on such days the trades are stored locally on both computers NT8 database. I would therefore have several duplicates in the excel spreadsheet. I see it as impossible to do this manually though due to the tons of trades. Especially as I would like to have the evaluation tool in Ninjatrader's trade performance window. There I can filter specifically and also have all the statistics displayed. I don't want to mess around with Excel.
I really would prefer to consolidate/merge/mix the data into one single NT8 database. If I somehow manage to consolidate the different data sets of the two computers into a single NT8 database file, I will only work with one computer in the future. Or, if necessary, work via a network/UNC path so that the data is centrally located on one share. I would still have to experiment with this. But first and foremost, what is important to me now is to consolidate the data into a single Ninjatrader 8 database.
I don't think so. You see the data on your screen but that doesn't mean this is stored on your pc. It isn't, otherwise it wouldn't be gone the next day. This data is streamed from a central server to give an accurate real-time overview of your account and trades.
You won't have duplicates. Do it manually? Duplicates are easy to remove.
I can imagine. But what statistics would you be interested in to begin with? Probably nothing too fancy. What really matters? Number of winning/losing trades, WinRate, Largest Win/Loss, AverageWin/-Loss, Number of longs/Shorts, Total Win/Loss, Total and last most importantly Expectancy. This is all easy to calculate in Excel. You can make pivots showing all kind of things. You can use filters and use sorting. It will be easier to delve a little into Excel as in SQL (you need to create a new database, tables & fields. Then export/import the data. Maybe you can use the "Insert Into Select"-statement).
I think once you have the data in Excel you can do what you want with it. Next time you want to export the data you choose the date where the last export ended so you get only the new data.
All data in one single NT database or in one single Excel file isn't really that different. It's more a matter of perspective. However, the data in Excel is much more easily accessible. It's saver to work outside NT as well. If you still want to go the SQL way I hope you find someone willing to help or the links I provided will support you in doing it yourself. if you don't have Excel you can use Open Office (like I do).
Yes it is. I certainly won't sort 4,000 trades per year manually in an Excel spreadsheet or look for dupes and correct them manually.
However, that's where you're wrong. What I have described is a fact. It is not an assumption but reality, tested and tested again and confirmed. As long as both PCs are connected to the FCM within one trading day, the trades made on this day will be streamed to the PC and will be saved and stored locally on the respective PC in the NinjaTrader.sdf. And this persistently. The Order ID and Statement IDs are the same, but the Execution IDs can and will differ in most cases. If you don't believe it, just try it yourself. For my part, I have tested it extensively and have been convinced by it.
As already explained, see last question --> Yes, wou WILL have duplicates although the execution and internal IDs on the particul local database may differ. But they are dupes because the trade is the same. There are also some other slight differences which I won't cover here, this is another topic.
that is your opinion, which I respect but do not share. As I said before, I am NOT interested in Excel tables and evaluations but would like to continue using the existing display and evaluation options from NT8.
Difficult != impossible
It is merely a challenge.
Nevertheless, I thank you for your effort. I really appreciate your feedback.
Anyone else have tried doing this and can share some insight?
First of all about my approach: I intend to create a VM and install NT8 fresh there. I will use this VM for my tests. I will not touch the existing databases and thus existing installations on my desktop PC and notebook for the time being.
You pointed me to the 3 tables Executions, Orders and OrderUpdates. I'm just confused that you also mentioned Orders and OrderUpdates. Are the data contents of these two tables processed at all by the "Trade Performance" window and displayed as analysis result?
In fact, my goal is mainly to use the "TradePerformance" window to do my desired analysis of the consolidated data. When I have done this successfully, the VM will be deleted afterwards.
I thought that I would first export the data content from both databases (PC and notebook) and then join them together without dupes. I examined several dupes that exist on both databases. The columns ExecutionID and OderID of the table Executions are identical on both databases, so I could use them to filter out dupes and not include them.
Won't it be enough to focus on the Executions table?
and can anyone explain to me, how the database column "TIME" is constructed? How can I convert this very huge number into a human-reading format to get UTC or LOCAL time ? For example: the date 2021/June/02 09:26:32 (my local time in Germany UTC+1) is shown in NT8 database as value 637582155922190000
Hello again. It is with some anticipation that I report back. I think I made it! first sample tests look good and I'm very optimistic that more detailed tests will pass as well.
Since only the evaluation tools under NT8-->New-->Trade Performance were important to me, I focused exclusively on the "Executions" table and thus did not consider the "Orders" and "OrderUpdates" further for my use case.
I worked exclusively with the SDF database program "CompactView" as already suggested and mentioned here several times. One must consolidate data, several ways lead to Rome. I did it so that I took the database from the PC (NinjaTrader.sdf) and first created a copy called "testing.sdf". I then worked with it in CompactView. Then I created a new table ExecutionsNB (suffix 'NB' = Notebook) with the same schema and PrimaryKey as the original Executions table. Relationships to the other tables were not important, I proceeded according to the KISS principle (Keep it small and simple). Then I imported the previously exported data from the notebook Executions table into the new ExecutionsNB table created on the PC. Here there was a small hurdle, which I could solve with some additional effort. So I had now in the table "Executions" the data from the PC and in the table "ExecutionsNB" the data from the notebook, both in the same database file "testing.sdf". The column "Id" serves as unique identification, I now had to filter out the duplicates. I read everything from the table "ExecutionsNB" and if ExecutionsNB.Id != Exections.Id then the INSERT INTO command was executed. The number of data series before/after I compared of course, also have many screenshots and backups of before/after.
The ready mixed testing.sdf I copied then on my VM, where I had prepared NT8 freshly installed for the tests. The original NinjaTrader.sdf from the test VM/NT8 I had deleted. The finished testing.sdf was renamed to NinjaTrader.sdf. Ninjatrader8 started without problems and so far everything looks great in trade performance. All trades are listed, now everything seems to be consolidated and I can perform my desired evaluations.
So far again BIG THANK YOU to all the helpful tips here. Am pretty happy
