New Open Source Project - MS Sql Server Market Data Colletor.

[jmayo05 10]

It sounds like you and I have very similar interestes. I would love to be involved in the project. Admittedly, I don't know a whole heap in application programming. (More knowledgable in web development and server hardware.)

 

I'm currently employeed at an FCM to monitor and administer an in-house trading system. My overall goal is to test system ideas and market trends that I notice while watching the futures markets at work. If you need resources, such as a server to host the data, or whatever, loop me in and I would love to help out.

[filip 10]

I'm SQL Server database administrator. If you need my help about sql, database, indexes,...etc let me know, I'd like to take part in this project. Actually I've developed something but it has manual export from my provider and then import and analyse.

F:missy:

 

 

 

 

Hi All,

 

I'm starting this thread, it is going to be based on open source license.

 

The aim of this application project is to produce software that will record hourly price of instruments into a MS Sql server. This server can be sitting locally or somewhere else via the web, you can then use .NET xml technologies to retrieve information.

 

The first part of the project is to find out a cheap or free hourly market data provider that has an API feed.

 

What are the advantages of this kind of application? Well for all you system builders out there, You can then develop other applications that can utilize the information and build systems with your formulas and strategies that your third party applications cannot provide.

 

After we will find the right market data source. We will then get into developing the database structure. I will post SQL files here so you can create your own databases. At this stage I am only interested in 1hour time frame so that will be the focus for this project but you can change that to suit any other time frame.

 

The last part of the project is to develop using vb.net a small application that will utilize the market data API feed and record new bar information into the database. The information that I want to record is OPEN, CLOSE, HIGH and LOW for multiple instruments, with an update feature in case the comptuer has been switched off for a number of hours.

 

I am willing to provide a MS 2008 database that will have web access available but only to those people that will assist me in this project. I will build a .NET web query xml page where you can utilize this information for yourself and your other projects.

 

By having multiple clients talking to the database server, no matter what machine is switched off the database is constantly being updated from all those other machines that are switched on. By helping you have a choice to utilize this information for your own projects.

 

I hope this makes sense.

 

If you have any questions please do not hesitate to ask me.

 

Regards

 

Simon

[filip 10]

I'm SQL Server database administrator. If you need my help about sql, database, indexes,...etc let me know, I'd like to take part in this project. Actually I've developed something but it has manual export from my provider and then import and analyse.

F

:missy:

[dam5h 10]

DISCLAIMER: i'm not very familiar with sql, though i've used it once or twice. so i'm just throwing this out there for anyone as an fyi for anyone interested in a similar project to this thread that make seek an alternative framework.

 

what i have used and really liked for historical backtesting is pytables, it's pretty awesome--can natively treat a portions (amount currently in RAM) of a database as a numpy array and has some real speedy and easy to use c based find/matching functions. i've used TA-Lib wrapped in python (but actually run in c) to populate the tables in the case that i wanted some indicators

 

plus, its python ; so we talking rapid development times.

 

matplotlib and scipy have some cool charting capabilities including candlesticks in the finance module.

 

not sure if it can be used with ms .NET's iron python distribution, i've only used it with linux

 

PyTables - Getting the most *out* of your data

An Introduction to PyTables video tutorial - PyTables Tutorials - Learn: programming_tools, interface, extremely_large_datasets, pytables, HDF5, efficient, tutorial, tools, cover, data, solution, interfaces, free, videos, video, fast, features, infor

 

Numpy Home Page

matplotlib: python plotting — Matplotlib v0.98.6svn documentation

TA-Lib : Technical Analysis Library - Home

[marcj 10]

Generally, you sign legal agreements when you buy data, not to destribute it to others. So is this legal?

[AldPixto 10]

I've put several years of futures price data into MySQL tables. My thoughts are:

 

1 - Timezones are important

1a - The database should self-identify what timezone data is being stored in, e.g. some integer offset relative to UTZ (i.e. keep database in EST, CST, whatever makes sense to you)

1b - Data loading applications need to know what timezone the source data is in so they can normalize to the target database (becomes important when people in different timezones can contribute data)

1c - User Applications (e.g. charts) should be able to convert database times into end-user times

 

2 - Even if you're only computing or plotting "floor" session data, it is useful to have 24-hour data (e.g. globex) so that overnight high low prices are known

 

3 - Market Profile charts

3a - the maximum time quanta is 30 minute bars, the "current" bar can be updated with greater frequency (e.g. every 30 seconds) for semi-realtime charts

3b - Volume-at-Price data is necessary for Market Profile but it will force you to compromise the minimum price-delta (e.g. $1 for $BRK.B , $0.01 for $GLD)

3c - separate tables for OHLC (key: YYMMDD,HHMM) and Volume-at-Price (key: YYMMDD,HHMM,Price) help speed charting for MP and non-MP charts

 

