Deciphering execution data from SQLite trade performance file

[OldNutty]

I don't have access to my trade performance via Ninjatrader at the moment but am trying to get all my previous trades together in a csv or sql file so I can visualize them for a data project.

I managed to open the Ninjatrader.sqlite file (found in /User/Documents/Ninjatrader 8/db) but many of the column headers are different from the executions tab documentation, and the data displayed doesn't give any context clues. NT doesn't have documentation that I can find on how to read the raw SQLite file.

A couple I need to understand in particular:

MarketPosition: returns a 0 or 1. I'm guessing this is whether the trade was long/short based on the entry & exit patterns associated, but i'm not sure which is which. There are no columns i can find that suggest long/short classification besides MarketPosition.
Also lines up with Position going up (buy) when MarketPosition was 0 and down (sell) when MarketPosition was 1.

Time: The timestamp is only displayed in 18-digit numbers. For example, the first three timestamps in this format are:
638253770893310000
638253771748400000
638253777112300000
I put it into ChatGPT and it said it might be a type of Unix timestamp based on the epoch, but using that formula to figure it out gives me dates from 1990 for these timestamps. (trades mostly happened over the last 12 months lol)

If anyone knows what these values translate to for sure, please let me know, otherwise I'd just be guessing. Does Ninjatrader use it's own unique timestamp in this file? Thank you!

[NinjaTrader_BrandonH]

Hello OldNutty,

Thanks for your notes.

This would go outside the support we would be able to provide you with in the Support department at NinjaTrader. Accessing information from the NinjaTrader.sqlite file is unsupported and undocumented.

You could consider getting your statements from the NinjaTrader Client Dashboard.

Below are NinjaTrader Support articles discussing accessing statements.

https://support.ninjatrader.com/s/article/How-do-I-access-my-daily-and-monthly-account-statements

https://support.ninjatrader.com/s/article/How-Can-I-Better-Understand-My-NinjaTrader-Daily-Statements

The forum thread will be open for other community members to share their insights on possible unsupported means you could try to accomplish this.

[OldNutty]

Turns out the time data is the number of ticks since January 01, 0001 rather than 1601 (Windows filetime) or 1970.

This Python script will return a proper date and time based on the "Time" values from the Ninjatrader.sqlite file:

from decimal import Decimal
import datetime

def ticks_to_DT(ticks):
    ticksPD = Decimal(864000000000)

    days = Decimal(ticks) / ticksPD

    remaining_ticks = Decimal(ticks) % ticksPD

    baseDate = datetime.datetime(1, 1, 1)
    delta = datetime.timedelta(days=int(days), microseconds=int(remaining_ticks) // 10)
    return baseDate + delta

#values from Ninjatrader.sqlite Time column
NTTime = [638253789873950000, 638253771748400000, 638253777112300000]

for value in NTTime:
    print(ticks_to_DT(value))​

[jrseti]

OldNutty: Thanks for this Python code! I took the liberty of converting your function to C#. Hope this helps someone...

public static DateTime TicksToDT(long ticks)
{
  decimal ticksPerDay = 864000000000m;

  // Calculate the number of whole days
  decimal days = ticks / ticksPerDay;

  // Calculate the remaining ticks after whole days
  decimal remainingTicks = ticks % ticksPerDay;

  // Base date is January 1, 0001
  DateTime baseDate = new DateTime(1, 1, 1);

  // Calculate the time span based on remaining ticks
  TimeSpan deltaDays = TimeSpan.FromDays((long)days);
  TimeSpan deltaTicks = TimeSpan.FromTicks((long)remainingTicks);

  // Add the time span to the base date
  DateTime result = baseDate + deltaDays + deltaTicks;

  return result;
}

[-=Edge=-]

trying to get all my previous trades together in a csv or sql file so I can visualize them for a data project.

What type of data visualization project are you working on?
Interested in exploring dynamic data visualization myself!

Here is NinjaScript example for Time:

string dbTradeTime           = ((reader["Time"]).ToString()); //638538035639125336
string ntTradeTimeZone       = NinjaTrader.Core.Globals.GeneralOptions.TimeZoneInfo.StandardName;
string ntTradeTimeZoneAbr    = string.Concat(System.Text.RegularExpressions.Regex.Matches(ntTradeTimeZone,
                             "[A-Z]").OfType<System.Text.RegularExpressions.Match>().Select(match => match.Value));
DateTime dtTradeTime          = (new DateTime(Convert.ToInt64(dbTradeTime)).ToLocalTime();

Print(dbTradeTime + " - " + dtTradeTime.ToString() + " - " + ntTradeTimeZoneAbr);

638538035639125336 - 6/12/2024 10:39:23 AM - CST



Be Safe in this Crazy World!

[-=Edge=-]

Question for NinjaTrader: Scenario:

3 Computers using NT 8.0.28.0. Only trade on any 1 at any time.
To keep Acc info w/hist trade analysis in sync on all computers,
I just copy the db/NinjaTrader.sqlite between them as needed.

Outside of a hassle, This works perfectly!

Now, I am going to update only one of my computers to NT 8.1.3.0,
and trying to determine if this practice might now cause issues!

Initial tests copying db back/forth between versions successful!
but would just like to confirm from NT if possibly any issues?

Could you please ask development if there has possibly been any
structural changes to the .sqlite file between 8.0.28 and 8.1.3?

Yes, in future, this might change, but in this specific case today:
Was there any structural changes made to db between 0.28 and 1.3?


Thanks

[NinjaTrader_ChelseaB]

Hello -=Edge=-,

Synching files can cause these to be locked from editing which can crash NinjaTrader. If this is done while NinjaTrader is shutdown it should be ok.

There can be changes to the database structure between versions, so it would be recommended to ensure all versions are the same.
Unfortunately, I will not be able to provide specifics on what these are, or any information about what is in the database file.

[-=Edge=-]

If this is done while NinjaTrader is shutdown it should be ok.

Yes, of course, and this works great from 8.0.28 to 8.0.28.

There can be changes to the database structure between versions, so it would be recommended to ensure all versions are the same.

Again, yes, agreed and understood! Just not ready to update everything to 8.1.3 just yet!

Unfortunately, I will not be able to provide specifics on what these are, or any information about what is in the database file.

I'm not asking what changes were made or how to access! I'm just asking, "Was there any structural changes made between 8.0.28.0 and 8.1.3.0"? a simple yes or no would suffice!


Thanks

[NinjaTrader_ChelseaB]

Hello -=Edge=-,

Yes, there are changes between these versions.

[-=Edge=-]

Yes, there are changes between these versions.

Thank You!

Sorry for my confusion here, hate beating a dead horse, but just want to clarify your words above for certainty.
Yes, there've been changes made specifically to the NinjaTrader.sqlite db file used between NT 8.0.28 and 8.1.3!

Correct? Yes/No


Thanks Again!

[NinjaTrader_ChelseaB]

Hello -=Edge=-,

Correct, from my understanding there are changes between the database in 8.0.28.0 and 8.1.3.0.

[-=Edge=-]

Correct, from my understanding there are changes between the database in 8.0.28.0 and 8.1.3.0.

As always, Thank You Very Much!