This tutorial is mainly to design how to use the shortest equation to calculate the KD value in any time range and the principle behind it.
The K value formula is:
[Close(today) - Min (low 9 day)] / [Max (high 9 day) - Min (low 9 day)] *100/ 3 + K(Yesterday) 2/3
The equation in F2 is: (E2-min(D2:D10))/(max(C2:C10)-min(D2:D10))*100/3+F3*2/3
Note: This equation needs to be pulled down manually. If you want to achieve automatic calculation, please continue to see the following teaching.
Since Max and Min cannot be used in Arrayformula, a more powerful function Query is required in this case.
Step 1: K value calculation
1. Enter the equation in H1: Query(C2:C,"Select *")
2.Enter this equation in I1: Query(C2:C,"Select * offset 1"), Enter this equation in I1:: Query(C2:C,"Select * offset 2"),Repeat 8 times.
3. In this way, there are 9 days of data for a single row:
4. But in this way, the length of each column is different, so'Limit' must be added, and the H1 equation is:
H1 = QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 0")
5. The 9-day array is:
H1 = {QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 0"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 1"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 2"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 3"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 4"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 5"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 6"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 7"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 8")}
6. At this time, just use transpose to get the following array:
7. Just use Query Max(Col1), Max(Col2), Max(Col3)... to find the corresponding Max (high 9 day) every day, the question is how many Col...
8. To find out how many Col, you have to find out how many columns of data High has, and enter the equation in H10:
H10=ArrayFormula(if(len(C10:C),ROW(C10:C)-Row(C10)+1,""))
9. Then combined into the query syntax of Query, the H10 equation is changed to:
H10=ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-Row(C10)+1&"),",""))
10. Then use Join to change it to a string, and the H10 equation change to:
H10=Join(,ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-Row(C10)+1&"),","")))
11. Then use Join to change it to a string, and the H10 equation to change it to:
H10=REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")
among them: REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","") is query string
12.Combine the two and enter the equation in G2 to get the Max (high 9 day) corresponding to each day:
G2=query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(C2:C,"Select *"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 1"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 2"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 3"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 4"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 5"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 6"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 7"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 8"),Count(C2:C)-8,1)},"Select* ")),"Select "®EXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2")
13. Min (low 9 day) is also found in the same way, and the final K value formula is:
G2=ArrayFormula(iferror((100*(E2:E-query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(D2:D,"Select *"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 1"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 2"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 3"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 4"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 5"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 6"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 7"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 8"),Count(D2:D)-8,1)},"Select* ")),"Select "®EXREPLACE(join("",ArrayFormula(if(len(C10:C),"Min(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2"))/(query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(C2:C,"Select *"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 1"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 2"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 3"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 4"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 5"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 6"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 7"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 8"),Count(C2:C)-8,1)},"Select* ")),"Select "®EXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2")-query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(D2:D,"Select *"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 1"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 2"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 3"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 4"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 5"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 6"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 7"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 8"),Count(D2:D)-8,1)},"Select* ")),"Select "®EXREPLACE(join("",ArrayFormula(if(len(C10:C),"Min(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2"))/3+F3:F*2/3),""))
The results of the two algorithms are consistent
Step 2: D value calculation
1. The D value calculation is much simpler, enter the equation in H2:
H2=ArrayFormula(if(G2:G="","",G2:G/3+H3:H*2/3))
The following is a demonstration to compare the differences between the two K value calculations:
沒有留言:
發佈留言