Clean Up old account names in ninja

[StevenNelson]

I've installed a new add on which requires me to associate an account with it. I only have two live accounts now and a sim. But Ninja 8 is showing me all the past accounts I've had (many old combine accounts). How can I erase all those old accounts from Ninja except for the current ones?

(All the combine accounts have very similar names so it gets confusing to quickly select the account I want to edit with the add on.)

[NinjaTrader_PatrickG]

You'll need to rename your database file to remove those accounts:

• Shutdown NinjaTrader and Go to the Start Menu
• Select My Documents--> NinjaTrader 8--> db--> "NinjaTrader.sdf" file (may only say 'NinjaTrader' depending on your Windows File Explorer settings)
• Right click on NinjaTrader.sdf and select "Rename" *Name it "OLDNinjaTrader.sdf"
• Then restart the software and NinjaTrader will create a fresh database file to use
• Unfortunately the following items stored in the old database will be lost - Trading Hours templates, Instrument Lists / Custom Instruments, historical trade execution data, saved Strategy Analyzer backtest results

[TickBull7000]

Hi,

I had a similar need to remove specific outdated accounts, but I wanted to keep other data. So deleting the entire database wasn't an option.
In case someone has the same issue, here's how I got around this issue:

• Make sure NT is not running.
• Make a backup of your original NinjaTrader.sdf" file (...\Documents\NinjaTrader 8\db) // this allows you to come back to the previous stage if something's going wrong
• Install any Database management tool which supports SQLCe, in order to interact with .sdf files (I used the free tool: DatabaseNet4 which runs on windows)
• Open your chosen tool and connect to the NinjaTrader.sdf file database.
• Now you can modify every data entry stored within this db...

Anyway, you should have at least some basic DB knowledge or read up on it before you make any modification.
A corrupt DB due to wrong modifications may cause NT to crash or behave in an undesired way.

So in my case, I've deleted the concerned account from the "Accounts"-Table and all related data to it (Executions, Strategy2...).

[lcdparade]

Hey TickBull7000, I had the same issue, and your solution is perfect. I'm not very knowledgeable about DB stuff, but I did manage to connect the .sdf file and see the Accounts. I couldn't figure out how to delete them though, only how to edit them. Any chance you could explain how to go about doing that when you get a chance? Thanks so much!

[balltrader]

this is the official download site, don't get malware from other sites

https://fishcodelib.com/Database.htm

[lcdparade]

this is the official download site, don't get malware from other sites

https://fishcodelib.com/Database.htm

Yep, that's where I got it from. Appreciate the heads up though.

[TickBull7000]

Hey TickBull7000, I had the same issue, and your solution is perfect. I'm not very knowledgeable about DB stuff, but I did manage to connect the .sdf file and see the Accounts. I couldn't figure out how to delete them though, only how to edit them. Any chance you could explain how to go about doing that when you get a chance? Thanks so much!

Hi lcdparade,

Ok I'll try to give a quick rundown on how you could delete accounts.

Since my last post regarding manual DB manipulations, NT has evolved from SDF Database to SQLITE Database. I've noticed that it may still has an old .sdf.bak file in the db folder, but you can disregard it. Look for "NinjaTrader.sqlite" instead.
You can still use the same DB Management tool, as it supports various DB types on which you can connect to.

Some understandings on DB:
In basic terms, a database (DB) consists of tables and data sets (information). Each table holds specific information, and these tables can be interconnected through relationships between their data sets. If you remove a data set from one table that is linked to another, it can lead to a corrupted database. It's crucial to ensure that any manipulations you make don't result in these missing connections.
This is why you either have to make a more advanced DB instruction or when using simple commands (like described below) being extra careful that you've done an entire cleanup on all affected tables.


Account Cleanup Procedure:

1. Backup

Relevant SQLITE DB File: ...Documents\NinjaTrader 8\db\NinjaTrader.sqlite
Prior to any modifications, always ensure to create a backup of the original DB file.

2. Connect To DB

• Open your database management software and connect to the DB file named "NinjaTrader.sqlite", through "Connect" button --> SQLite --> Open ...)

3. Identifying Account IDs for Deletion

In Database.NET, locate all tables of the SQLite DB displayed in the DB Explorer on the left-hand side: such as "AccountItems", "Accounts", "Executions", and others.
Begin by identifying the IDs of the accounts you intend to delete. To do this, you need to load the table to view its content.

• Right-click on the table labeled "Accounts" and select "Open".

This action will automatically generate and execute a query to display the first 500 datasets in the table (click on ALL instead if you need all), eliminating the need for manual query writing.
The "Name" column displays the Account Name as it appears in NT (NinjaTrader).
The "ID" column specifies the unique identifier of each account, which is essential for cleaning up all associated tables.

• Make note of all the account IDs you wish to delete. You may record them in a notepad for reference.

4. Deleting Accounts

