general-reports
general-reports copied to clipboard
SMA200 SMA50 SMA10
I've creared SQL query for calculate (Simple Moving Average) SMA200 50 10 for AAPL
WITH d as (
WITH RECURSIVE
cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<365)
SELECT
'AAPL' s
, x
, date('now', '-'||x||' days') d
FROM cnt)
select d.x
, d.d date
, case when d.d= date(d.d, 'start of month') then d.d else '' end label
, (select value from STOCKHISTORY where DATE = date(d.d) and SYMBOL = d.s ) Value_
, (select value from STOCKHISTORY where DATE >= date(d.d, '-3 days') and DATE <= date(d.d) and SYMBOL = d.s and value is not null order by 1 desc limit 1 ) Value
, (select avg(value) from STOCKHISTORY where DATE <= d.d and DATE > date(d.d, '-50 days') and SYMBOL = d.s) avg50
, (select avg(value) from STOCKHISTORY where DATE <= d.d and DATE > date(d.d, '-200 days') and SYMBOL = d.s) avg200
from d
where x < 100
I need graph for this data like this one:
https://marketrealist.com/2019/10/amzn-aapl-msft-tech-stocks-averages-pre-earnings/
https://tobiasahlin.com/blog/chartjs-charts-to-get-you-started/#2-line-chart