Database structure

This document describes the database structure, the various tables and their corresponding values. It should reflect the structure/definition that is expected by all functions dealing with the mysql database, especially in database.c. The MYSQL example codes can be c&p directly (make sure to tailor the commands to you needs).

Name convention

The database name can be chosen freely and must be specified in ichinscratchy.conf. The selected user needs access to the server and all privileges of the database and its tables.

List of tables currently implemented


Table account

Description

account stores some values which are needed to calculate profit/losses, your current cash, equity and such. The table hosts variables with their corresponding values. As in theory this table can store arbitrary variables and values, the following variables are currently needed by the program (and expected in the table):

  • balance (current free cash in account)
  • equity (free cash plus current value of positions- if sold right NOW)
  • riskfreeequity (free cash plus current value of positions if all their stop losses were hit NOW)
  • virginflag (initialized with 1 when creating db, valid until first position is bought- after that 0)
    The primary key is the column variable
    name.

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `account` (
  `variable_name` char(20) NOT NULL,
  `value` decimal(10,2) PRIMARY KEY (`variable_name`));

 

Example queries

Get all variables from account table:

    SELECT variable_name, value 
        FROM account 
        WHERE 
            variable_name='balance' 
            OR variable_name='equity' 
            OR variable_name='risk_free_equity' 
            OR variable_name='virgin_flag';

Table quotes_daily

Description

quotes_daily hosts the market data, which is imported from csv files (which are updated on a regular basis). They include end of day data, as shown in the example below. As shown below, the data is not ordered but stored in the same order they are pulled in. The corresponding SQL query must make sure to get a sorted search. Note that all prices are in each market´s currency (NOT in account currency!). The primary key of this table is {date,symbol}.

datesymbolopenhighlowclosevolumechanges
2016-03-09 DAX 9700,16 9838,95 9679,19 9723,09 100,9 0,31
2016-03-10 DAX 9697,64 9995,84 9498,15 9498,15 177,5 -2,31
2016-03-09 DOW 16969,17 17048,5 16947,94 17000,36 116,69 0,21
2016-03-10 DOW 17006,05 17130,11 16821,86 16995,13 117,57 -0,03
2016-03-09 GOLD 1254,5 1260 1244,8 1256,6 0,08 -0,44
2016-03-10 GOLD 1250 1273 1237,5 1272 0,12 1,23
2016-03-11 DAX 9831,13 9672,05 9833,9 9642,79 118,96 3,51
2016-03-11 DOW 17213,31 17014,99 17220,09 17014,99 123,43 1,28
2016-03-11 GOLD 1270 1280,7 1249,4 1258,7 0 -1,05


Creation

The table can/should be created using the following SQL command:

CREATE TABLE `quotes_daily` (
  `date` date NOT NULL,
  `daynr` int, 
  `symbol` char(10) NOT NULL,
  `open` decimal(16,6),
  `high` decimal(16,6),
  `low` decimal(16,6),
  `close` decimal(16,6),
  `volume` decimal(16,6),
  `changes` decimal(16,6),
  PRIMARY KEY (`date`,`symbol`));

Example queries

get all quotes for symbol DAX, order ascending:

   SELECT * from quotes_daily WHERE symbol='DAX' ORDER BY date DESC;

get quotes for last 40 days, order ascending:

   SELECT * FROM (SELECT * FROM quotes_daily 
     WHERE symbol='DAX' ORDER BY date DESC LIMIT 40)
     sub ORDER BY date ASC;

Let MySQL calculate the number of days since 1900-01-01 for each date in quotes_daily (this is much faster than shuffling huge tables from the program to the mysql server):

    UPDATE quotes_daily SET daynr = 
        (SELECT datediff(date, '1900-01-01'))
 

Update from csv

update daily DAX quotes from file data/DAX.csv important: start mysqlclient with --local-infile option and/or set local-infile=1 into [mysql] entry of my.cnf
This programs sets mysql_options(mysql,MYSQL_OPT_LOCAL_INFILE,0); (see connect_mysql_database in database.c)

