How to create a database for backtesting? - futures io
futures io futures trading



How to create a database for backtesting?


Discussion in Traders Hideout

Updated by schematics12
      Top Posters
    1. looks_one rleplae with 3 posts (0 thanks)
    2. looks_two Big Mike with 3 posts (1 thanks)
    3. looks_3 GoldenRatio with 2 posts (0 thanks)
    4. looks_4 baywolf with 1 posts (0 thanks)
    1. trending_up 5,915 views
    2. thumb_up 6 thanks given
    3. group 7 followers
    1. forum 12 replies
    2. attach_file 1 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 100,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
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

(If you already have an account, login at the top of the page)

 
Search this Thread
 

How to create a database for backtesting?

(login for full post details)
  #1 (permalink)
Philadelphia, PA
 
Experience: Advanced
Platform: Matlab, TradeStation
Trading: Stocks
 
GoldenRatio's Avatar
 
Posts: 201 since Aug 2012
Thanks: 4,940 given, 278 received

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

Can you help answer these questions
from other members on futures io?
Which broker that works with NT8 has a Debit Visa card?
Brokers
FYI plot bug & workaround: changing color or width is delayed 1 bar
EasyLanguage Programming
Data Feed sharing possible between TS/IB and 3rd party apps?
Platforms and Indicators
Options Backtesting Software with Market Replay tool/feature?
Options
TOS script
ThinkOrSwim
 
Best Threads (Most Thanked)
in the last 7 days on futures io
Are sharks watching on the other side?
58 thanks
VWAP for stock index futures trading?
32 thanks
Earn2Trade (Helios) - "The Gauntlet"
17 thanks
Sierra Chart ACSIL for Beginners.
15 thanks
FIO Journal Challenge - June 2020 w/SharkIndicators
14 thanks
 
(login for full post details)
  #3 (permalink)
Site Administrator
Manta, Ecuador
 
Experience: Advanced
Platform: My own custom solution
Trading: Emini Futures
 
Big Mike's Avatar
 
Posts: 48,890 since Jun 2009
Thanks: 31,573 given, 94,493 received


Follow my process as it evolved:







Ultimately leading to this:



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

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/
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:
 
(login for full post details)
  #4 (permalink)
Philadelphia, PA
 
Experience: Advanced
Platform: Matlab, TradeStation
Trading: Stocks
 
GoldenRatio's Avatar
 
Posts: 201 since Aug 2012
Thanks: 4,940 given, 278 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
 
(login for full post details)
  #5 (permalink)
San Diego, CA
 
Experience: Intermediate
Platform: TT Pro
Broker: Advantage
Trading: Gasoil, heatoil, crude
 
baywolf's Avatar
 
Posts: 55 since Sep 2009
Thanks: 26 given, 31 received

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
 
(login for full post details)
  #6 (permalink)
seoul, Korea
 
Experience: Intermediate
Platform: Multicharts
Broker: CQG, DTN IQfeed
Trading: YM 6E
 
treydog999's Avatar
 
Posts: 896 since Jul 2012
Thanks: 291 given, 1,023 received

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
 
(login for full post details)
  #7 (permalink)
Texas, USA
 
Experience: Advanced
Platform: TT Pro, Custom
Broker: dxFeed
Trading: Futures, Spreads
 
Hulk's Avatar
 
Posts: 248 since May 2014
Thanks: 588 given, 650 received


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.


Visit my futures io Trade Journal Reply With Quote
The following 5 users say Thank You to Hulk for this post:
 
(login for full post details)
  #8 (permalink)
Gits (Hooglede) Belgium
 
Experience: Master
Platform: NinjaTrader, Proprietary,
Broker: Ninjabrokerage/IQfeed + Synthetic datafeed
Trading: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,990 since Sep 2013
Thanks: 2,437 given, 5,793 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
 
(login for full post details)
  #9 (permalink)
Site Administrator
Manta, Ecuador
 
Experience: Advanced
Platform: My own custom solution
Trading: Emini Futures
 
Big Mike's Avatar
 
Posts: 48,890 since Jun 2009
Thanks: 31,573 given, 94,493 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

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/
Follow me on Twitter Visit my Facebook Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #10 (permalink)
Gits (Hooglede) Belgium
 
Experience: Master
Platform: NinjaTrader, Proprietary,
Broker: Ninjabrokerage/IQfeed + Synthetic datafeed
Trading: 6A, 6B, 6C, 6E, 6J, 6S, ES, NQ, YM, AEX, CL, NG, ZB, ZN, ZC, ZS, GC
 
rleplae's Avatar
 
Posts: 2,990 since Sep 2013
Thanks: 2,437 given, 5,793 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


futures io Trading Community Traders Hideout > How to create a database for backtesting?


March 21, 2016


Upcoming Webinars and Events
 

Free BloodHound Licenses for everyone!

June
 

Every journal equals ten meals for the hungry

June
     



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