Getting data out of NinjaTrader.SDF SQL Server Compact - NinjaTrader Programming | futures io social day trading
futures io futures trading


Getting data out of NinjaTrader.SDF SQL Server Compact
Updated: Views / Replies:2,150 / 10
Created: by vantojo Attachments:0

Welcome to futures io.

(If you already have an account, login at the top of the page)

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors Ė all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you donít need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

Reply
 
Thread Tools Search this Thread
 

Getting data out of NinjaTrader.SDF SQL Server Compact

  #1 (permalink)
Elite Member
Neuquen, Argentina
 
Futures Experience: Intermediate
Platform: Ninja
Favorite Futures: CL, TF, 6S, NG, sometimes GC
 
Posts: 195 since Jul 2012
Thanks: 25 given, 39 received

Getting data out of NinjaTrader.SDF SQL Server Compact

I use SAP/Sybase Powerbuilder development system to create my own analytical reports. It is C# / .NET friendly. However, connecting to the Ninja SDF database seems very difficult. I was able connect with a Registry kludge under a prior version of Windows, but now on Windows 8 and with a new version of Powerbuilder, I am going to give up. (That is, unless someone can tell me how to have the Powerbuilder Database Painter connect in a clean way to the SDF.)

Otherwise I want to export the entire schema and then the daily data from the Ninja database into a SQL Server LocalDB or Express database. Even better yet would be to pipe it daily into a Sybase/SAP SQL Anywhere database, which is native to Powerbuilder.

I would like to grab the Ninja schema and port it to the other database, then set up a daily pipe to copy the data completely into the second database, where I can easily access it.

I used to work in SQL Server (eenterprise) some time ago, and it seems this would be possible, but I don't remember. Then there was a Data Transformation Service that would probably do it.

(And who knows if MS is even going to support the SDF format anymore. I suppose the next version of Ninja might use LocalDB.)

Even better yet, would be to have the database painter in Powerbuilder 12.5 (SAP/Sybase) be able to access the SQL Compact database....but I'm not going to hold my breath. (Because Powerbuilder has an automated data pipe.)

Any ideas?

Thank you in advance.

Reply With Quote
 
  #2 (permalink)
Quick Summary
Quick Summary Post

Quick Summary is created and edited by users like you... Add FAQ's, Links and other Relevant Information by clicking the edit button in the lower right hand corner of this message.

 
  #3 (permalink)
Elite Member
Neuquen, Argentina
 
Futures Experience: Intermediate
Platform: Ninja
Favorite Futures: CL, TF, 6S, NG, sometimes GC
 
Posts: 195 since Jul 2012
Thanks: 25 given, 39 received

possiblilty


Looks like there is a Replication service that can be set up between the Ninja SDF database technology and its bigger brother SQL Server Express. Once the data is ported then it is easy to get to by many different tools.

(C# can access the SDF, but I need these other analytic tools to get to the data.)

The Replication Servce requires that Ninja SDF be in version SQL Server Compact 3.5, which Ninja confirmed it is.

Thanks.

Reply With Quote
 
  #4 (permalink)
Elite Member
Omer עומר / Israel י
 
Futures Experience: Master
Platform: NinjaTrader, Proprietary,
Broker/Data: Ninjabrokerage/IQfeed + Synthetic datafeed
Favorite Futures: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,489 since Sep 2013
Thanks: 1,704 given, 3,694 received
Forum Reputation: Legendary

I am trying to read the NinjaTrader.sdf and that works fine, BUT, where I'm having trouble is with the orderid.

When i create an order with a unique ID through the ATI interface, the order gets into NT and it goes
to the market. When i ask the status through the ATI interface, i can follow the order until it gets
executed.

The problem is, when i touch the order in the chart trader and move the limit, my program has no possibility to
"see" this change to the limit, stop or target.

For this reason i want to read the DB and get the information directly from there, but i can not match my own orderid
with the orderid as it is stored in the DB, the value seems to be 'scrambled' or simply not correspond to my value,
anybody any idea or has looked to this before ?

Reply With Quote
 
  #5 (permalink)
Elite Member
Omer עומר / Israel י
 
Futures Experience: Master
Platform: NinjaTrader, Proprietary,
Broker/Data: Ninjabrokerage/IQfeed + Synthetic datafeed
Favorite Futures: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,489 since Sep 2013
Thanks: 1,704 given, 3,694 received
Forum Reputation: Legendary

I think i got one step closer to the solution. Whatever orderid i choose, the result in the database is a 32 hex string
this makes me think, this is not the orderId but a hash on the orderId. Most likely this is an MD5 hash algo...

A hash is a cryptographic reduction of a given text.

This also explain, if i query with my orderid, they are able to find back the order, without the reference being in the DB.

Reply With Quote
 
  #6 (permalink)
Elite Member
Omer עומר / Israel י
 
Futures Experience: Master
Platform: NinjaTrader, Proprietary,
Broker/Data: Ninjabrokerage/IQfeed + Synthetic datafeed
Favorite Futures: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,489 since Sep 2013
Thanks: 1,704 given, 3,694 received
Forum Reputation: Legendary

The mystery is solved. For every order there is a unique GUI created for orderid and orderref, nothing to do with the reference chosen to do the ATI call. The custom order number is stored in the BLOB userdata as XML

0x3C004E0069006E006A0061005400720061006400650072003E003C005F004C00650067006100630079004E0061006D0065003E0045006E007400720079003C002F005F004C00650067006100630079004E0061006D0065003E003C004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E0031003C002F004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E003C002F004E0069006E006A0061005400720061006400650072003E00

Which becomes after decoding

 
Code
<NinjaTrader><_LegacyName>Entry</_LegacyName><AutomatedTradingOrderId>1</AutomatedTradingOrderId></NinjaTrader>
Which allows to retrieve the orderID given to the order of ATI.
In this case 1.

Reply With Quote
 
  #7 (permalink)
Elite Member
NJ
 
Futures Experience: Intermediate
Platform: NinjaTrader, tradestation
Favorite Futures: forex
 
Posts: 9 since Oct 2010
Thanks: 2 given, 0 received

Trackign chart trader ORDER ID


rleplae View Post
The mystery is solved. For every order there is a unique GUI created for orderid and orderref, nothing to do with the reference chosen to do the ATI call. The custom order number is stored in the BLOB userdata as XML

0x3C004E0069006E006A0061005400720061006400650072003E003C005F004C00650067006100630079004E0061006D0065003E0045006E007400720079003C002F005F004C00650067006100630079004E0061006D0065003E003C004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E0031003C002F004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E003C002F004E0069006E006A0061005400720061006400650072003E00

Which becomes after decoding

 
Code
<NinjaTrader><_LegacyName>Entry</_LegacyName><AutomatedTradingOrderId>1</AutomatedTradingOrderId></NinjaTrader>
Which allows to retrieve the orderID given to the order of ATI.
In this case 1.



Thanks for the insight you provided from your research. Below is what I understood from your post -
From chart trader when ever a price crosses a limit order level... a Ninja generated OrderID is generated. and we can see this in Order tab. This GUID is order refID (YOU Referenced in your post.)

Now when this order is sent to broker and it is filled ..actual OrderID from broker is returned and gets stored in ORDERID filed in SQL db.

Now when you query with Ninja generated ID...you can trace the order status and actual order ID.

lET ME KNOW MY UNDERSTANDING IS CORRECT HERE OR NOT!!

In my case.. whenever a limit order is filled by chart trader I want to read the ninjatrader generated order ID. And after fill I want to know actual order ID. Whenever stop order is moved ON CHART CAN WE READ THAT VALUE DIRECTLY FROM THIS NINJA COMPACT DB.?

Reply With Quote
 
  #8 (permalink)
Elite Member
Omer עומר / Israel י
 
Futures Experience: Master
Platform: NinjaTrader, Proprietary,
Broker/Data: Ninjabrokerage/IQfeed + Synthetic datafeed
Favorite Futures: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,489 since Sep 2013
Thanks: 1,704 given, 3,694 received
Forum Reputation: Legendary


saurabh View Post
Thanks for the insight you provided from your research. Below is what I understood from your post -
From chart trader when ever a price crosses a limit order level... a Ninja generated OrderID is generated. and we can see this in Order tab. This GUID is order refID (YOU Referenced in your post.)

Now when this order is sent to broker and it is filled ..actual OrderID from broker is returned and gets stored in ORDERID filed in SQL db.

Now when you query with Ninja generated ID...you can trace the order status and actual order ID.

lET ME KNOW MY UNDERSTANDING IS CORRECT HERE OR NOT!!

In my case.. whenever a limit order is filled by chart trader I want to read the ninjatrader generated order ID. And after fill I want to know actual order ID. Whenever stop order is moved ON CHART CAN WE READ THAT VALUE DIRECTLY FROM THIS NINJA COMPACT DB.?

I don't remember exactly and i would have to deep dive into my code
the easiest way is to just do a few tests..

The above post should allow you to make the link

Reply With Quote
 
  #9 (permalink)
Elite Member
NJ
 
Futures Experience: Intermediate
Platform: NinjaTrader, tradestation
Favorite Futures: forex
 
Posts: 9 since Oct 2010
Thanks: 2 given, 0 received


rleplae View Post
I don't remember exactly and i would have to deep dive into my code
the easiest way is to just do a few tests..

The above post should allow you to make the link


Can you please share a code snippet from your side. that will be of great help.
Also please confirm my understanding after reading your post.

Reply With Quote
 
  #10 (permalink)
Elite Member
Omer עומר / Israel י
 
Futures Experience: Master
Platform: NinjaTrader, Proprietary,
Broker/Data: Ninjabrokerage/IQfeed + Synthetic datafeed
Favorite Futures: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,489 since Sep 2013
Thanks: 1,704 given, 3,694 received
Forum Reputation: Legendary



saurabh View Post
Can you please share a code snippet from your side. that will be of great help.
Also please confirm my understanding after reading your post.

Hope this helps you getting started :

 
Code
   // update order status 
        // search for order based on NTorderId
        // - check limit
        // - check quantity
        // - (status is not updated) because we do this through the ATI interface
        public void synchroniseLimitQuantityPositions(int dbDebug, List<PositionRec> PositionTable)
        {
            int int_i;

            int quantity;
            decimal limitprice;
            decimal stopprice;
            decimal avgfillprice;
            int orderstate;

            try
            {
                // open NT DB
                string sdfFILE = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\Documents\\NinjaTrader 7\\db\\NinjaTrader.sdf; " + "File Mode=Read Only; SSCE:Temp File Directory=C:\\myTempDir\\;";
                // open the ninjatrader.sdf database 
                SqlCeConnection con = new SqlCeConnection("Data Source = " + sdfFILE);
                {
                    con.Open();

                    // order table holds the open orders (entry orders)
                    //
                    for (int_i = PositionTable.Count - 1; int_i >= 0; int_i--)
                    {
                        if (!PositionTable[int_i].isSimulation && PositionTable[int_i].NTtargetId != "")
                        {
                            // prepare SQL statement
                            string mySQLcommand = "SELECT quantity, limitprice, avgfillprice,  orderstate FROM nt_order WHERE name='Target1' AND orderid='" + PositionTable[int_i].NTtargetId + "'";

                            // Read in all values in the table.
                            using (SqlCeCommand com = new SqlCeCommand(mySQLcommand, con))
                            {
                                SqlCeDataReader reader = com.ExecuteReader();
                                while (reader.Read())
                                {
                                    quantity = reader.GetInt32(0);
                                    limitprice = (decimal)reader.GetDouble(1);
                                    avgfillprice = (decimal)reader.GetDouble(2);
                                    orderstate = reader.GetInt32(3);

                                    // order quantity changed -> update quantity
                                    if (quantity != PositionTable[int_i].Quantity)
                                    {
                                        if (dbDebug > 0 && PositionTable[int_i].Quantity !=0)
                                            Log(PositionTable[int_i].NTcode + " Qty was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Quantity + " to : " + quantity);
                                        PositionTable[int_i].Quantity = quantity;
                                        PositionTable[int_i].Manual = true;
                                    }
                                    // order limit changed - > update order 
                                    if (limitprice != PositionTable[int_i].Target && orderstate == 9)
                                    {
                                        if (dbDebug > 0 && PositionTable[int_i].Target != 0)
                                            Log(PositionTable[int_i].NTcode + " Target limit was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Target + " to : " + limitprice);
                                        PositionTable[int_i].Target = limitprice;
                                        PositionTable[int_i].Manual = true;
                                    }
                                    // order was executed, price is avgfillprice
                                    if (orderstate == 2)
                                    {
                                        if (dbDebug > 0)
                                            Log(PositionTable[int_i].NTcode + " Target got hit in NT for order " + PositionTable[int_i].OrderSeqNr + " at : " + avgfillprice);
                                        PositionTable[int_i].AvgTorS = "T";
                                        PositionTable[int_i].Avgfillprice = avgfillprice;
                                    }
                                }
                                reader.Close();
                            }
                        }
                        if (!PositionTable[int_i].isSimulation && PositionTable[int_i].NTstopId != "")
                        {
                            // prepare SQL statement
                            string mySQLcommand = "SELECT quantity, stopprice, avgfillprice, orderstate FROM nt_order WHERE name='Stop1' AND orderid='" + PositionTable[int_i].NTstopId + "'";

                            // Read in all values in the table.
                            using (SqlCeCommand com = new SqlCeCommand(mySQLcommand, con))
                            {
                                SqlCeDataReader reader = com.ExecuteReader();
                                while (reader.Read())
                                {
                                    quantity = reader.GetInt32(0);
                                    stopprice = (decimal)reader.GetDouble(1);
                                    avgfillprice = (decimal)reader.GetDouble(2);
                                    orderstate = reader.GetInt32(3);

                                    // order quantity changed -> update quantity
                                    if (quantity != PositionTable[int_i].Quantity)
                                    {
                                        if (dbDebug > 0  && PositionTable[int_i].Quantity != 0)
                                            Log(PositionTable[int_i].NTcode + " Qty was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Quantity + " to : " + quantity);
                                        PositionTable[int_i].Quantity = quantity;
                                        PositionTable[int_i].Manual = true;
                                    }
                                    // order limit changed - > update order 
                                    // 
                                    if (stopprice != PositionTable[int_i].Stop && orderstate ==0)
                                    {
                                        if (dbDebug > 0 && PositionTable[int_i].Stop != 0)
                                            Log(PositionTable[int_i].NTcode + " Stop limit was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Stop + " to : " + stopprice);
                                        PositionTable[int_i].Stop = stopprice;
                                        PositionTable[int_i].Manual = true;
                                    }
                                    if (orderstate == 2)
                                    {
                                        if (dbDebug > 0)
                                            Log(PositionTable[int_i].NTcode + " Stop got hit in NT for order " + PositionTable[int_i].OrderSeqNr + " at : " + avgfillprice);
                                        PositionTable[int_i].AvgTorS = "S";
                                        PositionTable[int_i].Avgfillprice = avgfillprice;
                                    }

                                }
                                reader.Close();
                            }
                        }
                    }
                    con.Close();
                }
            }
            catch
            {
                Log("Exception in : synchroniseLimitQuantityPositions ");
            }
        }

Reply With Quote
The following 2 users say Thank You to rleplae for this post:

Reply



futures io > > > > > Getting data out of NinjaTrader.SDF SQL Server Compact

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)

Jigsaw Trading: TBA

Elite only

FuturesTrader71: TBA

Elite only

NinjaTrader: TBA

Jan 18

RandBots: TBA

Jan 23

GFF Brokers & CME Group: Futures & Bitcoin

Elite only

Adam Grimes: TBA

Elite only

Ran Aroussi: TBA

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
Market analysis with SQL shodson Platforms and Indicators 8 March 28th, 2015 12:45 AM
Stable Server solution - automated trading with NinjaTrader + Interactive Brokers Foerster NinjaTrader 2 August 9th, 2013 06:24 AM
Eurex Autotrading - Server Hosts / Broker / Data Feeds Koepisch Reviews of Brokers and Data Feeds 24 December 10th, 2012 10:45 AM
Ninja - populate SQL CE database with OHLCV information ejtrader The Elite Circle 2 January 31st, 2012 09:43 AM
Quality of the data from replay NT7 server amethyste Reviews of Brokers and Data Feeds 24 November 18th, 2010 06:40 PM


All times are GMT -4. The time now is 04:31 PM.

Copyright © 2017 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts
Page generated 2017-12-13 in 0.13 seconds with 19 queries on phoenix via your IP 54.90.92.204