MySQL udf library for technical analysis
Switch branches/tags
Nothing to show
Clone or download
Pull request Compare This branch is 6 commits behind joaocosta:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
config
examples
setup
specfile
src
test
windows
.gitignore
.travis.yml
AUTHORS
COPYING
ChangeLog
Dockerfile
GPL-LICENSE
INSTALL
MIT-LICENSE
Makefile.am
Makefile.in
Makefile.simple
NEWS
README
README.md
aclocal.m4
autogen.sh
build_packages.sh
config.h.in
configure
configure.ac
sampledb.sql

README.md

Build Status

DESCRIPTION:

Implements technical analysis functions as MySQL UDFs.

Currently implemented functions are:

TA_SMA
TA_EMA
TA_RSI
TA_TR (True Range)
TA_SUM (Running sum, as opposed to aggregate sum provided by mysql)
TA_STDDEVP (Running stddevp, as opposed to aggregate stddevp provided by mysql)
TA_PREVIOUS

Other indicators which can be derived from those functions include:

MACD
Bollinger Bands
ADX

AVAILABILITY:

Source repository at: http://github.com/joaocosta/lib_mysqludf_ta

DOCKER IMAGES

# Create a new empty database
docker run --name fxdata -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=fxdata -d mysqludf/ta

# Alternatively, mount an existing database outside the container
docker run -v /my/databasedir:/var/lib/mysql --name fxdata -d mysqludf/ta

# Connect with mysql client inside a separate container
docker run -it --link fxdata:mysql --rm mariadb sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

See more examples of how to use the mariadb image at https://hub.docker.com/_/mariadb/

COMPILING:

Windows:

A binary dll is shipped with this release.

Copy it to the MySQL plugin directory. To find out what the MySQL plugin directory is run this in a MySQL prompt:

show variables like 'plugin_dir';

The following article describes how to compile MySQL UDFs in Windows: http://rpbouman.blogspot.com/2007/09/creating-mysql-udfs-with-microsoft.html

Linux:

./configure
make
sudo make install

If this fails, run ./autogen.sh and repeat the "configure && make && make install" sequence

For more information on compiling MySQL UDFs:

http://dev.mysql.com/doc/refman/5.1/en/udf-compiling.html
http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html

#Install the UDFs

From the MySQL prompt:

Windows

CREATE FUNCTION ta_ema RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_max RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_min RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_previous RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_rsi RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_sma RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_stddevp RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_sum RETURNS REAL SONAME 'lib_mysqludf_ta.dll';
CREATE FUNCTION ta_tr RETURNS REAL SONAME 'lib_mysqludf_ta.dll';

Linux

CREATE FUNCTION ta_ema RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_max RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_min RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_previous RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_rsi RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_sma RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_sum RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_stddevp RETURNS REAL SONAME 'lib_mysqludf_ta.so';
CREATE FUNCTION ta_tr RETURNS REAL SONAME 'lib_mysqludf_ta.so';

Or simply:

cat setup/*_up.sql | mysql -uroot

Test your installation

make check  #This assumes a local mysql instance is available, username root with no password

FUNCTIONS:

To try the examples below import the provided sampledb.sql into a MySQL database.

mysqladmin -u root create lib_ta
mysql -u root lib_ta < sampledb.sql
mysql -u root lib_ta

ta_ema - Exponential moving average

ta_ema(
    float data, 
    int period
)
  • data - The data to average
  • period - Running period to calculate for

Example:

To calculate a 50 period EMA of closing prices:

SELECT datetime, ta_ema(close, 50)
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

ta_sma - Simple Moving Average ( aka Running average )

ta_sma(
    float data, 
    int period
)
  • data - The data to average
  • period - Running period to calculate for

Example:

To calculate a 50 period SMA of closing prices:

SELECT datetime, ta_sma(close, 50)
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

ta_rsi - Relative Strength Index

ta_rsi(
    float data, 
    int period
)
  • data - The data to calculate rsi for
  • period - Running period to calculate for

Example:

To calculate a 14 period RSI of closing prices:

SELECT datetime, ta_rsi(close, 14)
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

To calculate a 10 period ta_ema of ta_rsi(14):

SELECT datetime, ta_ema(ta_rsi(close, 14), 10)
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

ta_stddevp - Running Standard Deviation

ta_stddevp(
    float data, 
    int period
)
  • data - The data to calculate on
  • period - Running period to calculate for

Example:

This function is useful for calculating indicators such as Bollinger Bands.

To calculate the upper limit Bollinger Band of 2 standard deviations over a 21 period sma:

SELECT datetime, ( ta_sma(close,21) + 2*ta_stddevp(close,21) ) AS `BOL_UP`
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

To calculate the lower limit Bollinger Band of 2 standard deviations over a 21 period sma:

SELECT datetime, ( ta_sma(close,21) - 2*ta_stddevp(close,21) ) AS `BOL_DOWN`
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

ta_sum - Running Sum ( as opposed to aggregate sum )

ta_sum(
    float data, 
    int period
)
  • data - The data to average
  • period - Running period to calculate for

Example:

To calculate a 50 running sum of closing prices:

SELECT datetime, ta_sum(close, 50)
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

ta_tr - Calculate True Range

ta_tr(
    float high,
    float low,
    float close
)
  • high - The Highest price for the period
  • low - The Lowest price for the period
  • close - The Closing price of the period

Example:

To calculate True Range

SELECT datetime, ta_tr(high, low, close)
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

ta_previous - Calculate True Range

ta_previous(
    float data,
    int period
)
  • data - The data to lookback into
  • period - Number of periods to look back into

Example:

See if today's close is greater than yesterday's close SELECT datetime, close > ta_previous(close,1) FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;


ta_max - Running Max

ta_max(
    float data,
    int period
)
  • data - The data to search the maximum for
  • period - Running period to calculate for

Example:

To return the maximum close over the last 50 periods: SELECT datetime, ta_max(close, 50) FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;


ta_min - Running Min

ta_min(
    float data,
    int period
)
  • data - The data to search the minimum for
  • period - Running period to calculate for

Example:

To return the minimum close over the last 50 periods: SELECT datetime, ta_min(close, 50) FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;


macd - Moving Average Convergence / Divergence

MACD is defined as the difference between two emas

SELECT datetime, ta_ema(close,12) - ta_ema(close,26) AS `MACD`
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

The MACD signal line is defined as an EMA of MACD

SELECT datetime, ta_ema(ta_ema(close,12) - ta_ema(close,26), 9) AS `MACD_SIGNAL`
FROM ( SELECT * FROM EURUSD_86400 ORDER BY datetime ASC ) AS T;

USING WITH INTEGER DATA INSTEAD OF FLOATS:

Financial data tends to be stored as floats, however sometimes it might be useful to run these functions with integer data. This can be achieved using MySQLs CAST:

SELECT ta_ema(CAST(integer_data_field AS DECIMAL(65), 14) FROM TABLE;

CREATING NEW FUNCTIONS:

  • The easiest way would be to copy one of the existing .c files to a different name and modify its implementation.
  • Edit test/03_test.sh and add a suitable test to validate results of the new function using the provided dataset.
  • Add a setup script for your new function under the setup directory. These are used by automated deployment mechanisms.
  • Edit Makefile.am and add the new source file.
  • To generate a new configure script, run the provided autogen.sh ( this depends on automake, autoconf and libtool being installed ).

CONTACT:

João Costa joaocosta@zonalivre.org