Вычисление максимальной «просадки инвестиционного портфеля» (drawdown) в ORACLE с помощью SQL

23 Июл
2012

Для тех, кто не знает, что такое «просадка инвестиционного портфеля», в двух словах это: снижение стоимости чистых активов относительно исторически наивысшей точки стоимости.

Рабочих примеров вычисления drawdown множество, но мне ни разу не попадалось решение, выполненное на SQL. Программисты делают это непосредственно в коде их любимого языка.

Теперь сам алгоритм.

Допустим, мы имеем данные о колебаниях цен на акции в течение какого-то промежутка времени, сохраненные в таблице stocks. Формат таблицы такой: дата, цена. Для простоты в таблице хранятся данные всего одной акции. Вот они:

AS_OF_DATE PRICE
1/1/2012 -23
1/2/2012 24
1/4/2012 -78
1/6/2012 23
1/7/2012 11
1/8/2012 2
1/9/2012 -2
1/10/2012 -2
1/11/2012 64

Задача: вычислить максимальный drawdown за последний год. Для начала выбираем данные для указанного промежутка:

SELECT as_of_date, price
FROM stocks
WHERE as_of_date BETWEEN TRUNC(SYSDATE, ‘YEAR’) AND SYSDATE

Ищем все пики цен, т.е. такие дни, до и сразу после которых цена увеличилась. Используем для этого две аналитические фунцкии ORACLE:

SELECT as_of_date, price
FROM (
SELECT as_of_date,
LAG(price) OVER (ORDER BY as_of_date) AS price_prev,
price,
LEAD(price) OVER (ORDER BY as_of_date) AS price_next
FROM (
SELECT as_of_date, price
FROM stocks
WHERE as_of_date BETWEEN TRUNC(SYSDATE, ‘YEAR’) AND SYSDATE
)
)
WHERE (price >= price_prev OR price_prev IS NULL) AND (price >= price_next OR price_next IS NULL)

Исполнив этот запрос получаем следующий результат:

AS_OF_DATE PRICE
1/2/2012 24
1/6/2012 23
1/11/2012 64

Теперь выбираем «бегущий максимум», чтобы отсечь пики, меньше предыдущего, так как они являются частью его:

SELECT as_of_date, MAX(price) OVER (ORDER BY as_of_date) AS run_max
FROM (
SELECT as_of_date, price
FROM (
SELECT as_of_date,
LAG(price) OVER (ORDER BY as_of_date) AS price_prev,
price,
LEAD(price) OVER (ORDER BY as_of_date) AS price_next
FROM (
SELECT as_of_date, price
FROM stocks
WHERE as_of_date BETWEEN TRUNC(SYSDATE, ‘YEAR’) AND SYSDATE
)
)
WHERE (price >= price_prev OR price_prev IS NULL) AND (price >= price_next OR price_next IS NULL)
)

После чего имеем такую выборку:

AS_OF_DATE RUN_MAX
1/2/2012 24
1/6/2012 24
1/11/2012 64

Избавляемся от дубликатов, вычисляя первый день текущего пика:

SELECT MIN(as_of_date) AS as_of_date, run_max
FROM (
SELECT as_of_date, MAX(price) OVER (ORDER BY as_of_date) AS run_max
FROM (
SELECT as_of_date, price
FROM (
SELECT as_of_date,
LAG(price) OVER (ORDER BY as_of_date) AS price_prev,
price,
LEAD(price) OVER (ORDER BY as_of_date) AS price_next
FROM (
SELECT as_of_date, price
FROM stocks
WHERE as_of_date BETWEEN TRUNC(SYSDATE, ‘YEAR’) AND SYSDATE
)
)
WHERE (price >= price_prev OR price_prev IS NULL) AND (price >= price_next OR price_next IS NULL)
)
)
GROUP BY run_max

Затем выстраивем интервалы времени, на которые нужно найти провалы цены:

SELECT as_of_date, NVL(LEAD(as_of_date) OVER (ORDER BY as_of_date), TO_DATE(SYSDATE)) AS next_date, run_max
FROM (
SELECT MIN(as_of_date) AS as_of_date, run_max
FROM (
SELECT as_of_date, MAX(price) OVER (ORDER BY as_of_date) AS run_max
FROM (
SELECT as_of_date, price
FROM (
SELECT as_of_date,
LAG(price) OVER (ORDER BY as_of_date) AS price_prev,
price,
LEAD(price) OVER (ORDER BY as_of_date) AS price_next
FROM (
SELECT as_of_date, price
FROM stocks
WHERE as_of_date BETWEEN TRUNC(SYSDATE, ‘YEAR’) AND SYSDATE
)
)
WHERE (price >= price_prev OR price_prev IS NULL) AND (price >= price_next OR price_next IS NULL)
)
)
GROUP BY run_max
)

Вот они:

AS_OF_DATE NEXT_DATE RUN_MAX
1/2/2012 1/11/2012 24
1/11/2012 7/20/2012 64

В конечном итоге, снова обращаемся к исходной таблице, чтобы найти провалы согласно вычисленных промежутков. Связываем два результата и получаем искомую величину (-102):

SELECT -MAX(run_max — price) AS dd_max
FROM (
SELECT run_max, MIN(price) AS price
FROM stocks, (
SELECT as_of_date, NVL(LEAD(as_of_date) OVER (ORDER BY as_of_date), TO_DATE(SYSDATE)) AS next_date, run_max
FROM (
SELECT MIN(as_of_date) AS as_of_date, run_max
FROM (
SELECT as_of_date, MAX(price) OVER (ORDER BY as_of_date) AS run_max
FROM (
SELECT as_of_date, price
FROM (
SELECT as_of_date,
LAG(price) OVER (ORDER BY as_of_date) AS price_prev,
price,
LEAD(price) OVER (ORDER BY as_of_date) AS price_next
FROM (
SELECT as_of_date, price
FROM stocks
WHERE as_of_date BETWEEN TRUNC(SYSDATE, ‘YEAR’) AND SYSDATE
)
)
WHERE (price >= price_prev OR price_prev IS NULL) AND (price >= price_next OR price_next IS NULL)
)
)
GROUP BY run_max
)
) max_points
WHERE stocks.as_of_date BETWEEN max_points.as_of_date AND max_points.next_date
GROUP BY run_max
)
По материалам Хабрахабр.



загрузка...

Комментарии:

Наверх