How to create a database for backtesting? - Traders Hideout | futures io social day trading
futures io futures trading


How to create a database for backtesting?
Updated: Views / Replies:1,679 / 12
Created: by GoldenRatio Attachments:1

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
 1  
 
Thread Tools Search this Thread
 

How to create a database for backtesting?

  #1 (permalink)
Elite Member
Philadelphia, PA
 
Futures Experience: Intermediate
Platform: Matlab, TradeStation
Favorite Futures: Stocks
 
GoldenRatio's Avatar
 
Posts: 179 since Aug 2012
Thanks: 4,556 given, 226 received

How to create a database for backtesting?

I have daily Excel spreadsheets of a stock database (db) that includes both price and fundamental information from the close of the day before. I want to create a db of this information that I will then run my backtests from. I plan to use Matlab to both create the database (save as a *.mat file) and backtest from.

My question is: what is the most efficient way to set up the database for testing?

1) Should I create a sheet for each stock where each row contains the daily information?
2) Should I create a sheet for ever days data?
3) Is there another option I am not considering?
4) How often do you backup your bd? Do you overwrite it after a set amount of time?

This is an area I have no expertise in. I'd like to hear the pros/cons of each method and from others that have created db's? How have you set yours up and what would you do different if starting over?

Obviously stock splits, dividends, de-listing, etc. will be issues to be addressed later.

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)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,357 given, 83,240 received

Answer
This post has been selected as an answer to the original posters question Answer


Follow my process as it evolved:

https://futures.io/elite-circle/21664-using-mysql-storing-tick-data.html

https://futures.io/matlab-r-project-python/29399-r-download-daily-stock-data-store-mysql.html

https://futures.io/elite-circle/21733-using-excel-query-mysql-database.html

Ultimately leading to this:

https://futures.io/elite-automated-trading/29764-big-mike-s-custom-trading-platform.html

My own platform that does everything for me. There are a few others on the forum that have done, or are doing, similar things.

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
The following user says Thank You to Big Mike for this post:
 
  #4 (permalink)
Elite Member
Philadelphia, PA
 
Futures Experience: Intermediate
Platform: Matlab, TradeStation
Favorite Futures: Stocks
 
GoldenRatio's Avatar
 
Posts: 179 since Aug 2012
Thanks: 4,556 given, 226 received

Thanks @Big Mike. I will read through all your threads linked.

I am not going to close the question just yet in hopes that others will chime in.

Reply With Quote
 
  #5 (permalink)
Elite Member
San Diego, CA
 
Futures Experience: Intermediate
Platform: x_trader pro, Excel
Broker/Data: Advantage
Favorite Futures: CL
 
baywolf's Avatar
 
Posts: 47 since Sep 2009
Thanks: 24 given, 24 received

Answer
This post has been selected as an answer to the original posters question Answer

For file-based storage, I have use datetime as the row key.

CVX_daily (DateTime, O,H,L,C,V)

For intraday the row count can get quite large so one file for each symbol for each day.

CVX_trade_20150901 (Time, Bid, Ask, BidSize, AskSize, Trade, TradeSize)


I guess its really up to you and how you plan to analyze your datasets, but this is typically how I see financial time-series datasets designed.

Reply With Quote
 
  #6 (permalink)
Elite Member
seoul, Korea
 
Futures Experience: Intermediate
Platform: Multicharts
Broker/Data: CQG, DTN IQfeed
Favorite Futures: YM 6E
 
treydog999's Avatar
 
Posts: 894 since Jul 2012
Thanks: 291 given, 1,006 received

Answer
This post has been selected as an answer to the original posters question Answer

I am using a SQL database and R for 90% of my work. Although i do have a mongoDB database that holds some news / non numerical data. C# is used for faster number crunching or if R is just too slow and then I pass the results in R to use performanceanalytics package or something to look at the results.

Once you get to larger and larger datasets you are going to have to look at better database solutions like KDB.

Reply With Quote
 
  #7 (permalink)
Elite Member
Texas, USA
 
Futures Experience: Intermediate
Platform: X_TRADER Pro, Custom
Broker/Data: NxCore
Favorite Futures: Futures, Spreads
 
Hulk's Avatar
 