4 - Store dates and prices as integer values

4a - need date methods to compute yymmdd+date_offset and (yymmdd+-yymmdd) as date offset (my database is in yymmdd and I will have to convert to yyyymmdd by next year)

4b - convert fractional prices (ags, bonds) to integers so that ranges can be easily computed, e.g. multiply Ag prices by 25 and 30-Year bonds by 15625 to eliminate the fractional portion of the price

 

5 - DBA stuff - data sources are imperfect and I have to reload data to remove bad ticks or fill-in missing times more often than I'd like. the real problem is finding bad data or missing ticks before the tick data goes offline (DTN tick data only goes back 8 days, eSignal tick data goes back 10 days)

[dam5h 10]

i really like the date/time module in python, it handles all that timezone, hour, and day of week stuff. not saying that you would, but if you ever wanted to do historical testing specific to days of week, you're covered without any tedious coding on your end.

[jmayo05 10]

Is there anyone out there that has the old OpenTick API or data available? For whatever reasons they shut down, it would be great if we could pick up (parts) of where they left off.

[uexkuell 10]

I joined OpenTick long ago. It certainly was a nice idea.

 

But I have to say: Their data was always messy. E.g. if you downloaded a continuous range of ES futures tick data it was always cluttered with holes - worthless for any serious analysis.

 

It was obviously only a testfield: They used the service to eliminate bugs (with the help of their customers) then moved to commercial.

[magister 10]

I am a c# .net developer and familiar with SQL, not so much the admin of db's but familiar with SQL scripts, creating tables, stored procedures. I would be interested in doing some work on this. I am mostly interested in getting tick data, but hourly data would be a good place to start. It would also be nice to have volume data.

[NetTecture 10]

Stupid question, but what is the sense of that?

 

THere are numerous APIs allowing access to hourly data rages (google financials for example). Unless one gets down to the roots and collects tick data including bid and ask it is useless for short term analysis and it is in particularly useless for trade simulation, especially during off times.

 

At least in Futures, data providers allowing the capture of complete exchange data (CME, CBOT) are not that expensive.

[BlowFish 10]

I am inclined to agree (as I said earlier in the thread) saving each tick and each order book change would generate something unique and valuable.

[NetTecture 10]

Not sure about the complete order book (though it may come in handy at times - like to see when it is particularly shallow and then to stay out of the market- happens in the YM at times before news). But at least best bid and ask are conditio sine qua non in some markets.

 

Try trading emini financial futures during exchange nights. The bid and ask move in perfect sync with forex quotes - obviously, enough arbitrage companeis there.

 

Sadly, it rarely trades. It trades SO rarely, stops are useless.... one has to program ones own stop mechanism to react on bid/ask running through ones stop price. I had (in simulator) situations where bid and ask passed through my stop about 30 ticks before someone decided to trade one contract (which triggers the stop). Perfect in sync with exchange regulations (trades trigger stops, only), but... in this case the trades only are just not the real representation of the market.

[BlowFish 10]

I am not sure about order book but why not collect it at least that would make that sort of analysis possible

 

It does seem that price tends to move towards order book size. There is an interesting thread over at ET some years back now. I also have a strong suspicion if you look at orders filled vs. orders pulled that you might find interesting information.

[NetTecture 10]

Well

 

My RIthmic tests are "on hold" until they fix some issue in their API - it overloads on AMD processors with the full CME feed, works fine on Intel They got an AMD server from me now for testing I Hope they sort it out soonish.

 

I am now working on order handling and subscribing only to specific instruments for now I hope to start full data capture next month (I really hope) Did my first trade today (on my app against CME test environment) - the whole low level API's are not exactly totally easy Now time for some front end and data storage

 

I hear a lot of mentioning of OpenTick (defunct as they are) Anyone thinks there is a market for not necessariyl real time historical data (especially if it includes best bid/ask or even full bid/ask)?

[davla 10]

Interested in your database project, please keep up the great work.

How far have you gotten?

 

a few years ago.. opentick had such an offering... you might find some views on the net about them... morevoer you might find someone with the original opensource database..

 

however data from open tick has stopped sometime last year or 1.5 years ago..

 

They created a new firm called xasax Xasax Corporation and i am sure a phonecall to establish relationships might prove a great way to ocntinue their efforts on the side... maybe you as the premier opensource group to carry on their orignal opentick efforts ( which i guess failed as a comemrcial venue)..

 

however it might be a direction to take .. if there are a group of supporters for code and for sharing expenses of data..

 

if you aligned with a group of traders i am sure xasax might give you an account....

 

you might have to go as far as forming an LLc or something since there might be

(i guess ) stipulations that you do not re-distribute data..

 

however if a user is a member of the llc.. I think that would satisfy the re-distribution limitations,

 

