Skip to content

Instantly share code, notes, and snippets.

@trylks
Created January 31, 2012 19:52
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save trylks/1712542 to your computer and use it in GitHub Desktop.
Here is a brief pl/pgsql code to calculate the MACD indicator from a table storing daily candles. It's not finished and help is welcome, either wrt efficiency or wrt correctness, for some reason EMA formulas online are calculated with alpha [0,1] whereas
create table candles(ticker text, day date, value real, primary key (ticker, day));
drop table macdtuple cascade;
CREATE TABLE macdtuple (ticker real, ema1val real, ema2val real, signalval real);
create or replace function macd_tuple_func(state macdtuple, inval real, ticker real, alpha1 real, alpha2 real, alpha3 real) returns macdtuple as $previousmacd$
declare
x macdtuple;
begin
if (state is null OR ticker <> ticker(state)) then
x.ticker := ticker;
x.ema1val := inval;
x.ema2val := inval;
x.signalval := 0;
return x;
else
x.ticker := ticker;
x.ema1val := alpha1 * inval + (1 - alpha1) * ema1val(state);
x.ema2val := alpha2 * inval + (1 - alpha2) * ema2val(state);
x.signalval := alpha3 * (x.ema1val-x.ema2val) + (1 - alpha3) * signalval(state);
return x;
end if;
end
$previousmacd$ language plpgsql;
create or replace function macd_histogram_final_func(state macdtuple) returns real as $transmacdh$
begin
return ema1val(state)-ema2val(state)-signalval(state);
end
$transmacdh$ language plpgsql;
create aggregate macd_tuple(real, real, real, real, real) (sfunc = macd_tuple_func, stype = macdtuple, finalfunc=macd_histogram_final_func);
select id_empresa, cierre, macd_tuple(cierre, id_empresa, 0.1, 0.2, 0.05) over (order by id_empresa, tiempo), tiempo from vela;
select ticker, value, macd_tuple(value, ticker, 0.1, 0.2, 0.05) over (order by ticker, day), day from candles;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment