Downloads

Information

Provides functions commonly used in technical analysis of financial markets.

about

MySQL UDFs offer a powerful way to extend the functionality of your MySQL database.

The UDFs on this site are all free: free to use, free to distribute, free to modify and free of charge. Even for commercial projects.

Checkout README.txt in the source distribution for the most up to date version of this document: README

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_PREVIOUS (Returns a result N periods ago)

Other indicators which can be derived from these functions include:

  • MACD
  • Bollinger Bands
  • Possibly others

Installing

Compile the library

I have only tried building this on Linux with MySQL 5.1 . If you manage to build it on other systems please let me know.

You need the following to build this library:

  • gcc or some other C compiler
  • mysql development packages

Just run make and it should build a shared library lib_mysqludf_ta.so. You might need to edit the Makefile to tell the compiler where to find the MySQL development header files.

Install the shared library

As root, run:
make install

This simply copies the library to /usr/lib/mysql/plugin and restarts mysql. Again, you might need to edit the Makefile if your MySQL plugin directory is different.

Register the UDFs with MySQL

The provided installdb SQL script will register all functions with mysql server:

mysql -u root -p < installdb

Basic test

From the MySQL prompt try the following simple test

SELECT ta_ema(10.0, 1);

This should return 10.0

More information

For more information on building mysql udf libraries:

  • http://dev.mysql.com/doc/refman/5.1/en/udf-compiling.html
  • http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html
  • http://rpbouman.blogspot.com/2007/09/creating-mysql-udfs-with-microsoft.html

API

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

mysqlimport somedb < sampledb.sql

ta_ema - Exponential moving average

ta_ema ( float data, int period )

Arguments

  • 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 EURUSD_86400;

sma - Simple Moving Average ( aka Running average )

ta_sma ( float data, int period )

Arguments

  • 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 EURUSD_86400;

rsi - Relative Strength Index

ta_rsi ( float data, int period )

Arguments

  • data - The data to average
  • period - Running period to calculate for

Example

To calculate a 14 period RSI of closing prices:

SELECT datetime, ta_rsi(close, 14) FROM EURUSD_86400;

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

SELECT datetime, ta_ema(ta_rsi(close, 14), 10) FROM EURUSD_86400;

tr - Calculate True Range

ta_tr ( float high, float low, float close )

Arguments

  • high - The Highest price for the period
  • low - The Lowest price for the period
  • close - The Closing price for the period

Example

To calculate True Range

SELECT datetime, ta_tr(high, low, close) FROM EURUSD_86400;

ta_sum - Running Sum ( as opposed to aggregate sum )

ta_sum ( float data, int period )

Arguments

  • data - The data to average
  • period - Running period to calculate for

Example

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

To calculate a 50 running sum of closing prices:

SELECT datetime, ta_sum(close, 50) FROM EURUSD_86400;

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

SELECT datetime, ta_sma(close,21) + 2*SQRT(ta_sum(POW(close - ta_sma(close, 21), 2), 21)/21) FROM EURUSD_86400;

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

SELECT datetime, ta_sma(close,21) - 2*SQRT(ta_sum(POW(close - ta_sma(close, 21), 2), 21)/21) FROM EURUSD_86400;

ta_max - Running Max ( as opposed to aggregate max )

ta_max ( float data, int period )

Arguments

  • data - The data to search the maximum value
  • period - Running period to calculate for

Example

To return the maximum close over the last 50 periods:

SELECT datetime, ta_max(close, 50) FROM EURUSD_86400;

ta_min - Running Min ( as opposed to aggregate min )

ta_min ( float data, int period )

Arguments

  • data - The data to search the minimum value
  • period - Running period to calculate for

Example

To return the minimum close over the last 50 periods:

SELECT datetime, ta_min(close, 50) FROM EURUSD_86400;

ta_previous - Return results N periods ago

ta_previous ( float data, int period )

Arguments

  • 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 EURUSD_86400;

Other derived indicators

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 EURUSD_86400;

The MACD signal line is defined as an EMA of MACD

SELECT datetime, ta_ema(ema(close,12) - ta_ema(close,26), 9) AS MACD_SIGNAL FROM EURUSD_86400;

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.

The quickest way to achieve this is to use MySQL CAST:

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

CREATING NEW FUNCTIONS

The easiest way might be to copy one of the existing .c files to a different name and modify its implementation. The provided Makefile will pick up new .c files with no modification.

Contributing

The development repository for this project is hosted at github: http://github.com/joaocosta/lib_mysqludf_ta