Delete last two database entries to make sure the latest data is applied (in case the data is retrieved several times during a day and so the primary key {date, symbol} already exists and won't be updated).

    DELETE FROM quotes_daily WHERE symbol='DOW' 
        ORDER BY date DESC LIMIT 2;

Then load data from local csv file into table:

    LOAD DATA LOCAL INFILE 'data/DAX.csv' INTO TABLE quotes_daily 
       FIELDS TERMINATED BY ',' ENCLOSED BY ',' 
       LINES TERMINATED BY '\n'
          (date, open, high, low, close, volume, changes) 
          set symbol='DAX';

Table indicators_daily

Description

indicators_daily holds fields that are not specific for the entries of the ichimoku trading system but can be used for other parts as well (like stops, determination of position sizes) or future trading systems independend from Ichimoku. Note that all prices are in each market´s currency (NOT in account currency!). This table is very likely to change during development. At the time of writing, it holds daily values for the following fields:

  • Date
  • Nr. of days since 1900-01-01
  • Symbol
  • HH_short (short term highest high, default last 9 days)
  • LL_short (short term lowest low, default last 9 days)
  • HH_mid (mid term hh, default last 26 days)
  • LL_mid (mid term ll, default last 26 days)
  • HH_long (long term hh, default last 52 days)
  • LL_long (long term ll, default last 52 days)
  • HHATRPeriod (highest high of SLCHANDELIERATR_PERIOD period days)
  • LLATRPeriod (Lowest low of SLCHANDELIERATR_PERIOD period days)
  • TR (True Range)
  • ATR (Average True Range of SLCHANDELIERATR_PERIOD period days)
  • ADX (Average Directional Movement Index)
  • regime_filter (Market Regime Filter a.k.a. sideways trend filter)

Primary key of this table (you guessed it) is {date, symbol}

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `indicators_daily` (
  `date` date NOT NULL,
  `daynr` int,
  `symbol` char(10) NOT NULL,
  `HH_short` decimal(16,6),
  `LL_short` decimal(16,6),
  `HH_mid` decimal(16,6),
  `LL_mid` decimal(16,6),
  `HH_long` decimal(16,6),
  `LL_long` decimal(16,6),
  `HH_atr_period` decimal(16,6),
  `LL_atr_period` decimal(16,6),
  `TR` decimal(16,6),
  `ATR` decimal(16,6),
  `ADX` decimal(16,6),
  `regime_filter` decimal(16,6),
  PRIMARY KEY (`date`,`symbol`));

Example queries

Create a query which joins tables quotesdaily and indicatorsdaily. The result should contain the date and the corresponding closes and ADX values for a specific symbol. Sort the results ascending and limit to the latest 20 datasets:

SELECT * FROM 
    (SELECT 
        quotes.date,quotes.symbol,quotes.close,
        indicators.adx 
    FROM quotes_daily quotes 
    INNER JOIN 
        indicators_daily indicators ON 
            (indicators.date = quotes.date AND 
            quotes.symbol='DAX') 
    GROUP BY quotes.date ORDER BY quotes.date DESC limit 20) 
SUB ORDER BY date;

Table ichimoku_daily

Description

ichimoku_daily holds the indicators, that are specific to the ichimoku kinko hyo system, on a daily basis. Note that all prices are in each market´s currency (NOT in account currency!).

Specifically, the table holds the following fields:

  • Date
  • Nr. of days since 1900-01-01
  • Symbol
  • Tenkan (9d average)
  • Kijun (26d average)
  • Chikou (26d lagging line)
  • Senkou_A ((Tenkan+Kijun)/2) 26d shift to future
  • Senkou_B (HH52+LL52)/2 26d shift shift to future

The primary key of this table again is {date,symbol}. All of those fields can be interpreted as lines, the last 2 forming the kumo (cloud). Please note that they are a projection into the future, so don't wonder if you query the database manually and see those fields filled out without having quotes for those dates. For a detailed description of Ichimoku, search for detailed tutorials on the net. The Wikipedia entry provides a good start. Note that SenkouA and SenkouB are projected into the future, so those indicators will have future dates. Because of this, the other indicators will contain (null) for those future days (until the dates are reached and filled with values).

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `ichimoku_daily` (
  `date` date NOT NULL,,
  `daynr` int,
  `symbol` char(10) NOT NULL,
  `tenkan` decimal(16,6),
  `kijun` decimal(16,6),
  `chikou` decimal(16,6),
  `senkou_A` decimal(16,6),
  `senkou_B` decimal(16,6),
  PRIMARY KEY (`date`,`symbol`));

Example queries

Update Orange Juice Chikou Span in Database:

  INSERT INTO ichimoku_daily
    (date, symbol, chikou)
    VALUES
    (2016-02-08,'OJuice',128.600006), 
    (2016-02-09,'OJuice',127.849998),
    (2016-02-10,'OJuice',127.550003);
 

Get exactly the last 60 indicator quotes (excluding those that are (null)):

  SELECT * from 
    (SELECT * from 
    (SELECT * FROM ichimoku_daily where 
    symbol='DAX' ORDER BY date DESC) sub
    WHERE kijun IS NOT NULL ORDER BY date DESC LIMIT 60) sub 
    ORDER by date ASC;

Table ichimoku_daily_signals

Description

As the name indicates, this table stores the generated signals. This table is currently under development and expected to change a bit in the nearer future (as the definition of the signal data structure in src/datatypes.h). At the moment of writing, the table holds the following fields:

  • date
  • Nr. of days since 1900-01-01
  • symbol
  • name: name of the signal, e.g. Tenkan/Kijun Cross, Chikou Cross, ...
  • type: signal type long/short
  • strength: weak/neutral/strong
  • Amplifiying info: free text, used to clarifiy signal name with golden/ death cross
  • signal quote: price the signal had (Note: in market´s currency)
  • price quote: price the underlying had (Note: in market´s currency)
  • executed (flag, if symbol was executed)

Primary Key is as always... oh, I got you there ;) Primary Key for this table is {date, symbol, name}, as there easily can be more than one signal per day.

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `ichimoku_daily_signals` (
  `date` date NOT NULL,
  `daynr` int,
  `symbol` char(10) NOT NULL,
  `name` char(30),
  `type` char(5),
  `strength` char(10),
  `amp_info` char(30),
  `signal_quote` decimal(16,6),
  `price_quote` decimal(16,6),
  `executed` bool,
  PRIMARY KEY (`date`,`symbol`,`name`));

