I know I should probably learn MATLIB or SPSS or some other statistical tool, but I've never learned those tools and it turns out I'm really good with SQL Server, so I'm looking at building a data mine of market data and metrics that I am interested in researching.

Currently, I am just doing this on the freely available SQL Express. If/when I need more BI capabilities and SSIS I will migrate to a full developer version of SQL Server.

So, what I've done so far is export a few years of historical data out of NT7 continuous contract data, imported them into SQL, and am now working on creating daily measurements (bars) of the market via stored procedures into fact tables.

For example, here's my script that processes the daily OHLCV data and stores various SMAs that might be of interest.



I have a lot more to do, and can provide updates of my progress, but I was just wondering if anybody else likes analyzing the markets with SQL.

Interesting.

If you subscribe to the idea that a trading platform is really just a front end to a database with some market data and brokerage connectors rolled in, it gives you a new perspective.

I posted some C# console apps I'm using to help my own SQL development and understanding of using distributed SQL instances on a network here:

Daily Trade Reconciliation Strategy Hi all, I have to code a Strategy that will output the contents of the Ninja DB shown in Executions tab on a daily basis for trade reconciliation. First version will output to a txt file on a network drive, but the end game is to have it write directly …

. It is in the Elite section but I'd be happy to post the code here if you think it will help this tread develop.

Have you looked at LINQ? Once you have the data you need in SQL you can use external apps to manipulate it and report. LINQ/C#, R to SQL, Matlab to SQL, etc are all at your disposal in way not easily done with the NT database itself.

If you like free stuff R might be worth a look before Matlab.

MXASJ 

Interesting. If you subscribe to the idea that a trading platform is really just a front end to a database with some market data and brokerage connectors rolled in, it gives you a new perspective. I posted some C# console apps I'm using to help my own SQL development and understanding of using distributed SQL instances on a network here: Daily Trade Reconciliation Strategy Hi all, I have to code a Strategy that will output the contents of the Ninja DB shown in Executions tab on a daily basis for trade reconciliation. First version will output to a txt file on a network drive, but the end game is to have it write directly … . It is in the Elite section but I'd be happy to post the code here if you think it will help this tread develop. Have you looked at LINQ? Once you have the data you need in SQL you can use external apps to manipulate it and report. LINQ/C#, R to SQL, Matlab to SQL, etc are all at your disposal in way not easily done with the NT database itself. If you like free stuff R might be worth a look before Matlab.

I've played with LINQ but, like most other object-data mapping attempts created by Microsoft, I hit a wall that prompted me to throw it away. For object-relational mapping (ORM) I have created my own code generator that I've used for years, based on similar approaches used by Ruby on Rails and other frameworks inspired by enterprise design patterns ( Fowler). Microsoft's "entity framework" looks perhaps maybe they're getting closer, but if you don't have your own ORM framework for .NET I'd recommend Subsonic.

shodson 

I know I should probably learn MATLIB or SPSS or some other statistical tool, but I've never learned those tools and it turns out I'm really good with SQL Server, so I'm looking at building a data mine of market data and metrics that I am interested in researching. Currently, I am just doing this on the freely available SQL Express. If/when I need more BI capabilities and SSIS I will migrate to a full developer version of SQL Server. So, what I've done so far is export a few years of historical data out of NT7 continuous contract data, imported them into SQL, and am now working on creating daily measurements (bars) of the market via stored procedures into fact tables. For example, here's my script that processes the daily OHLCV data and stores various SMAs that might be of interest. I have a lot more to do, and can provide updates of my progress, but I was just wondering if anybody else likes analyzing the markets with SQL.

@shodson thanks a lot for this thread. I know it is a very old thread, but could you please post an update. Did you make any progress in doing analysis with SQL ? Or you found it to be very limiting and therefor dumped it midway ?

I am also interested in doing some analysis within SQL Server for the EOD database that I have. I think that a lot of work could be done within such databases, which would be difficult to do in the normal platforms like ninja etc. Since you did this years ago, therefor I am very interested in knowing about your overall practical experience in this regards.

Thanks and regards

Be sure to also check:

Using MySQL for storing tick data Thought I would document a few things. I am using MariaDB (MySQL) on a Debian server. I have a database called 'ticks', and a table called 'qcollector'. I am storing QCollector (tick data) data here. root@media:~# mysql Welcome to …

And

Big Mike's custom trading platform Hi guys, So I've written several posts about this all over the place but I keep getting questions and PM's so it makes more sense for this to have its own thread. First, I need to tell you that while I have no problem sharing a great deal, …

I use MySQL extensively in my own platform (second thread).

Mike

Big Mike 

Be sure to also check: Using MySQL for storing tick data Thought I would document a few things. I am using MariaDB (MySQL) on a Debian server. I have a database called 'ticks', and a table called 'qcollector'. I am storing QCollector (tick data) data here. root@media:~# mysql Welcome to … And Big Mike's custom trading platform Hi guys, So I've written several posts about this all over the place but I keep getting questions and PM's so it makes more sense for this to have its own thread. First, I need to tell you that while I have no problem sharing a great deal, … I use MySQL extensively in my own platform (second thread). Mike

Thanks Mike, I will check them out.

anny 

@shodson thanks a lot for this thread. I know it is a very old thread, but could you please post an update. Did you make any progress in doing analysis with SQL ? Or you found it to be very limiting and therefor dumped it midway ? I am also interested in doing some analysis within SQL Server for the EOD database that I have. I think that a lot of work could be done within such databases, which would be difficult to do in the normal platforms like ninja etc. Since you did this years ago, therefor I am very interested in knowing about your overall practical experience in this regards. Thanks and regards

Yes I made progress and it's a core part of my trading today. Instead of finding it limiting, I find it very expansive and additive to my trading toolbelt instead of just relying on vendor-provided software. If you have the skills, time, and passion for statistics and quantitative methodologies I highly recommend something along these lines.

shodson 

Yes I made progress and it's a core part of my trading today. Instead of finding it limiting, I find it very expansive and additive to my trading toolbelt instead of just relying on vendor-provided software. If you have the skills, time, and passion for statistics and quantitative methodologies I highly recommend something along these lines.

Thanks for the update shodson.

That means that SQL Server has got good potential in this direction. I will definitely spend more time on this.

Regards