Using Oracle Database for Technical Market Analysis
This article is about Oracle Database, PL / SQL, SQL, MATCH_RECOGNIZE, MODEL clause, aggregate and pipelined functions.
The technical analysis (TA) of the markets was used as a functional area. First, a small superficial introduction to trade in the markets, then - calculations.
Technical analysis - a set of tools to predict the likely price changes based on patterns of price changes in the past. Theoretically, Technical Analysis is applicable in any market. But Technical analysis is most prevalent in highly liquid free markets, for example, on the stock exchanges.
')
Many SQL statements, procedures, and schedules have been developed. Part - below, fully code - on GitHub at the link at the bottom of the article.
Technical Market Indicators (TIR) ​​- additional charts to price charts, formed on the basis of recalculation of the values ​​contained in the basic price chart . Usually, these are different types of averaging (the next point of the chart is calculated as the average value of a certain number of previous price values, for example, a moving average), relations (the next point is the result of comparing a certain number of previous prices - their difference derived from the change over a period), or lags ( delays). Indicators clearly show some non-obvious information contained in the statistics of price changes, and can form recommendations about trade orders - BUY / SELL. Indicators have at least one variable parameter, the value of which will change the result. For the opening of real transactions are usually used several indicators in the complex, plus additional information, at the discretion of the trader.
One of the technical analysis postulates is “history repeats itself”: Market participants behave in approximately the same way in similar circumstances, forming a similar dynamics of price changes. And it is natural to assume that the market behavior in the future will basically repeat the patterns that have emerged in the past. Following this statement, the investor can choose from the possible parameters of TIR those that have best proven themselves in previous periods.
This article builds a graph of trading performance when using each indicator from the value of the TIR parameter, it is in a two-dimensional representation. But in fact, I will build in a three-dimensional representation in order to evaluate also the effect of the delay in executing an order on the result, so the parameter will be the X axis (abscissa), the delay will be the Y axis (ordinate), and the result will be the Z axis (applicate). This is some attempt to assess the influence of “slippage”, which, unfortunately, always happen. Instead of a real “slippage” along the Y axis, I postpone the execution of the BUY / SELL order for a period of 1 to 5 periods.
If a global maximum stands out on this graph and the graph itself looks like a hat (in general, this is called “normal distribution”, but it assumes strict symmetry about the vertical axis), the cone, pyramid are also suitable - this means that TIR can pick up a certain value of the parameter , which will show the highest result, and with this indicator you can try to trade. If the performance graph, depending on the parameter, will resemble a “palisade” - it is impossible to choose the optimal parameter value and there is no point in using this TIR in trade.
It can be said that in this article I count only the “percent hit” of the indicators. The task, to estimate how much you can “earn”, was not solved by me.
Successful completion of the described test is one of the necessary, but not sufficient factors for effective trading.
For information ... In contrast to Technical Analysis, Fundamental Analysis (FA) is a term for designating methods for predicting the market (exchange) value of instruments based on an analysis of financial and operating performance indicators.
The “internal value” of FA in most cases does not coincide with the price of the company's shares, which is determined by the ratio of supply and demand in the stock market. Investors using FA in their activities are primarily interested in situations where the “intrinsic value” of a company's shares exceeds the price of shares on an exchange. Such stocks are considered undervalued, so their price will rise, and they are potential investment targets.
One of the most well-known investors using Fundamental Analysis is Warren Buffett.
Watch the trailer: https://www.youtube.com/watch?v=SqE8fnvmV1Y
Thus, we have two diametrically opposite approaches - TA and FA .
FA is usually interested in long-term investors, TA - short- and medium-term and is used for speculative transactions, when the trader does not interest the trading item itself.
And the goals of this article are two ...
In addition to the above reasoning about TA and FA, I wanted to explore and show the capabilities of the Oracle Database to perform calculations of Technical Market Indicators .
I present these opportunities to the readers.
If you decide to repeat my calculations - download the code from GitHub at the link at the bottom of the article. Code tested on version 12.2.0.1.
First create shared objects. The first are the tables and view. In the same file below - the transaction modeling package.
The second step is to create functions that perform TIR calculations.
The third step is the calculations.
All functions for calculating all TIRs will return a cursor with the following fields: STOCK_NAME, ADATE, ACLOSE (daily closing price), AACTION (order to sell / buy)
The package contains three table modeling functions that take the cursor from the TIR calculation functions described above, offset (delay in execution of the order, lag), and initial capital, the default is 1000 USD. Package functions are called like this:
where XXXXXXXX is the name of TIR .
All functions calculate the result of trade subject to complete reinvestment of profits, excluding transaction costs and taxes, and excluding inflation and discounting.
The first function
The second function
In the last line of each instrument you can see the result of trading for each instrument, as well as in the previous function, if the investor is “in securities”, the sale is simulated to calculate the balance in foreign currency
The third function,
Calculations are given for such markets and indices: S & P500, NYSE, Brent, BTCUSD, EURUSD.
The first 4 are downloaded from Yahoo Finance, the last from another source. Calculation results for courses from other sources may vary.
Please note that the course periods of each instrument differ, namely:
Therefore, it is impossible to compare the achieved profitability of tools with each other, but you can compare the profitability of each of the tools when using different indicators.
The file for loading (SQL * Loader) can also be taken from GitHub via the link at the bottom of the article.
There are at least three basic types of moving averages:
They differ in the weights of their constituents. For linear moving average weights are equal, for exponential and linearly weighted weights decrease as the component moves away from the right edge of the window - exponentially or linearly.
The linear moving average is the easiest to calculate. In Oracle, this function is
The linear moving average has disadvantages. First, such averages slow to respond to market reversals. Since many values ​​are averaged, each of which is given equal weight, the average reaction often occurs after several timeframes after the price reversal of the asset.
Also, the linear moving average is not very effective because it responds to the signal twice: when the indicator entered the sliding window and left it. The rest react only to the input of the indicator and smoothly deduce it as it moves from the right edge of the window to the left.
All three types of moving averages are used in TIR calculations. For the exponential and linearly weighted in this article developed the aggregate functions of
Calculation of EMA and WMA, without recursion or model, alone by analytics, in the Oracle Database, apparently, is impossible.
But more reservations about moving averages:
For almost every indicator, several calculation methods will be given: CALC - calculation using PL / SQL code, SIMP - calculation by one operator or RECU - calculation by recursion, AGRF - calculation using the aggregate function, MODE - calculation by modelka.
There are reasons for developing multiple methods. First, by calculating the TIR by several methods and comparing the indicators, if the indicators are the same, you can be sure that the calculations were carried out correctly (taking into account different methods of rounding and processing the NULL and “0” values). In this article I will compare the hashes of samples of different methods, so match up to a bit and the same processing by all algorithms is guaranteed.
I suppose, in Oracle, it is necessary to begin the development of TIR with the SIMP method - calculation by one operator. When this is done, the orakloid has a plan and calculation algorithm in its head, and it can be easily shifted to PL / SQL or to another procedural language.
I note that the method of calculating CALC on PL / SQL here turns out to be faster than the SIMP method (by one operator) in the event that the entire calculation can be performed in one pass through the cursor. But if for the calculation it is necessary to form temporary tables or collections, or more than 1 pass on the cursor, I suppose the “one operator” method will turn out to be faster and less resource-intensive.
For all methods, including the SIMP method (“one operator”), I will place the operators in functions, so that the calculation can be called with a parameter to select the optimal value.
Calculations are given for seven TIR : Intersection of the moving average (EMASIMPLE), “Golden” and “Deadly crosses (CROSSES), Balance volume (OBV), Keltner Channel (KELTNER), Price and Volume Trend (PVT), Arms Lightness Indicator (EMV ) Commodity Channel Index (CCI).
For the seven indicators of these two articles there will be one parameter “averaging window size”, and the second parameter will be the shift (lag). The shift says how many bars to postpone the execution of the order BUY / SELL - how many bars to shift the closing price (all orders are made at the closing price of the bar). This is similar to “slippage”, but it is not exactly “slipping.” Slippage is usually not in favor of the client, and our lag can be both in favor of the client and in favor. However, the use of modeling with a lag of 1 to 5 bars for some indicators shows that slippage has a significant effect on the result. And for some indicators, lag and slippage are not so important.
In the SIMP (“one operator”) and RECU (“recursion”)
Detailed descriptions of all TIRs can be found in Wikipedia or in Robert Colby's book, Encyclopedia of Technical Indicators of the Market.
The method of intersection of exponential moving averages is the simplest TIR .
The method involves: buying (opening a long position) if the price value crosses its exponential moving average (ESS) from bottom to top; selling (closing a long position) if the price crosses its ESS from top to bottom.
Short positions, as well as margin trading in general, are not considered in this article.
In the future, this indicator can be used as a benchmark for comparison with others. A comparison with this TIR is better than a comparison with the Buy and Hold strategy, because this strategy is not profitable in falling markets.
The only parameter TIR - the length of the moving average.
Calculate with PL / SQL
Calculation by one recursive operator
Calculation using the phrase MODEL
Calculation using the aggregate function
Let's compare the calculation results with one parameter:
All hashes must match for all four methods.
If the hashes do not converge, you can figure out exactly where the discrepancy arose with such an operator (substitute the names of the functions to be compared):
Selection of parameters
The TIR itself uses one parameter; for this and for all the other TIRs, I change it in the range from 1 to 200, but to calculate the three-dimensional image for the dependency and on the lag too, we will enter the second parameter, which will vary from 1 to 5.
The operator opens 200 * 5 = 1000 cursors, so you may need to change the Oracle OPEN_CURSORS parameter
The query below performs a Cartesian product of a table with numbers from 1 to 200, with a table with numbers from 1 to 5, and Cartesian multiplies all this by calling the table function
By some further manipulations in MATLAB, we next obtain a 200 * 5 matrix, where in the cells of the matrix there will be the total capital value for each value of each of the two parameters. Next in MATLAB build a three-dimensional image.
All calculations in this article are performed slowly, up to 20 minutes, this is due to the discovery of a large number of cursors.
I developed a faster simulation method with one cursor, without opening 1000 cursors, but it is so voluminous that it would take half of the article. Therefore, I will not bring him here.
The result of the simulation of the moving average intersection (all graphs are clickable):
The second line of graphs is the same as the first line, but the graphs are slightly rotated, the graphs with different lags are arranged one after the other. This allows you to evaluate the effect of lag on the result.
In general, the indicator is not very sensitive to lags. For the S & P500 and NYSE markets, the TIR parameter should be chosen the more the better. For the Brent market, it is around 25. In the other two markets, there is no correlation between profitability and a parameter.
In the implementation on Oracle, this indicator is very similar to the previous one, only here two moving averages are used, and not one. Therefore, I will give only one version of the calculation.
The Wiki describes the Ishimoku Indicator. This is a complex indicator. "Crosses" are one of the components. But the indicator is described on the wiki badly, in particular, note that the lines Tenkan and Kijun are described in completely different words, although in essence they are one and the same, but with different periods.
In the book by Robert Colby, this indicator is also not described.
Japanese analysts call the crossing of the medium when the short-term average crosses the long-term from the bottom up, the Golden Cross (Golden Cross), and the opposite situation, when the short-term moving average crosses the long-term from the top to the bottom - the Dead cross (Dead Cross).
The author drew attention to the fact that this indicator is described in the article “Futures for American oil formed a“ deadly cross ”” and began to google its description and use.
This indicator is considered serious in the stock market, in part because it rarely signals.
The most commonly used are 50 and 200 period moving averages.
When modeling the performance below, we will take a period of a long moving average equal to four times a short moving average, and simulate a short length from 1 to 200 days (from 4 to 800 days for a long).
The code for calculating on GitHub.
The indicator is not very sensitive to lags. For the S & P500 market, there are highs at 48 (192 for the long SS) and 98 days (392 for the long SS). Note that the first maximum is very close to the numbers 50x200. It can be assumed that if this option is selected by 1 and 2 less than other market participants, you can try to beat them on this indicator alone.
On the NYSE 4 maximum. The indicator does not work in the Brent and BTCUSD markets.
For the EURUSD market, too, the parameter should be chosen a little less than 50 for a short CC. But the indicator does not give profit in this market. It can only be used as an additional indicator.
The OBV indicator is a cumulative moving average of trading volume, taken with a plus sign in the case of a growing market and with a minus sign in the case of a falling one.
Here, this method of interpreting the indicator values ​​will be used: buy when OBV crosses its moving average from bottom to top, sell when OBV crosses its moving average from top to bottom.
Read more on Wiki or Colby.
In the S & P500 market, the indicator is very sensitive to delays (lags), but you can try to choose the parameter according to the principle “the more the better”, some profit is achieved. In the NYSE market, profitability cannot be achieved. In the Brent market, you can select a parameter value from 20 to 100. There is no clear linear relationship in the BTCUSD market, but it is not advisable to choose a parameter value less than 40. For the EURUSD market, the parameter value should be chosen “the more, the better”, but profitability cannot be achieved.
, . .
.
, .
: , , , .
There are highs for the S & P500 and NYSE markets. In the Brent market, there is no dependence of the result on the parameter, and all trade is at a loss. There are highs on the BTCUSD market, but they are narrow and difficult to hit. In the EURUSD market, the dependence of the result on the parameter is.
PVT .
: , PVT -, , PVT -.
. .
S&P500 « , », NYSE , Brent 50, BTCUSD , 1-2, , 50-, EURUSD .
EMV — , . , .
: , EMV , , EMV .
.
S&P, NYSE , Brent . BTCUSD EURUSD .
CCI — , . .
: , CCI 100, , CCI 100.
.
S&P500, NYSE, Brent . BTCUSD ( ), . EURUSD .
GitHub
, , ( , Yahoo Finance), , , ( , 20 ).
: . . .
Additionally:
Three more indicators were analyzed that are not included in the article. But they could not get any result. Perhaps their study will continue.
To perform calculations and complex modeling of TIR and financial activities in general on the Oracle Database is very convenient.
The technical analysis (TA) of the markets was used as a functional area. First, a small superficial introduction to trade in the markets, then - calculations.
Technical analysis - a set of tools to predict the likely price changes based on patterns of price changes in the past. Theoretically, Technical Analysis is applicable in any market. But Technical analysis is most prevalent in highly liquid free markets, for example, on the stock exchanges.
')
Many SQL statements, procedures, and schedules have been developed. Part - below, fully code - on GitHub at the link at the bottom of the article.
Technical Market Indicators (TIR) ​​- additional charts to price charts, formed on the basis of recalculation of the values ​​contained in the basic price chart . Usually, these are different types of averaging (the next point of the chart is calculated as the average value of a certain number of previous price values, for example, a moving average), relations (the next point is the result of comparing a certain number of previous prices - their difference derived from the change over a period), or lags ( delays). Indicators clearly show some non-obvious information contained in the statistics of price changes, and can form recommendations about trade orders - BUY / SELL. Indicators have at least one variable parameter, the value of which will change the result. For the opening of real transactions are usually used several indicators in the complex, plus additional information, at the discretion of the trader.
One of the technical analysis postulates is “history repeats itself”: Market participants behave in approximately the same way in similar circumstances, forming a similar dynamics of price changes. And it is natural to assume that the market behavior in the future will basically repeat the patterns that have emerged in the past. Following this statement, the investor can choose from the possible parameters of TIR those that have best proven themselves in previous periods.
This article builds a graph of trading performance when using each indicator from the value of the TIR parameter, it is in a two-dimensional representation. But in fact, I will build in a three-dimensional representation in order to evaluate also the effect of the delay in executing an order on the result, so the parameter will be the X axis (abscissa), the delay will be the Y axis (ordinate), and the result will be the Z axis (applicate). This is some attempt to assess the influence of “slippage”, which, unfortunately, always happen. Instead of a real “slippage” along the Y axis, I postpone the execution of the BUY / SELL order for a period of 1 to 5 periods.
If a global maximum stands out on this graph and the graph itself looks like a hat (in general, this is called “normal distribution”, but it assumes strict symmetry about the vertical axis), the cone, pyramid are also suitable - this means that TIR can pick up a certain value of the parameter , which will show the highest result, and with this indicator you can try to trade. If the performance graph, depending on the parameter, will resemble a “palisade” - it is impossible to choose the optimal parameter value and there is no point in using this TIR in trade.
It can be said that in this article I count only the “percent hit” of the indicators. The task, to estimate how much you can “earn”, was not solved by me.
Successful completion of the described test is one of the necessary, but not sufficient factors for effective trading.
For information ... In contrast to Technical Analysis, Fundamental Analysis (FA) is a term for designating methods for predicting the market (exchange) value of instruments based on an analysis of financial and operating performance indicators.
The “internal value” of FA in most cases does not coincide with the price of the company's shares, which is determined by the ratio of supply and demand in the stock market. Investors using FA in their activities are primarily interested in situations where the “intrinsic value” of a company's shares exceeds the price of shares on an exchange. Such stocks are considered undervalued, so their price will rise, and they are potential investment targets.
One of the most well-known investors using Fundamental Analysis is Warren Buffett.
Watch the trailer: https://www.youtube.com/watch?v=SqE8fnvmV1Y
Thus, we have two diametrically opposite approaches - TA and FA .
FA is usually interested in long-term investors, TA - short- and medium-term and is used for speculative transactions, when the trader does not interest the trading item itself.
And the goals of this article are two ...
In addition to the above reasoning about TA and FA, I wanted to explore and show the capabilities of the Oracle Database to perform calculations of Technical Market Indicators .
I present these opportunities to the readers.
If you decide to repeat my calculations - download the code from GitHub at the link at the bottom of the article. Code tested on version 12.2.0.1.
First create shared objects. The first are the tables and view. In the same file below - the transaction modeling package.
The second step is to create functions that perform TIR calculations.
The third step is the calculations.
All functions for calculating all TIRs will return a cursor with the following fields: STOCK_NAME, ADATE, ACLOSE (daily closing price), AACTION (order to sell / buy)
The package contains three table modeling functions that take the cursor from the TIR calculation functions described above, offset (delay in execution of the order, lag), and initial capital, the default is 1000 USD. Package functions are called like this:
select * from HABR_TRADEMODELLING_P.TRADE_LOG (cursor (select STOCK_NAME, ADATE, ACLOSE, AACTION from table (HABR_MARKETINDEXES_XXXXXXXX_F_CALC (10))), p_lag => 1) order by 1, 2; select * from HABR_TRADEMODELLING_P.CALC_ACTIONS (cursor (select STOCK_NAME, ADATE, ACLOSE, AACTION from table (HABR_MARKETINDEXES_XXXXXXXX_F_CALC (10))), p_lag => 1); select * from HABR_TRADEMODELLING_P.CALC_ACTIONS_TOTALS (cursor (select STOCK_NAME, ADATE, ACLOSE, AACTION from table (HABR_MARKETINDEXES_XXXXXXXX_F_CALC (10))), p_lag => 1); where XXXXXXXX is the name of TIR .
All functions calculate the result of trade subject to complete reinvestment of profits, excluding transaction costs and taxes, and excluding inflation and discounting.
The first function
TRADE_LOG forms a simplified trades transaction log. The function allows you to trace the entire chain of transactions. If at the end of the period the investor is “in securities”, to calculate the balance in a currency (financial result), the function simulates the sale of all securities at the latest closing price and forms the corresponding mark in the IN_STOCK field.The second function
CALC_ACTIONS returns the same columns as the called TIR calculation cursor, plus adds the following columns: AACTION_LAG (order with offset), BALANCE_CURRENCY (trader’s account balance in cash), BALANCE_STOCK (number of tools in open positions). Depending on the value of the AACTION_LAG field, buying or selling at the closing price is simulated, and the currency balance and the amount of open positions change accordingly.In the last line of each instrument you can see the result of trading for each instrument, as well as in the previous function, if the investor is “in securities”, the sale is simulated to calculate the balance in foreign currency
The third function,
CALC_ACTIONS_TOTALS does the same thing as the second, but returns only the last line — the result of trading for each instrument. It will be used in the simulation.Download data
Calculations are given for such markets and indices: S & P500, NYSE, Brent, BTCUSD, EURUSD.
The first 4 are downloaded from Yahoo Finance, the last from another source. Calculation results for courses from other sources may vary.
Please note that the course periods of each instrument differ, namely:
- S & P500 - 03/01/1950 ... 29/01/2019, 69 years;
- NYSE - 31/12/1965 ... 22/03/2019, 54;
- Brent - 17/05/1991… 06/02/2019, 28 years old;
- BTCUSD - 16/07/2010 ... 29/01/2019, 9 years;
- EURUSD - 16/02/2001… 27/05/2019, 18 years old.
Therefore, it is impossible to compare the achieved profitability of tools with each other, but you can compare the profitability of each of the tools when using different indicators.
The file for loading (SQL * Loader) can also be taken from GitHub via the link at the bottom of the article.
Moving averages
There are at least three basic types of moving averages:
- linear (Simple Moving Average, SMA),
- exponential (Exponential Moving Average, EMA) and
- linear weighted (Weighted Moving Average, WMA).
They differ in the weights of their constituents. For linear moving average weights are equal, for exponential and linearly weighted weights decrease as the component moves away from the right edge of the window - exponentially or linearly.
The linear moving average is the easiest to calculate. In Oracle, this function is
avg (VALUE) over (partition by STOCK_NAME order by ADATE rows between 9 preceding and current row) - a moving average with an averaging window size of 10 values.The linear moving average has disadvantages. First, such averages slow to respond to market reversals. Since many values ​​are averaged, each of which is given equal weight, the average reaction often occurs after several timeframes after the price reversal of the asset.
Also, the linear moving average is not very effective because it responds to the signal twice: when the indicator entered the sliding window and left it. The rest react only to the input of the indicator and smoothly deduce it as it moves from the right edge of the window to the left.
All three types of moving averages are used in TIR calculations. For the exponential and linearly weighted in this article developed the aggregate functions of
EMA and WMA , which are used in analytical form. In addition, these moving averages can be calculated by recursion or a model (the phrase MODEL ).Calculation of EMA and WMA, without recursion or model, alone by analytics, in the Oracle Database, apparently, is impossible.
But more reservations about moving averages:
- the shorter the averaging period and the more sensitively the method reacts to reversals, the more it gives false signals;
- the shorter the averaging period, the more signals are generated, the more transaction overhead, which can be very significant.
Technical Market Indicators
For almost every indicator, several calculation methods will be given: CALC - calculation using PL / SQL code, SIMP - calculation by one operator or RECU - calculation by recursion, AGRF - calculation using the aggregate function, MODE - calculation by modelka.
There are reasons for developing multiple methods. First, by calculating the TIR by several methods and comparing the indicators, if the indicators are the same, you can be sure that the calculations were carried out correctly (taking into account different methods of rounding and processing the NULL and “0” values). In this article I will compare the hashes of samples of different methods, so match up to a bit and the same processing by all algorithms is guaranteed.
I suppose, in Oracle, it is necessary to begin the development of TIR with the SIMP method - calculation by one operator. When this is done, the orakloid has a plan and calculation algorithm in its head, and it can be easily shifted to PL / SQL or to another procedural language.
I note that the method of calculating CALC on PL / SQL here turns out to be faster than the SIMP method (by one operator) in the event that the entire calculation can be performed in one pass through the cursor. But if for the calculation it is necessary to form temporary tables or collections, or more than 1 pass on the cursor, I suppose the “one operator” method will turn out to be faster and less resource-intensive.
For all methods, including the SIMP method (“one operator”), I will place the operators in functions, so that the calculation can be called with a parameter to select the optimal value.
Calculations are given for seven TIR : Intersection of the moving average (EMASIMPLE), “Golden” and “Deadly crosses (CROSSES), Balance volume (OBV), Keltner Channel (KELTNER), Price and Volume Trend (PVT), Arms Lightness Indicator (EMV ) Commodity Channel Index (CCI).
For the seven indicators of these two articles there will be one parameter “averaging window size”, and the second parameter will be the shift (lag). The shift says how many bars to postpone the execution of the order BUY / SELL - how many bars to shift the closing price (all orders are made at the closing price of the bar). This is similar to “slippage”, but it is not exactly “slipping.” Slippage is usually not in favor of the client, and our lag can be both in favor of the client and in favor. However, the use of modeling with a lag of 1 to 5 bars for some indicators shows that slippage has a significant effect on the result. And for some indicators, lag and slippage are not so important.
In the SIMP (“one operator”) and RECU (“recursion”)
MATCH_RECOGNIZE , the phrase MATCH_RECOGNIZE actively used to generate the BUY / SELL trading signal based on the entry / exit of the calculated TIR to a specified range or its behavior relative to its moving average.Detailed descriptions of all TIRs can be found in Wikipedia or in Robert Colby's book, Encyclopedia of Technical Indicators of the Market.
Intersection of exponential moving average
The method of intersection of exponential moving averages is the simplest TIR .
The method involves: buying (opening a long position) if the price value crosses its exponential moving average (ESS) from bottom to top; selling (closing a long position) if the price crosses its ESS from top to bottom.
Short positions, as well as margin trading in general, are not considered in this article.
In the future, this indicator can be used as a benchmark for comparison with others. A comparison with this TIR is better than a comparison with the Buy and Hold strategy, because this strategy is not profitable in falling markets.
The only parameter TIR - the length of the moving average.
Calculate with PL / SQL
create or replace function HABR_MARKETINDEXES_EMASIMPLE_F_CALC (p_averaging_window_width integer) return HABR_MARKETINDEXES_RESULT_LIST_T pipelined is l_result HABR_MARKETINDEXES_RESULT_LIST_T; EMA number; prev_EMA number; prev_TYPICAL_PRICE number; retval HABR_MARKETINDEXES_RESULT_T := HABR_MARKETINDEXES_RESULT_T (null, null, null, null, null, null, null, null, null); prev_STOCK_NAME varchar2(256); l_alpha number; begin l_alpha := 2 / (p_averaging_window_width + 1); for c1 in (select STOCK_NAME, ADATE, TYPICAL_PRICE, ACLOSE from LOAD_YAHOO_V order by 1, 2) loop retval.ADATE := c1.ADATE; retval.ACLOSE := c1.ACLOSE; if prev_STOCK_NAME is null or prev_STOCK_NAME <> c1.STOCK_NAME then retval.STOCK_NAME := c1.STOCK_NAME; EMA := c1.TYPICAL_PRICE; prev_EMA := null; else EMA := round (c1.TYPICAL_PRICE * l_alpha + EMA * (1 - l_alpha), 20); end if; if prev_TYPICAL_PRICE < prev_EMA and c1.TYPICAL_PRICE > EMA then retval.AACTION := 'BUY'; elsif prev_TYPICAL_PRICE > prev_EMA and c1.TYPICAL_PRICE < EMA then retval.AACTION := 'SELL'; else retval.AACTION := null; end if; retval.IND_VALUE := EMA; pipe row (retval); prev_STOCK_NAME := c1.STOCK_NAME; prev_EMA := EMA; prev_TYPICAL_PRICE := c1.TYPICAL_PRICE; end loop; end; Calculation by one recursive operator
create or replace function HABR_MARKETINDEXES_EMASIMPLE_F_RECU (p_averaging_window_width integer) return HABR_MARKETINDEXES_RESULT_LIST_T is l_result HABR_MARKETINDEXES_RESULT_LIST_T; begin with T1 (STOCK_NAME, ADATE, TYPICAL_PRICE, EMA, ACLOSE, RN) as (select STOCK_NAME, ADATE, TYPICAL_PRICE, round (TYPICAL_PRICE, 20), ACLOSE, RN from LOAD_YAHOO_V where RN = 1 union all select b.STOCK_NAME , b.ADATE , b.TYPICAL_PRICE , round (b.TYPICAL_PRICE * 2 / (p_averaging_window_width + 1) + a.EMA * (1 - 2 / (p_averaging_window_width + 1)), 20) , b.ACLOSE , b.RN from T1 a, LOAD_YAHOO_V b where b.RN = a.RN + 1 and b.STOCK_NAME = a.STOCK_NAME) select HABR_MARKETINDEXES_RESULT_T (STOCK_NAME, ADATE, ACLOSE, EMA, null, null, null, null, AACTION) bulk collect into l_result from T1 match_recognize (partition by STOCK_NAME order by ADATE measures classifier() as AACTION all rows per match with unmatched rows pattern (BUY+ | SELL+) define BUY as (prev (TYPICAL_PRICE) < prev (EMA) and TYPICAL_PRICE > EMA) , SELL as (prev (TYPICAL_PRICE) > prev (EMA) and TYPICAL_PRICE < EMA) ) MR; return l_result; end; Calculation using the phrase MODEL
Function text HABR_MARKETINDEXES_EMASIMPLE_F_MODE
create or replace function HABR_MARKETINDEXES_EMASIMPLE_F_MODE (p_averaging_window_width integer) return HABR_MARKETINDEXES_RESULT_LIST_T is l_result HABR_MARKETINDEXES_RESULT_LIST_T; begin with T1 as (select * from LOAD_YAHOO_V model dimension by (STOCK_NAME, RN) measures (ADATE, TYPICAL_PRICE, ACLOSE, to_number(null) as EMA) rules (EMA[any, any] = round (TYPICAL_PRICE [cv(), cv()] * 2 / (p_averaging_window_width + 1) + nvl(EMA [cv(), cv() - 1], TYPICAL_PRICE [cv(), cv()]) * (1 - 2 / (p_averaging_window_width + 1)), 20))) , T2 as (select STOCK_NAME, ADATE, ACLOSE , TYPICAL_PRICE, LAG (TYPICAL_PRICE) over (partition by STOCK_NAME order by ADATE) as PREV_TYPICAL_PRICE , EMA, lag (EMA) over (partition by STOCK_NAME order by ADATE) as PREV_EMA from T1) select HABR_MARKETINDEXES_RESULT_T (STOCK_NAME, ADATE, ACLOSE, EMA, null, null, null, null , case when prev_TYPICAL_PRICE < prev_EMA and TYPICAL_PRICE > EMA then 'BUY' when prev_TYPICAL_PRICE > prev_EMA and TYPICAL_PRICE < EMA then 'SELL' end) bulk collect into l_result from T2 order by STOCK_NAME, ADATE; return l_result; end; Calculation using the aggregate function
EMA aggregate text and HABR_MARKETINDEXES_EMASIMPLE_F_AGRF function
create or replace type EMA_DATA_T as object (AVALUE number, AVERAGING_WINDOW integer); create or replace type EMA_IMPL_T as object ( l_window_width integer, l_ema number, static function ODCIAggregateInitialize (sctx in out EMA_IMPL_T) return number, member function ODCIAggregateIterate (self in out EMA_IMPL_T, value in EMA_DATA_T) return number, member function ODCIAggregateMerge (self in out EMA_IMPL_T, ctx2 in EMA_IMPL_T) return number, member function ODCIAggregateTerminate (self in EMA_IMPL_T, returnValue out number, flags in number) return number ); create or replace type body EMA_IMPL_T is static function ODCIAggregateInitialize (sctx in out EMA_IMPL_T) return number is begin sctx := EMA_IMPL_T (null, null); return ODCIConst.Success; end; member function ODCIAggregateIterate (self in out EMA_IMPL_T, value in EMA_DATA_T) return number is begin if value.AVALUE is not null then if l_window_width is null then l_window_width := value.AVERAGING_WINDOW; self.l_ema := value.AVALUE; else self.l_ema := round (value.AVALUE * 2 / (l_window_width + 1) + self.l_ema * (1 - 2 / (l_window_width + 1)), 20); end if; end if; return ODCIConst.Success; end; member function ODCIAggregateMerge(self in out EMA_IMPL_T, ctx2 in EMA_IMPL_T) return number is begin return ODCIConst.Error; end; member function ODCIAggregateTerminate(self in EMA_IMPL_T, returnValue out number, flags in number) return number is begin returnValue := self.l_ema; return ODCIConst.Success; end; end; create or replace function EMA (input EMA_DATA_T) return number aggregate using EMA_IMPL_T; create or replace function HABR_MARKETINDEXES_EMASIMPLE_F_AGRF (p_averaging_window_width integer) return HABR_MARKETINDEXES_RESULT_LIST_T is l_result HABR_MARKETINDEXES_RESULT_LIST_T; begin with T1 as (select STOCK_NAME, ADATE, TYPICAL_PRICE, ACLOSE , round (EMA (EMA_DATA_T (TYPICAL_PRICE, p_averaging_window_width)) over (partition by STOCK_NAME order by ADATE), 20) as EMA from LOAD_YAHOO_V) select HABR_MARKETINDEXES_RESULT_T (STOCK_NAME, ADATE, ACLOSE, EMA, null, null, null, null, AACTION) bulk collect into l_result from T1 match_recognize (partition by STOCK_NAME order by ADATE measures classifier() as AACTION all rows per match with unmatched rows pattern (BUY+ | SELL+) define BUY as (prev (TYPICAL_PRICE) < prev (EMA) and TYPICAL_PRICE > EMA) , SELL as (prev (TYPICAL_PRICE) > prev (EMA) and TYPICAL_PRICE < EMA) ) MR; return l_result; end; Let's compare the calculation results with one parameter:
select COLUMN_VALUE as ALG, dbms_sqlhash.gethash (COLUMN_VALUE, 2) as RECORDSET_HASH from table (sys.odcivarchar2list ('select * from table (HABR_MARKETINDEXES_EMASIMPLE_F_CALC (15)) order by 1, 2' , 'select * from table (HABR_MARKETINDEXES_EMASIMPLE_F_RECU (15)) order by 1, 2' , 'select * from table (HABR_MARKETINDEXES_EMASIMPLE_F_MODE (15)) order by 1, 2' , 'select * from table (HABR_MARKETINDEXES_EMASIMPLE_F_AGRF (15)) order by 1, 2')); All hashes must match for all four methods.
If the hashes do not converge, you can figure out exactly where the discrepancy arose with such an operator (substitute the names of the functions to be compared):
select coalesce (a.STOCK_NAME, b.STOCK_NAME) as STOCK_NAME, coalesce (a.ADATE, b.ADATE) as ADATE , a.ACLOSE as CALC_ACLOSE, b.ACLOSE as AGRF_CLOSE , a.IND_VALUE as CALC_EMA, b.IND_VALUE as AGRF_EMA , a.AACTION as CALC_AACTION, b.AACTION as AGRF_AACTION from table (HABR_MARKETINDEXES_EMASIMPLE_F_CALC (15)) a full outer join table (HABR_MARKETINDEXES_EMASIMPLE_F_AGRF (15)) b on a.STOCK_NAME = b.STOCK_NAME and a.ADATE = b.ADATE --where sys_op_map_nonnull (a.ACLOSE) <> sys_op_map_nonnull (b.ACLOSE) -- or sys_op_map_nonnull (a.IND_VALUE) <> sys_op_map_nonnull (b.IND_VALUE) -- or sys_op_map_nonnull (a.AACTION) <> sys_op_map_nonnull (b.AACTION) order by 1, 2; ; Selection of parameters
The TIR itself uses one parameter; for this and for all the other TIRs, I change it in the range from 1 to 200, but to calculate the three-dimensional image for the dependency and on the lag too, we will enter the second parameter, which will vary from 1 to 5.
The operator opens 200 * 5 = 1000 cursors, so you may need to change the Oracle OPEN_CURSORS parameter
The query below performs a Cartesian product of a table with numbers from 1 to 200, with a table with numbers from 1 to 5, and Cartesian multiplies all this by calling the table function
HABR_TRADEMODELLING_P.CALC_ACTIONS_TOTALS .By some further manipulations in MATLAB, we next obtain a 200 * 5 matrix, where in the cells of the matrix there will be the total capital value for each value of each of the two parameters. Next in MATLAB build a three-dimensional image.
rollback; delete HABR_MARKETINDEXES_PARMSEL_RESULTS where INDICATOR_NAME = 'HABR_MARKETINDEXES_EMASIMPLE_F_CALC'; commit; insert into HABR_MARKETINDEXES_PARMSEL_RESULTS (INDICATOR_NAME, PARM1, PARM2, STOCK_NAME, ADATE_MIN, ADATE_MAX, DEALS_COUNT, BALANCE_RESULT, DEALS_PROFIT_AMOUNT, DEALS_LOSS_AMOUNT, DEALS_PROFIT_COUNT, DEALS_LOSS_COUNT, IN_STOCK) with TP1 as (select rownum as PARM1 from dual connect by level <= &&AVERAGING_INTERVAL) , TP2 as (select rownum as PARM2 from dual connect by level <= &&LAG_MODELLING_DEPTH) select --+ parallel(8) 'HABR_MARKETINDEXES_EMASIMPLE_F_CALC', PARM1, PARM2, STOCK_NAME, ADATE_MIN, ADATE_MAX, DEALS_COUNT, BALANCE_RESULT, DEALS_PROFIT_AMOUNT, DEALS_LOSS_AMOUNT, DEALS_PROFIT_COUNT, DEALS_LOSS_COUNT, IN_STOCK from TP1 cross join TP2 cross join table (HABR_TRADEMODELLING_P.CALC_ACTIONS_TOTALS (cursor (select STOCK_NAME, ADATE, ACLOSE, AACTION from table (HABR_MARKETINDEXES_EMASIMPLE_F_CALC (PARM1))), PARM2)) ; commit; All calculations in this article are performed slowly, up to 20 minutes, this is due to the discovery of a large number of cursors.
I developed a faster simulation method with one cursor, without opening 1000 cursors, but it is so voluminous that it would take half of the article. Therefore, I will not bring him here.
The result of the simulation of the moving average intersection (all graphs are clickable):
The second line of graphs is the same as the first line, but the graphs are slightly rotated, the graphs with different lags are arranged one after the other. This allows you to evaluate the effect of lag on the result.
In general, the indicator is not very sensitive to lags. For the S & P500 and NYSE markets, the TIR parameter should be chosen the more the better. For the Brent market, it is around 25. In the other two markets, there is no correlation between profitability and a parameter.
Deadly and Golden Crosses
In the implementation on Oracle, this indicator is very similar to the previous one, only here two moving averages are used, and not one. Therefore, I will give only one version of the calculation.
The Wiki describes the Ishimoku Indicator. This is a complex indicator. "Crosses" are one of the components. But the indicator is described on the wiki badly, in particular, note that the lines Tenkan and Kijun are described in completely different words, although in essence they are one and the same, but with different periods.
In the book by Robert Colby, this indicator is also not described.
Japanese analysts call the crossing of the medium when the short-term average crosses the long-term from the bottom up, the Golden Cross (Golden Cross), and the opposite situation, when the short-term moving average crosses the long-term from the top to the bottom - the Dead cross (Dead Cross).
The author drew attention to the fact that this indicator is described in the article “Futures for American oil formed a“ deadly cross ”” and began to google its description and use.
This indicator is considered serious in the stock market, in part because it rarely signals.
The most commonly used are 50 and 200 period moving averages.
When modeling the performance below, we will take a period of a long moving average equal to four times a short moving average, and simulate a short length from 1 to 200 days (from 4 to 800 days for a long).
The code for calculating on GitHub.
The indicator is not very sensitive to lags. For the S & P500 market, there are highs at 48 (192 for the long SS) and 98 days (392 for the long SS). Note that the first maximum is very close to the numbers 50x200. It can be assumed that if this option is selected by 1 and 2 less than other market participants, you can try to beat them on this indicator alone.
On the NYSE 4 maximum. The indicator does not work in the Brent and BTCUSD markets.
For the EURUSD market, too, the parameter should be chosen a little less than 50 for a short CC. But the indicator does not give profit in this market. It can only be used as an additional indicator.
Balance Volume, Equilibrium Volume, On-Balance Volume (OBV)
The OBV indicator is a cumulative moving average of trading volume, taken with a plus sign in the case of a growing market and with a minus sign in the case of a falling one.
Here, this method of interpreting the indicator values ​​will be used: buy when OBV crosses its moving average from bottom to top, sell when OBV crosses its moving average from top to bottom.
Read more on Wiki or Colby.
In the S & P500 market, the indicator is very sensitive to delays (lags), but you can try to choose the parameter according to the principle “the more the better”, some profit is achieved. In the NYSE market, profitability cannot be achieved. In the Brent market, you can select a parameter value from 20 to 100. There is no clear linear relationship in the BTCUSD market, but it is not advisable to choose a parameter value less than 40. For the EURUSD market, the parameter value should be chosen “the more, the better”, but profitability cannot be achieved.
Keltner Channel, Keltner Channel
, . .
.
, .
: , , , .
There are highs for the S & P500 and NYSE markets. In the Brent market, there is no dependence of the result on the parameter, and all trade is at a loss. There are highs on the BTCUSD market, but they are narrow and difficult to hit. In the EURUSD market, the dependence of the result on the parameter is.
Trend of price and volume, Trend of price and volume, Price – Volume Trend, PVT
PVT .
: , PVT -, , PVT -.
. .
S&P500 « , », NYSE , Brent 50, BTCUSD , 1-2, , 50-, EURUSD .
, , Arms' Ease of Movement Value, EMV
EMV — , . , .
: , EMV , , EMV .
.
S&P, NYSE , Brent . BTCUSD EURUSD .
, ommodity channel index, CCI
CCI — , . .
: , CCI 100, , CCI 100.
.
S&P500, NYSE, Brent . BTCUSD ( ), . EURUSD .
GitHub
, , ( , Yahoo Finance), , , ( , 20 ).
: . . .
Additionally:
Three more indicators were analyzed that are not included in the article. But they could not get any result. Perhaps their study will continue.
To perform calculations and complex modeling of TIR and financial activities in general on the Oracle Database is very convenient.
Source: https://habr.com/ru/post/455050/
All Articles