Example queries

Get the last 20 strong signals, ordered by symbol:

SELECT * FROM (SELECT * FROM ichimoku_daily_signals
    WHERE strength='strong' ORDER BY date DESC limit 20)
    sub ORDER BY symbol ASC;


Get all strong signals of a given period:

SELECT date, symbol, name, type, amp_info,price_quote 
    FROM ichimoku_daily_signals 
    WHERE strength='strong' AND date 
        BETWEEN '2017-09-11' AND '2017-09-15' 
    ORDER BY symbol;

 


Table portfolio

Description

This table stores all currently active trades. All signals executed by the execution manager result in trade which is being accounted in the portfolio

  • symbol
  • buydate: the day the symbol was bought
  • signaldate: the day the signal was triggered
  • type: signal type long/short
  • price_buy: buying price for one unit in market´s currency
  • price_last: last price for one unit in market´s currency
  • stoploss: current stop loss (in market´s currency)
  • initial stop loss flag: flag if SL is still the initial one
  • cost per item: price per unit at buying date in account currency
  • pos_size: weighted size 0<x<1 after allocation algorithm
  • quantity: how many units of symbol
  • p_l: current profit/loss (in account currency)
  • slpl: current profit/loss considering stoploss (in account currency)
  • trading days: how many days is this position already active

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `portfolio` (
  `symbol` char(10) NOT NULL,
  `buydate` date NOT NULL,
  `signaldate` date NOT NULL,
  `signalname` char(30) NOT NULL;
  `type` char(5),
  `price_buy` decimal(16,6),
  `price_last` decimal(16,6),
  `stoploss` decimal(16,6),
  `initial_sl` decimal(16,6),
  `cost_per_item` decimal(10,2),
  `pos_size` decimal(10,2),
  `quantity` decimal(10,2),
  `p_l` decimal(10,2),
  `sl_p_l` decimal(10,2),
  `trading_days` int,
  PRIMARY KEY (`symbol`, `buydate`,`signaldate`,`signalname`,`type`,`quantity`));

Example queries

Update a specific portfolio entry:

    INSERT INTO portfolio 
    (symbol, buydate, signaldate, signalname, type, price_buy, price_last, stoploss, 
            initial_sl, cost_per_item, pos_size, quantity, last_value, trading_days)
    VALUES ('Gold' , '2016-08-23', '2016-08-23', 'Kijun Cross', 
            'long', '1500', '1200', '666', '0', '1340.6', '0.8','10', '6660', '2') 
    ON DUPLICATE KEY UPDATE stoploss=VALUES(stoploss), 
            initial_sl=VALUES(initial_sl), last_value=VALUES(last_value), 
            trading_days=VALUES(trading_days);

