mysql database code sample

[NinjaTrader_JoshP]

If you want to export real-time information you need to program it in C#. Unfortunately this is beyond what we can support. Please see this sample for exporting to a text file: http://www.ninjatrader-support2.com/...ead.php?t=3475

[iwannatoscript]

If you want to export real-time information you need to program it in C#. Unfortunately this is beyond what we can support. Please see this sample for exporting to a text file: http://www.ninjatrader-support2.com/...ead.php?t=3475

Great Example.

Could yuo post an example how to write directly in excel or calc please?

By ninjatrader is the same wrire in excel or in calc?


If i buy qcollector

http://www.ninjatrader-support2.com/vb/showthread.php?t=11299

what is the real "pro" that could we have?

In which case do guys suggest to buy?

I really don't understand why could be useful, because if i connect to

esignal i reciva also tick data of 10 days ago, but this already do esignal or am i wrong?
To avoid paying esignal i use mirus and the fire?
so wgat is a good reason that could i buy this software?

look

http://kb.esignalcentral.com/display/2n/articleDirect/index.asp?aid=1203&r=0.350857

Is there a datafeed that give me more that 10 days of tickdata, isn't this a backfiil?

Is it possible to have a real backtest, like to be in real trading, is it possible by market replay?

[NinjaTrader_Bertrand]

No, in NinjaTrader you would use the inbuild NinjaScript coding engine, here're some good tutorials for a start - http://www.ninjatrader.com/support/h...tml?Overview18

For more advanced projects, you can check out those reference samples - http://www.ninjatrader-support2.com/...splay.php?f=30

For datafeeds, we can't really recommend you one, as we have many equally respected partners. For an overview over all supported data options, please check this grid - http://www.ninjatrader.com/support/h...HistoricalData

There will always be differences between backtesting and realtime trading (fills), but Market Replay and the Strategy Analzyer can give you a very good idea if it's worth forward testing your strategy or not - http://www.ninjatrader.com/support/h...TimeVsBacktest

[uprz23]

DB Connection Optimization

First of all, thanks for the C# codes. I had no idea how to use mysql before reading it.

To help with the CPU usage and efficiency of the code, I try opening the db connection in Initialize() and closing it in Dispose().

Here's the pseudo code.

public class IndicatorName : Indicator
    {
        #region Variables
        ...
	private MySqlConnection dbConnection = null; 
	...
        #endregion

       
        protected override void Initialize()
        {
		...

		string myConnectionString = "Database=" + dBName + ";Data Source=" + dBHost + ";User Id=" + dBUser + ";Password=" + dBPass;
		dbConnection = new MySqlConnection(myConnectionString);
		dbConnection.Open();
			 
		...
			
        }
		
        public override void Dispose()
        {

            base.Dispose(); 
			
            // clase the db connection at the end
            if (dbConnection != null) {
                dbConnection.Close();
                dbConnection = null;
            }
			
        }
		
		...
 
        protected override void OnMarketDepth(MarketDepthEventArgs e)
        {
		...
		MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
		// using dbConnection from class and not creating a new one every method call
		myCommand.Connection = dbConnection; 	
		myCommand.ExecuteNonQuery();
		...
		
	}
		
	...
}

[DangerBoy]

Hi guys

Maybe a stupid question this or perhaps I'm missing the point, why are you guys creating an external database connection to a table and then storing tick data? Are you not able to just pull down the tick data from the Ninja Historical servers as i've done with the FX instruments I trade?

The reason I stumbled across this thred is I was looking if it's possible to access external DB's from Ninja so that my auto strats can take account of information that I would like to enter via a custom external dashboard. I can see it's now possible but still just wondering why everyone is so keen to store the tick data.

Cheers
Ross

[auspiv]

Hi Ross, the main reason I wanted to store tick data is so I could store not only the actual trades, but all bid and ask updates as well. In addition, NinjaTrader only stores timestamp information to the nearest second--not nearly enough detail for a precise backtest using a complete level 1 feed.

I'm very happy to see many of you implementing this and coming up with ideas for improving this sample. The very reason I posted this was because no one had as far as I could tell. Now that someone has, everyone gets the benefits, which is exactly what I was going for.

As far as storing tick data goes, I've actually abandoned this project a while ago. Glad to see everyone still has an active interest for linking NinjaTrader and MySQL--there are many, many possibilities with this combination, like keeping track of executions/positions outside of NT.

EDIT: Forgot to say that all improvements you guys mentioned are great ideas. Maybe I'll take this further one day. It could be improved by the following:

• Open the connection in Initialize and close it with Dispose.
• Reference the instrument name so there is only one script that works for every instrument.
• Create methods that query the database to see if tables exist for the instrument and if the tables don't exist, create them--a seemless way to instantly start building a table for any instrument without fumbling around with creating the table yourself.
• Create large datasets in memory and the write to the database every hundred (or thousand) records or so (or you could append the insert text with StringBuilder).

That all I can think of now, and I know those four items are just the start to optimizing/streamlining this code.