• Return to the left side of the interface, right-click on the "Accounts" table once more, then select "SCRIPT AS" and choose the "DELETE" template.
  please read this line carefully; do not click on "Delete" from the top menu, but rather select "SCRIPT AS" and only then from this sub-menu the "DELETE" template.
  

This action will open a new query page where you can customize the query as needed. Below are examples illustrating various deletion scenarios:

• Example 1: Deleting one specific account with ID 12
  

  DELETE FROM [Accounts]
  	WHERE id is 12

• Example 2: Delete a range of account IDs (when they are all in a consecutive range), in this example from 12 to 24 (the 12th and 24th will be deleted too)
  

  DELETE FROM [Accounts]
  	WHERE id between 12 and 24

• Example 3: Combined selection of accounts to clean in one go (12-24; 34-38; 40)
  

  DELETE FROM [Accounts]
  	WHERE id between 12 and 24
  	OR id between 34 and 38
  	OR id is 40

  
  

• After constructing the desired query, click on "Execute" in the menu bar above to execute the command.
  This will delete all the selected accounts.
  

5. Identify all relevant tables related to Accounts table:

As mentioned earlier, the database contains nested elements, so cleaning up involves addressing related tables. To identify these tables, we need to determine which ones reference the "Accounts" table. This can be achieved by checking if a table contains a row named "Accounts".

Using the DB-Explorer on the left-hand side, expand each table and its "COLUMNS" child element. Look for the presence of "Account" within the column names. The following tables, as of NT version 8.1.2.1, are identified as related to the "Accounts" table:

• Executions
• Logs
• Orders
• Positions
• Strategy2Account
• User2Account
  
  

6. Deleting Related Entries from Identified Tables


Apply the following steps to all tables identified in step 5.

• Navigate back to the left side of the DB-Explorer and right-click on the respective table, such as "Executions".

• Select "SCRIPT AS" and choose the "DELETE" template.
  please read this line carefully; do not click on "Delete" from the top menu, but rather select "SCRIPT AS" and only then from this sub-menu the "DELETE" template.

• Write the delete instructions accordingly. Note that now we refer to the column "Account" (not "ID"), which contains the Account ID from the Accounts Table.
  I will use Example 3 provided earlier:
  

  DELETE FROM [Executions]
  	WHERE Account between 12 and 24
  	OR Account between 34 and 38
  	OR Account is 40

• Construct your instructions as needed. When ready, click on "Execute" in the menu bar above.
  This will execute the command, deleting all entries containing the specified IDs in the filter criteria.

Once executed, the tool will indicate how many rows have been affected (in this example it will show something along "19 rows affected"). If "0 rows affected" is returned, it means that no dataset contained the filtered IDs in the treated table. This is normal for tables that contain "Account" but have no relations with the deleted accounts.

Note: Usually at least the tables "Executions" and "Orders" must have these ID's if you've traded the Accounts, others may not.

7. Disconnect

• If desired, keep the tabs with the queries open for future use, so you just have to insert the new IDs.
• Disconnect from the DB.
• Test if NT loads the DB properly.

Final Thoughts

Bear in mind that incorrect modifications can lead to a corrupt DB, causing NT to crash or behave unexpectedly. In case of errors, you can replace the modified version with the original backed-up one. Always shut down NT before making any DB manipulations.

Additionally, cleaning up unused accounts can significantly improve NT's loading time (relates to the delay from NT-Login to the instance before charts begin to load cached market data).
If you frequently perform Back/Forward Testing with SIM accounts, avoid using this method. Instead, safely reset your SIM account directly in NT under account settings to ensure proper cleanup of the DB.

Unfortunately, there's no direct option in NT to delete "normal" accounts safely. Depending on the account type, using the "Remove Account" function may not work when connected (obviously). Disconnecting from the accounts hides them, making it impossible to use the "Remove Account" function again.


Let me know if anything is unclear.

[lcdparade]

WOW, I really appreciate the very detailed response, TickBull7000. Your explanation was EXCELLENT, I truly wouldn't have been able to figure that out on my own, but you made it very easy, thanks again! This thread is now a complete gem for anybody that has this issue.

Hope all is well with you and your trading!

[TickBull7000]

WOW, I really appreciate the very detailed response, TickBull7000. Your explanation was EXCELLENT, I truly wouldn't have been able to figure that out on my own, but you made it very easy, thanks again! This thread is now a complete gem for anybody that has this issue.

Hope all is well with you and your trading!

You're welcome!
Let me know if it worked out for you.

[lcdparade]

You're welcome!
Let me know if it worked out for you.

Yep, worked like a charm, and I had zero issues. Thanks again for taking the time to write up the detailed instructions!

[TickBull7000]

Yep, worked like a charm, and I had zero issues. Thanks again for taking the time to write up the detailed instructions!

Excellent, good work!

No big deal, glad it was useful.