Exchanges don't like people re-distributing data!!!!

They loose money.

 

A bit on me.. I am not a developer... however i am involved in quant trading in design and integration of intelligent systems for inst'l firms. i am always looking for a group that will promote great open source tools ofor real time and historical databases.

 

Rgds

Davla

[NetTecture 10]

Interested in your database project, please keep up the great work.

How far have you gotten?

 

a few years ago.. opentick had such an offering... you might find some views on the net about them... morevoer you might find someone with the original opensource database..

 

however data from open tick has stopped sometime last year or 1.5 years ago..

 

They created a new firm called xasax Xasax Corporation and i am sure a phonecall to establish relationships might prove a great way to ocntinue their efforts on the side... maybe you as the premier opensource group to carry on their orignal opentick efforts ( which i guess failed as a comemrcial venue)..

 

however it might be a direction to take .. if there are a group of supporters for code and for sharing expenses of data..

 

if you aligned with a group of traders i am sure xasax might give you an account....

 

you might have to go as far as forming an LLc or something since there might be

(i guess ) stipulations that you do not re-distribute data..

 

however if a user is a member of the llc.. I think that would satisfy the re-distribution limitations,

 

Exchanges don't like people re-distributing data!!!!

They loose money.

 

A bit on me.. I am not a developer... however i am involved in quant trading in design and integration of intelligent systems for inst'l firms. i am always looking for a group that will promote great open source tools ofor real time and historical databases.

 

Rgds

Davla

 

Davia... You are SO under the wrong impression about me Hehe.

 

* There is no need to form an LLC or something I operate an IT company that just opens a trading branch, hopefully moving into that direction.

* Similar the cost of data for me is zero I only collect data I want to have anyway I strife into system development, and full tick data is a necessity. I basically need them for backtesting And as I do not know what I Will trade next month.... I collect complete exchanges.

 

I have ot say I am not so interested in... hm.... open source per se. This is data collection for an enterprise. Being someone else open source arm would mean a commitment I am not sure I want to make at that point Plus, seriously.... the code is more trivial. The main problem is handling the data I am afraid I Will need a new server for the data - with another 24 discs or so Next year

 

The real deal would be sharing the data - and that would not be free. Too much investment needed for that This is a tremendous amount of stuff going through the system

 

Non-Redistribution is basically always for real time - and in that case... I will adhere. Point. An LLC would not protect against that anyway.... but I Think non-real-time is really valuable enough for many people for backtesting

 

My timeframe is starting with CME and possibly FOREX 1st of October. We have some performance issues with our data collector so far in the provider code that works great under intel, but breas down on AMD processors. I hope they fix it next week It sucks if a CPU core (and their network code is inherently single threaded - which makes sense, as long as you only have one connection for one login) breaks down and uses way too much CPU. Totally no clue where that comes from - as I said, on Intel it works fine.

 

Once that is done, we will start collection on CME and - as I said - possibly FOREX, CBOT and the ICE are next on the list. I basically will add them as I see I can handle the data volume And as I actually need them for my own operations.

 

Once the full feed scalability is fixed.... my collectors start working Even if only in dump mode (write all out to a text file for later transversion). My programming focus is now on trade capture for internal reporting and getting the whole cleaning / accounting side into a database - so I can trade with another frontend and capture the trades with a software for internal reporting. Plus holding one's hand (new trader starting tomorrow after sim training - lets see how that goes) and looking for the first programmer to add full time to that project

 

Btw., I am 60% done with defining a binary coded market data stream format Which WILL be open source Incl. C# code to read it

[Szymon 10]

Hi All,

 

Been busy here but have devoted more time on this project.

 

As you all know the first stage is to see if the concept works. So I have created a small application that connects to the OpenECry servers and show you all their contracts. I have not polished this application so it is crude but it works.

 

I have attached this application in its binary form.

 

If you have an valid OpenECry account then use that make sure you change the server address to prod.openecry.com from api.openecry.com and press the login button.

 

I will try to do some more work this weekend. Once this connecting software is finished all I want it to do for now is to select the required symbols to track and shows what variables are the for every contract that is selected in realtime.

 

Then we will discuss the next part of the project.

 

All the best.

 

Simon

Debug.rar

[Szymon 10]

Hi All,

 

It's been a while since I posted here, haven't given up on this project yet.

 

Here is a snapshot of it in action.

 

I have written a chart application to handle the data which is now stored in a MS SQL database.

 

Early days, but slow as at the moment I'm working on other projects. So a few hours on the weekends is as much time as I can devote.

 

The application that displays this data is all written by me, I have incorporated Gann techniques into it, so it shows how effective they are and other bits and pieces.

 

Happy Trading

[alibaba5055 10]

Thanks for the contribution Szymon

Any new updates on this..