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
- account: account data (balance, equity, ...)
- quotes_daily: daily stock data
- indicators_daily: calculated multi-purpose indicators, daily basis
- ichimoku_daily: calculated ichimoku indicators, daily basis
- ichimokudailysignals: all generated ichimoku signals
- portfolio: all currently active trades
- orderbook_daily: record of all executed trades
- stoploss_daily: record of all stop losses
- currency_translation: translation of all symbol currencies to account currency
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 variablename.
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}.
date | symbol | open | high | low | close | volume | changes |
---|---|---|---|---|---|---|---|
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