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:5,446 / 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 100,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)
Philadelphia, PA
 
Trading Experience: Intermediate
Platform: Matlab, TradeStation
Favorite Futures: Stocks
 
GoldenRatio's Avatar
 
Posts: 197 since Aug 2012
Thanks: 4,778 given, 261 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
 
Trading Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 47,599 since Jun 2009
Thanks: 30,337 given, 89,708 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 (see why)

Follow me on Twitter Visit my Facebook Visit my futures io Trade Journal Reply With Quote
The following user says Thank You to Big Mike for this post:
 
  #4 (permalink)
Philadelphia, PA
 
Trading Experience: Intermediate
Platform: Matlab, TradeStation
Favorite Futures: Stocks
 
GoldenRatio's Avatar
 
Posts: 197 since Aug 2012
Thanks: 4,778 given, 261 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)
San Diego, CA
 
Trading Experience: Intermediate
Platform: TT Pro
Broker/Data: Advantage
Favorite Futures: Gasoil, heatoil, crude
 
baywolf's Avatar
 
Posts: 54 since Sep 2009
Thanks: 26 given, 30 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)
seoul, Korea
 
Trading Experience: Intermediate
Platform: Multicharts
Broker/Data: CQG, DTN IQfeed
Favorite Futures: YM 6E
 
treydog999's Avatar
 
Posts: 895 since Jul 2012
Thanks: 291 given, 1,016 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)
Texas, USA
 
Trading Experience: Advanced
Platform: X_TRADER Pro, Custom
Broker/Data: NxCore
Favorite Futures: Futures, Spreads
 
Hulk's Avatar
 
Posts: 224 since May 2014
Thanks: 531 given, 492 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).

Visit my futures io Trade Journal Reply With Quote
The following 5 users say Thank You to Hulk for this post:
 
  #8 (permalink)
Market Wizard
Gits (Hooglede) Belgium
 
Trading 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,956 since Sep 2013
Thanks: 2,407 given, 5,506 received

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

What would be the best approach ?

Follow me on Twitter Visit my Facebook Visit my futures io Trade Journal Reply With Quote
 
  #9 (permalink)
Site Administrator
Manta, Ecuador
 
Trading Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 47,599 since Jun 2009
Thanks: 30,337 given, 89,708 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 (see why)

Follow me on Twitter Visit my Facebook Visit my futures io Trade Journal Reply With Quote
 
  #10 (permalink)
Market Wizard
Gits (Hooglede) Belgium
 
Trading 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,956 since Sep 2013
Thanks: 2,407 given, 5,506 received



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 ?

Follow me on Twitter Visit my Facebook Visit my futures io Trade Journal 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)
 

futures io is celebrating 10-years w/ over $18,000 in prizes!

Right now
 

$250 Amazon Gift Cards with our "Thanks Contest" challenge!

Right now
 

Show us your trading desks and win over $5,000 in prizes w/Jigsaw Trading

August
     

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 02:14 PM
Tick Database Storage MXASJ Platforms and Indicators 40 July 20th, 2012 12:30 PM
Database - A Simple Query ab456 Traders Hideout 1 October 15th, 2011 02:04 PM
Database for NT bomberone1 NinjaTrader Programming 6 April 29th, 2011 09:11 AM
My 2 cents in mysql database iwannatoscript NinjaTrader Programming 5 March 7th, 2010 08:56 AM


All times are GMT -4. The time now is 09:18 AM. (this page content is cached, log in for real-time version)

Copyright © 2019 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