Time Series Trend Detection

Question

I have a psql database with time series values ​​for different stocks. I want to find if the value of the shares increases, say, 50% in the last 45 days. I want to detect such sudden changes in the curve. Now I am focused on adding value. Mostly quadratic curves, although it would be nice to also find linear / logarithmic magnification.

I will receive sudden drops soon. Any pointers to how I detect such patterns on a curve? Can this be done in sql? I am also open to learning any tools or languages ​​of mathematical analysis.

Answer 1

To get this information, there are many approaches that you can take. If this report is run every once in a while, then you may have an SQL query that joins the table by itself at an earlier date. Assuming you have a date, stock_directory, and price as fields, the query might look something like this:

SELECT base.date, base.stock_ticker, ((base.price - old.price) / old.price) as gain FROM stocks as base LEFT OUTER JOIN stocks as old ON base.stock_ticker = old.stock_ticker AND base.date = old.date + cast('45 days' as interval) 

This will give you a table with the date, stock ticker and how much the price is from the day 45 days ago. With this, you can use it as a subquery, alias it with a WITH clause, or do other work.

If you did not search only for a ratio over a period of time, you can use the same technique to get daily average values, and with this data to find a moving average.