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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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