Anyways, thanks for all the feedback everyone! Even if I'm not making use of all the suggestions, someone else is for sure.

[DangerBoy]

Sure that is most reasonable, I myself have spent a substantial amount of time using the backtest functionality and have a couple of workarounds to make it as near accurate as possible like adding a secondary tick bar time series which ensures my market fills are "near enough" to the point of the trade. The bid/ask as you say is the one thing i am unable to do anything about so i've kind of fallen back on the idea that backtesting will never allow you a 100% accurate story of how the strategy would have performed but merely a indication of whether its worth forward testing.

I would assume by going to the level of trying to store the bid/ask you are looking for even closer to accurate fills but then the issue is how do you intend to backtest this data as surely it's now a completely external element and not able to be interpreted by either the strategy analyser for automated NT backtesting or by the NT charts for manual backtesting? Furthermore I would think if you are going to this level of accuracy then like me you will be backtesting your systems over no less than a years worth of data?

If your main reason was to show the potential of the external connection then fully understood and definitely it's been invaluble, thanks for posting this, I just notice a few other posters immediately all wanting to do the same so was wondering if I'm missing something about existing NT tick data as it stands.

[VladimirN]

How watching DOM in Excel?

I very need real-time watching DOM in Excel.
Anybody have idea - how it possible?

Please, give example scripts for such data-export!!!

Only watching, one DOM 5x5, no orders-import. Thanks.

[r2kTrader]

Decus,

You are my hero!

I was going to write the same thing when I got around to it. If you could PM me, I would be happy to help in anyway.

If you don't want to share, I understand. I am willing to put a few bucks your way if that makes sense for you. Since it is not "proprietary" in nature, I thought I would ask.

Maybe I could help you with something as well.

Good stuff,

Have a look at persistent connections and the min/max pool sizes.

As for mySQL vs MSSQL, well I would argue there is more user support for mySQL than MSSQL free edition, but it basically comes does to user choice.

Myself, I'm using mySQL with ninjatrader strats to not trade around news events. I wrote a java app to strip forex news from a website every day then when I go to enter a trade my strat checks the db to see if there is news coming up.

I decided to use a db and not parse the website directly in NT mainly so that I backtesting would be quicker, and also I didn't want to put extra burden on the news website I was parsing.

Also, my mySQL db is on another computer within my LAN, seems to work fine so far.

edit: I spell good

[palinuro]

I was doing this for a while in order to maintain a DB with timestamps to the millisecond, but I've now switched to getting the actual values directly from the ZenFire API.

I would like to read calculated values from the DB back into an NT indicator for plotting, but I am not sure what would be the best approach. Does anyone have any experience doing this? Any suggestions?

[Jean-Marc-Molina]

Writing in a MySQL Database

Hi,

I am looking for a Ninja Strategy (or indicator) code that writes data into a mySQL Database. Has anybody been able to write successfully such a code with Ninja 6.5?

Thanks in advance.


Jean-Marc.

[spoowiz]

How to READ mysql data

Hi,
I'm new to NT and found this forum. I followed the directions and the script compiles successfully with the sample code. (thanks).
This sample "writes" data (using insert command). I have a need to "read" mysql table's fields to NT variables for plotting.
Would someone be so kind to provide a sample code of how to do that?
Thanks

[iwannatoscript]

Ok, i am lookin for an how to that explain how to set up database?

Are there any how to or any mnuals of set up?

[Taddypole]

so, i've been trying to implement a tick database for quite some time now, and alot of it came together last night. here is an indicator that writes L1 data to a table that also receives the tick quotes. it is quite cpu intensive. i haven't done any testing, optimization, or anything really other than get a "working" solution. maybe some of you guys can give some advice for optimization. well here is the structure for the mysql table:

CREATE TABLE `cl` (
  `id_tick` int(16) NOT NULL auto_increment,
  `time` varchar(17) NOT NULL,
  `bidvol` int(8) NOT NULL,
  `bidprice` float NOT NULL,
  `askprice` float NOT NULL,
  `askvol` int(8) NOT NULL,
  `lastprice` float NOT NULL,
  `volume` int(8) NOT NULL,
  PRIMARY KEY  (`id_tick`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

and the indicator code is attached, where qqqqq = database name, cl is table name, zzzzz is the server, xxxxx is user, and yyyyy is password.

if you have any ideas to improve this code, or any ideas at all for that matter, just say it.

edit: changed attachment to more current, better version

I'm new to Ninja Trader and would like to create this indicator but I don't know the basic steps. Can someone supply me with basic direction? The DBwritesES.txt file was attached but I don't know what to do with it.

Thanks,
Taddypole...

[Taddypole]

OK... i tried renaming the file "DBWriteES.txt" to DBWriteES.cs".
I then copied this file into the

..My Documents\NinjaTrader 7\bin\Custom\Indicator directory

I then reloaded Ninja Trader and looked for the indicator in the indicator list but its not there.

I can see it with the editor.

Can someone tell me what i'm doing wrong?

Thanks,
Taddypole...