Posts: 215 since May 2014
Thanks: 509 given, 474 received

Answer
This post has been selected as an answer to the original posters question Answer


GoldenRatio View Post
I have daily Excel spreadsheets of a stock database (db) that includes both price and fundamental information from the close of the day before. I want to create a db of this information that I will then run my backtests from. I plan to use Matlab to both create the database (save as a *.mat file) and backtest from.

My question is: what is the most efficient way to set up the database for testing?

1) Should I create a sheet for each stock where each row contains the daily information?
2) Should I create a sheet for ever days data?
3) Is there another option I am not considering?
4) How often do you backup your bd? Do you overwrite it after a set amount of time?

This is an area I have no expertise in. I'd like to hear the pros/cons of each method and from others that have created db's? How have you set yours up and what would you do different if starting over?

Obviously stock splits, dividends, de-listing, etc. will be issues to be addressed later.

Thank you in advance!

I trade commodity futures so my design was based on forward curves and the ability to create continuous forward curves based on various parameters. I also had to design for spreads.

To speed up the initial development, I created just 1 table for each series of data - tick, intraday and daily. I have an event calendar that assigns special meaning to certain days. I store historical business dates so that I dont have to deal with re-calculating holidays, early closes etc.

I backup my db each night. Its a SQL Server database, not my first choice but speed of development was a priority so I decided to go with everything Microsoft. Surprisingly, I find no issues with performance. The initial load time for about 16 charts x 4 different series (and each series is a calculated continuous curve) is under 2 minutes. No issues with real-time charting either (my charts are created with ChartDirector for .NET). I have 8-12 years of daily and intra-day data. About 1 year of tick data. And for each day, I have 3 forward months (ticks) to 36 forward months (time) of data. My database size is about 15 GB. So far, I havent felt the need to separate data by a range of dates.

The attached image is a portion of my database design that might help you. My data comes from DTN so the design is specific to their format but you should get a good idea from it.

Hopefully this helps.

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).

Reply With Quote
The following 5 users say Thank You to Hulk for this post:
 
  #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,507 since Sep 2013
Thanks: 1,710 given, 3,722 received
Forum Reputation: Legendary

Have you been able to insert real-time data in NT
That you compiled yourself ?

What would be the best approach ?

Reply With Quote
 
  #9 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,357 given, 83,240 received


rleplae View Post
Have you been able to insert real-time data in NT
That you compiled yourself ?

What would be the best approach ?

I'm not sure what exactly "insert" means, but assuming it means you want to put something on a chart, or check something in a strategy, I would recommend using a communication socket.

With a socket, you can query your "custom" front-end that is interfacing with your proprietary database, then give whatever response data you need. Data to fill a dataseries, or some economic number, field result, whatever. You can then chart it or whatever you wish in Ninja.

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

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,507 since Sep 2013
Thanks: 1,710 given, 3,722 received
Forum Reputation: Legendary



Big Mike View Post
I'm not sure what exactly "insert" means, but assuming it means you want to put something on a chart, or check something in a strategy, I would recommend using a communication socket.

With a socket, you can query your "custom" front-end that is interfacing with your proprietary database, then give whatever response data you need. Data to fill a dataseries, or some economic number, field result, whatever. You can then chart it or whatever you wish in Ninja.

Mike

I am able to put something on a chart...

I am not able to create a chart from zero (like a basic series/instrument)
Let say i create synthetic data and want that charted in ninjatrader...

What would you recommend ?

Reply With Quote

Reply



futures io > > > How to create a database for backtesting?

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
why Backtesting stocks difficult vs backtesting Futures emini_Holy_Grail Traders Hideout 8 July 4th, 2015 03:14 PM
Tick Database Storage MXASJ Platforms and Indicators 40 July 20th, 2012 01:30 PM
Database - A Simple Query ab456 Traders Hideout 1 October 15th, 2011 03:04 PM
Database for NT bomberone1 NinjaTrader Programming 6 April 29th, 2011 10:11 AM
My 2 cents in mysql database iwannatoscript NinjaTrader Programming 5 March 7th, 2010 09:56 AM


All times are GMT -4. The time now is 06:44 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-18 in 0.15 seconds with 39 queries on phoenix via your IP 54.221.93.187