Jump to content

Welcome to the new Traders Laboratory! Please bear with us as we finish the migration over the next few days. If you find any issues, want to leave feedback, get in touch with us, or offer suggestions please post to the Support forum here.

  • Welcome Guests

    Welcome. You are currently viewing the forum as a guest which does not give you access to all the great features at Traders Laboratory such as interacting with members, access to all forums, downloading attachments, and eligibility to win free giveaways. Registration is fast, simple and absolutely free. Create a FREE Traders Laboratory account here.

Szymon

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)? ;)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 ;)

Share this post


Link to post
Share on other sites

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

test_screen.jpg.be1b0241fa3cb2c851600509496f2f46.jpg

Share this post


Link to post
Share on other sites

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

marketcharts.thumb.jpg.8664e97bb447c4e6fd67315d7fc8832d.jpg

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.