Table orderbook_daily

Description

This table stores all transaction that were ever taken. The metadata is used for statistics.

  • date: date of buying or selling
  • symbol
  • type: long/short
  • buy/sell: transaction was buying or closing a position
  • price: price of symbol per unit (in market´s currency)
  • signalname: name of signal
  • cost per item in account currency
  • pos_size: weighted size 0<x<1 after allocation algorithm
  • quantity: how many units of symbol
  • buydate: when was this position bought?
  • signaldate: date when the corresponding signal/sl occured
  • hold days: if sell: how many days between buy/sell
  • comission: trade comission for broker in account currency
  • initial stoploss: initial SL in market´s currency
  • PLtotal: if sell: profit/loss of total position (in account currency)
  • PLpiece: if sell: profit/loss per unit (in account currency)
  • PLpercent: if sell: profit/loss in percent (in account currency)

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `orderbook_daily` (
  `date` date NOT NULL,
  `symbol` char(10) NOT NULL,  
  `type` char(5) NOT NULL,
  `buy_sell` char(4) NOT NULL,  
  `price` decimal(16,6),  
  `signalname` char(30) NOT NULL,
  `cost_per_item` decimal(10,2),
  `pos_size` decimal(10,2), 
  `quantity` decimal(10,2),
  `buydate` date,
  `signaldate` date NOT NULL,
  `hold_days` int,
  `comission` decimal(16,2),  
  `stoploss` decimal(16,6),
  `P_L_total` decimal(10,2),
  `P_L_piece` decimal(10,2),
  `P_L_percent` decimal(10,2),
  PRIMARY KEY (`date`,`signaldate`,`symbol`, `price`, `type`,`buy_sell`, `signalname`));

Example queries

Let's do some statistics on the orderbook! Give the Profit/Loss (by percent) and the number of trades for year 2006 - 2016 of the orderbook, ordered by years:

    SELECT YEAR(date) AS `year`,  ROUND(SUM(P_L_total),4) AS `Profit total`,
    count(*) AS `nr. of trades`  FROM orderbook_daily  
    WHERE date BETWEEN '2006-01-01' AND '2016-11-01' AND buy_sell='sell' 
    GROUP BY YEAR(date);


Do the same, but this time more detailed by symbols:

   SELECT YEAR(date) AS `year`, symbol, ROUND(SUM(P_L_total),4) AS `Profit total`,
   count(*) AS `nr. of trades`  FROM orderbook_daily
   WHERE date BETWEEN '2006-01-01' AND '2016-11-01' AND buy_sell='sell' GROUP BY YEAR(date), symbol;

Do the same like in 1st example, but this time more detailed by month, order by year and month:

SELECT   
    YEAR(date) AS `year`,   MONTHNAME(date) AS `month`,  
    ROUND(SUM(P_L_total),4) AS `Profit total`, count(*) AS `nr. of trades` 
    FROM orderbook_daily  WHERE 
    date BETWEEN '2006-01-01' AND '2016-11-01' 
    AND buy_sell='sell' GROUP BY YEAR(date), MONTH(date);

 


Table stoploss_daily

Description

This tables stores the history of all trade's stop losses. At the moment it is intended for development only and might be removed in future versions. Note that all prices are in each market´s currency.
The table holds the following columns:

  • symbol (which symbol)
  • buydate (when was the date of buying)
  • sl_type (what sl type?)
  • date (which date is this sl?)
  • sl (this date's sl price level) Primary key is {symbol, buydate, sl_type, date} to allow multiple active trades for one symbol and even switching the type of SL in the lifespan of a single trade.

Creation

The table can/should be created using the following SQL command:

CREATE TABLE `stoploss_daily` (
  `symbol` CHAR(10) NOT NULL,
  `buydate` date NOT NULL,
  `sl_type` CHAR(10) NOT NULL,
  `date` date NOT NULL,
  `sl` decimal(16,6) NOT NULL,
  PRIMARY KEY (`symbol`, `buydate`, `sl_type`,`date`, ));

 

Example queries

    tbd

Comments powered by CComment