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))​