Registered User
Joined: 2/26/2005
Posts: 115
|
Hi I need a trainer's help to codify the following formula in the SF version 4 I am still working in:
1. There are two symbols involved: the main symbol (S1) and a secondary symbol (S2), with the secondary symbol being selectable/changeable.
2. Sum of returns for S2 during those days when its own returns are ABOVE their average/ median (selectable) over a certain period (selectable)
3. Sum of returns for S2 during those days when its own returns are BELOW their average/ median (selectable) over a certain period (selectable)
4. Sum of returns for S1 during those days when S2 returns are above their average/median (selectable) over a certain period (selectable)
5. Sum of returns for S1 during those days when S2 returns are below their average/ median (selectable) over a certain period (selectable)
6. Ratio #4/#2
7. Ratio #5/#3
8. Calculate the expression: ((((#6-#7)^2*2)^0.5)*0.5)*((#6-#7)/abs(#6-#7))
I appreciate the help!
K
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
By "returns" do you mean the Price Percent Change over some Period of time or something else?
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 2/26/2005
Posts: 115
|
Sorry for the delay, Bruce--yes, Returns=price percent change
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
I can do the mean, but the median is beyond my ability to provide assistance. You would need to Drag and Drop a Price for Symbol Indicator into the Code tab of the RealCode Editor to create the first line of the following RealCode Indicator:
'# PfS = indicator.PriceforSymbol.2
'# Period = UserInput.Integer = 20
Static Tot As Single
Static PeriodPlus As Integer
Static PeriodMinus As Integer
If CurrentIndex > Period Then
Tot += (PfS.Last / PfS.Last(1) - _
PfS.Last(Period) / PfS.Last(PeriodPlus))
Else If isFirstBar Then
Tot = 0
PeriodPlus = Period + 1
PeriodMinus = Period - 1
Else
Tot += PfS.Last / PfS.Last(1)
End If
If CurrentIndex >= Period Then
Dim Sum(3) As Single
Dim Avg As Single = Tot / Period
For i As Integer = 0 To PeriodMinus
If PfS.Last(i) / PfS.Last(i + 1) > Avg Then
Sum(0) += 100 * (PfS.Last(i) / PfS.Last(i + 1) - 1)
Sum(2) += 100 * (Price.Last(i) / Price.Last(i + 1) - 1)
Else If PfS.Last(i) / PfS.Last(i + 1) < Avg Then
Sum(1) += 100 * (PfS.Last(i) / PfS.Last(i + 1) - 1)
Sum(3) += 100 * (Price.Last(i) / Price.Last(i + 1) - 1)
End If
Next
Dim Ratio(1) As Single
Ratio(0) = Sum(2) / Sum(0)
Ratio(1) = Sum(3) / Sum(1)
Plot = ((((Ratio(0) - Ratio(1)) ^ 2 * 2) ^ .5) * .5) * _
((Ratio(0) - Ratio(1)) / System.Math.Abs(Ratio(0) - Ratio(1)))
Else
Plot = Single.NaN
End If
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 2/26/2005
Posts: 115
|
Super Thanks!, Bruce.
I loaded and it seems to work, but I tested it on a couple of symbols and I am worried that it comes up with different results than I got on excel calculations. If you do not mind, I will forward you via Support an elementary excel spreadsheet that step by step shows all the calculations for you to check the code--it would be much easier for you to see what is involved. It's a simple example with just two symbols, SPY for S2 and IF for S1 (as they are referred to in my description). When I run IF through the code it gives me a -0.43 value while my spreadsheet indicates it should be +13.74, which is a big difference in both size and sign (which is very important for what I am computing).
Looking forward to your input and Many Thanks in Advance!
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
I'm sure I'm just misunderstanding part of your description. Looking at a spreadsheet should be quite helpful.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
The formulas in the 510 row (G510, H510, I510 & J510) in the code section of the spreadsheet actually seems to be adding up 380 lines instead of 126 (129 through 508).
That said, I think the primary reason it doesn't match is the RealCode compares each Percent Change during the Period to Average Percent Change during the same Period. This means a 126-Period version starts returning Values at 127-Bars and can make comparisons at the the second Bar (the first Bar with a Percent Change). This requires looping at each Bar to make the comparisons.
The spreadsheet compares the Percent Change at any given point to the Average Percent Change at that same point. This means the 127th Bar is the first Bar to get added to the sum and you need 126 more Bars to get to your first Sum (or whatever this is set to if there are different Periods for the Moving Average and the Sum). On the plus side, this eliminates looping back at each Bar. Since the Average doesn't change after the comparisons are made, you only need to compare the Average to the actual value once.
Changing to your method should probably create quicker RealCode, but the RealCode will also be more complicated and require more variables. I'm working on it, but probably won't get it done before Monday at the earliest.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
You would need to Drag and Drop a Price for Symbol Indicator into the Code tab of the RealCode Editor to create the first line of the following RealCode Indicator:
'# PfS = indicator.PriceforSymbol.2
'# AvgPeriod = UserInput.Integer = 126
'# SumPeriod = UserInput.Integer = 126
Static Percent As New System.Collections.Generic.List(Of Single)
Static Average As New System.Collections.Generic.List(Of Single)
Static S2Above As New System.Collections.Generic.List(Of Single)
Static S2Below As New System.Collections.Generic.List(Of Single)
Static S1Above As New System.Collections.Generic.List(Of Single)
Static S1Below As New System.Collections.Generic.List(Of Single)
Static Sum(3) As Single
If isFirstBar Then
Percent.Clear
Average.Clear
Average.Add(0)
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
For i As Integer = 0 To 3
Sum(i) = 0
Next
Plot = Single.NaN
Else
Percent.Add(100 * (PfS.Last / PfS.Last(1) - 1))
Average(Average.Count - 1) += Percent(Percent.Count - 1) / AvgPeriod
If Percent.Count >= AvgPeriod Then
If Percent(Percent.Count - 1) >= Average(Average.Count - 1) Then
S2Above.Add(Percent(Percent.Count - 1))
S1Above.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(0) += S2Above(S2Above.Count - 1)
Sum(2) += S1Above(S1Above.Count - 1)
Else
S2Below.Add(Percent(Percent.Count - 1))
S1Below.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(1) += S2Below(S2Below.Count - 1)
Sum(3) += S1Below(S1Below.Count - 1)
End If
If Average.Count >= SumPeriod Then
Dim RatioUp As Single = Sum(2) / Sum(0)
Dim RatioDn As Single = Sum(3) / Sum(1)
Plot = ((((RatioUp - RatioDn) ^ 2 * 2) ^ .5) * .5) * _
((RatioUp - RatioDn) / System.Math.Abs(RatioUp - RatioDn))
If Percent(0) >= Average(0) Then
Sum(0) -= S2Above(0)
Sum(2) -= S1Above(0)
S2Above.RemoveAt(0)
S1Above.RemoveAt(0)
Else
Sum(1) -= S2Below(0)
Sum(3) -= S1Below(0)
S2Below.RemoveAt(0)
S1Below.RemoveAt(0)
End If
Average.RemoveAt(0)
Else
Plot = Single.NaN
End If
Average.Add(Average(Average.Count - 1) - Percent(0) / AvgPeriod)
Percent.RemoveAt(0)
Else
Average(0) += Percent(Percent.Count - 1) / AvgPeriod
Plot = Single.NaN
End If
End If
If isLastBar Then
Percent.Clear
Average.Clear
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
End If
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 2/26/2005
Posts: 115
|
Hey Bruce, great work--many thanks! You are right that my spreadsheet formulas in the 510 row (G510, H510, I510 & J510) say that I am using averages over 126 days, but in reality they calculate 380 days. My mistake. I am not sure if in your code you ended up doing a 126 or a 380 average... I cannot tell... but instead I wanted to ask you to make this value a selectable parameter. In fact there should be two parameters that one can specify when bringing up the indicator:
the number of days in the average of the closes of S2 against which each daily close of S2 is compared to decide if it is above or below (NAME THIS "LOOKBACK")
and the number of days in the average of the sums (in rows 510) (NAME THIS "SPAN")
Can you make this modification in the code? And how would the final code look like?
Thanks!
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
There already are two userinputs. The one you call LOOKBACK is called AvgPeriod and the one you call SPAN is called SumPeriod. Here is a version using your variable names instead:
'# PfS = indicator.PriceforSymbol.2
'# LOOKBACK = UserInput.Integer = 126
'# SPAN = UserInput.Integer = 126
Static Percent As New System.Collections.Generic.List(Of Single)
Static Average As New System.Collections.Generic.List(Of Single)
Static S2Above As New System.Collections.Generic.List(Of Single)
Static S2Below As New System.Collections.Generic.List(Of Single)
Static S1Above As New System.Collections.Generic.List(Of Single)
Static S1Below As New System.Collections.Generic.List(Of Single)
Static Sum(3) As Single
If isFirstBar Then
Percent.Clear
Average.Clear
Average.Add(0)
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
For i As Integer = 0 To 3
Sum(i) = 0
Next
Plot = Single.NaN
Else
Percent.Add(100 * (PfS.Last / PfS.Last(1) - 1))
Average(Average.Count - 1) += Percent(Percent.Count - 1) / LOOKBACK
If Percent.Count >= LOOKBACK Then
If Percent(Percent.Count - 1) >= Average(Average.Count - 1) Then
S2Above.Add(Percent(Percent.Count - 1))
S1Above.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(0) += S2Above(S2Above.Count - 1)
Sum(2) += S1Above(S1Above.Count - 1)
Else
S2Below.Add(Percent(Percent.Count - 1))
S1Below.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(1) += S2Below(S2Below.Count - 1)
Sum(3) += S1Below(S1Below.Count - 1)
End If
If Average.Count >= SPAN Then
Dim RatioUp As Single = Sum(2) / Sum(0)
Dim RatioDn As Single = Sum(3) / Sum(1)
Plot = ((((RatioUp - RatioDn) ^ 2 * 2) ^ .5) * .5) * _
((RatioUp - RatioDn) / System.Math.Abs(RatioUp - RatioDn))
If Percent(0) >= Average(0) Then
Sum(0) -= S2Above(0)
Sum(2) -= S1Above(0)
S2Above.RemoveAt(0)
S1Above.RemoveAt(0)
Else
Sum(1) -= S2Below(0)
Sum(3) -= S1Below(0)
S2Below.RemoveAt(0)
S1Below.RemoveAt(0)
End If
Average.RemoveAt(0)
Else
Plot = Single.NaN
End If
Average.Add(Average(Average.Count - 1) - Percent(0) / LOOKBACK)
Percent.RemoveAt(0)
Else
Average(0) += Percent(Percent.Count - 1) / LOOKBACK
Plot = Single.NaN
End If
End If
If isLastBar Then
Percent.Clear
Average.Clear
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
End If
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 2/26/2005
Posts: 115
|
Bruce, I have had the time to play around with the indicator you coded above and it looks like it has some weird behavior--can you please recheck the coding?! I would appreciate it.
Here it is:
The LOOKBACK annd the SPAN periods should be independent, but I noticed that when the Lookback is set at 126, then the SPAN cannot be set at much smaller values, like 9 (try that on the XME--it does not plot any recent values)... Why?!
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
The issue appears to be that when the SPAN is smaller than LOOKBACK by a significant margin, it is possible for every Bar during the SPAN to either be Above or Below the Average. I've added checks for this to avoid the errors that would cause. It should not Plot anything when a division by zero would have otherwise occurred.
'# PfS = indicator.PriceforSymbol.2
'# LOOKBACK = UserInput.Integer = 126
'# SPAN = UserInput.Integer = 126
Static Percent As New System.Collections.Generic.List(Of Single)
Static Average As New System.Collections.Generic.List(Of Single)
Static S2Above As New System.Collections.Generic.List(Of Single)
Static S2Below As New System.Collections.Generic.List(Of Single)
Static S1Above As New System.Collections.Generic.List(Of Single)
Static S1Below As New System.Collections.Generic.List(Of Single)
Static Sum(3) As Single
If isFirstBar Then
Percent.Clear
Average.Clear
Average.Add(0)
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
For i As Integer = 0 To 3
Sum(i) = 0
Next
Plot = Single.NaN
Else
Percent.Add(100 * (PfS.Last / PfS.Last(1) - 1))
Average(Average.Count - 1) += Percent(Percent.Count - 1) / LOOKBACK
If Percent.Count >= LOOKBACK Then
If Percent(Percent.Count - 1) >= Average(Average.Count - 1) Then
S2Above.Add(Percent(Percent.Count - 1))
S1Above.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(0) += S2Above(S2Above.Count - 1)
Sum(2) += S1Above(S1Above.Count - 1)
Else
S2Below.Add(Percent(Percent.Count - 1))
S1Below.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(1) += S2Below(S2Below.Count - 1)
Sum(3) += S1Below(S1Below.Count - 1)
End If
If Average.Count >= SPAN Then
If Sum(0) <> 0 AndAlso Sum(1) <> 0 Then
Dim RatioUp As Single = Sum(2) / Sum(0)
Dim RatioDn As Single = Sum(3) / Sum(1)
If RatioUp <> RatioDn Then
Plot = ((((RatioUp - RatioDn) ^ 2 * 2) ^ .5) * .5) * _
((RatioUp - RatioDn) / System.Math.Abs(RatioUp - RatioDn))
Else
Plot = Single.NaN
End If
Else
Plot = Single.NaN
End If
If Percent(0) >= Average(0) Then
If S2Above.Count >= 1 Then
Sum(0) -= S2Above(0)
Sum(2) -= S1Above(0)
S2Above.RemoveAt(0)
S1Above.RemoveAt(0)
End If
Else
If S2Below.Count >= 1 Then
Sum(1) -= S2Below(0)
Sum(3) -= S1Below(0)
S2Below.RemoveAt(0)
S1Below.RemoveAt(0)
End If
End If
Average.RemoveAt(0)
Else
Plot = Single.NaN
End If
Average.Add(Average(Average.Count - 1) - Percent(0) / LOOKBACK)
Percent.RemoveAt(0)
Else
Average(0) += Percent(Percent.Count - 1) / LOOKBACK
Plot = Single.NaN
End If
End If
If isLastBar Then
Percent.Clear
Average.Clear
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
End If
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 2/26/2005
Posts: 115
|
I appreciate the rework! Outside some weird numbers I have little ability to verify if we are hitting the target, but let me ask you about a discrepancy: In the spreadsheet I sent you the IF symbol produces a value of 13.74 for a LOOKBACK = 126 and SPAN = 380 on 5/19/2011 and that number is nowhere near to the numbers that either your previous formula (-49.45) or the current re-worked formula (0.01) produce. Why can't we replicate the excel values?
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
I've noticed at least two differences between the RealCode and the spreadsheet. The spreadsheet multiplies the ratios by 100 and the ratios in the RealCode are reversed. I adjust the RealCode to match the spreadsheet in both regards below, but it still won't match.
The older adjusted prices from Yahoo for both SPY and IF in the spreadsheet do not match the prices in StockFinder. In some cases the adjusted prices are significantly different. That said, my attempts to plug StockFinder's prices into your spreadsheet didn't produce a match either (even after I figured out that the spreadsheet's setting were such that it required a manual update). I have no idea what the differences might be at this point. Creating an exact match appears to be beyond my abilities to help you.
'# PfS = indicator.PriceforSymbol.2
'# LOOKBACK = UserInput.Integer = 126
'# SPAN = UserInput.Integer = 380
Static Percent As New System.Collections.Generic.List(Of Single)
Static Average As New System.Collections.Generic.List(Of Single)
Static S2Above As New System.Collections.Generic.List(Of Single)
Static S2Below As New System.Collections.Generic.List(Of Single)
Static S1Above As New System.Collections.Generic.List(Of Single)
Static S1Below As New System.Collections.Generic.List(Of Single)
Static Sum(3) As Single
If isFirstBar Then
Percent.Clear
Average.Clear
Average.Add(0)
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
For i As Integer = 0 To 3
Sum(i) = 0
Next
Plot = Single.NaN
Else
Percent.Add(100 * (PfS.Last / PfS.Last(1) - 1))
If Percent.Count >= LOOKBACK Then
Average(Average.Count - 1) += Percent(Percent.Count - 1) / LOOKBACK
If Percent(Percent.Count - 1) >= Average(Average.Count - 1) Then
S2Above.Add(Percent(Percent.Count - 1))
S1Above.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(0) += S2Above(S2Above.Count - 1)
Sum(2) += S1Above(S1Above.Count - 1)
Else
S2Below.Add(Percent(Percent.Count - 1))
S1Below.Add(100 * (Price.Last / Price.Last(1) - 1))
Sum(1) += S2Below(S2Below.Count - 1)
Sum(3) += S1Below(S1Below.Count - 1)
End If
If Average.Count >= SPAN Then
If Sum(0) <> 0 AndAlso Sum(1) <> 0 Then
Dim RatioUp As Single = 100 * Sum(2) / Sum(0)
Dim RatioDn As Single = 100 * Sum(3) / Sum(1)
If RatioUp <> RatioDn Then
Plot = ((((RatioUp - RatioDn) ^ 2 * 2) ^ .5) * .5) _
* ((RatioUp - RatioDn) / System.Math.Abs(RatioUp - RatioDn))
Else
Plot = Single.NaN
End If
Else
Plot = Single.NaN
End If
If Percent(0) >= Average(0) Then
If S2Below.Count >= 1 Then
Sum(0) -= S2Above(0)
Sum(2) -= S1Above(0)
S2Above.RemoveAt(0)
S1Above.RemoveAt(0)
End If
Else
If S2Below.Count >= 1 Then
Sum(1) -= S2Below(0)
Sum(3) -= S1Below(0)
S2Below.RemoveAt(0)
S1Below.RemoveAt(0)
End If
End If
Average.RemoveAt(0)
Else
Plot = Single.NaN
End If
Average.Add(Average(Average.Count - 1) - Percent(0) / LOOKBACK)
Percent.RemoveAt(0)
Else
Average(0) += Percent(Percent.Count - 1) / LOOKBACK
Plot = Single.NaN
End If
End If
If isLastBar Then
Percent.Clear
Average.Clear
S2Above.Clear
S2Below.Clear
S1Above.Clear
S1Below.Clear
End If
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 2/26/2005
Posts: 115
|
Super thanks, Bruce, for your hard work in coding the spreadsheet--I am very thankful. In case this is of broader value, could you plug in the K or anybody from the developement team to help? I may reveal some problems/limitations with the program language... The type of relationships in the spreadsheet are central to many good tools and they should be within SF/TCD2000's capability rangerange.
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
No. While they might help with StockFinder 5 RealCode under certain circumstances, they aren't going to help with a StockFinder 4 RealCode Indicator. We don't offer a programming service. Any RealCode provided is for educational purposes only.
The only reason the RealCode is so complicated is because we are doing it in StockFinder 4. The language has expanded significantly in StockFinder 5 and I would probably write much different RealCode which takes advantage of the changes. I'm doing something wrong when I'm manually creating functions which already exist as part of the RealCode in StockFinder 5, I just can't figure out what.
I have created a block diagram based version which work in StockFinder 4 and matches the results of the spreadsheet when it is using StockFinder's data. You should be able to Open an attached Indicator directly into a running copy of StockFinder (and save it from within StockFinder if desired).Attachments:
kostas_gr52197.sfInd - 10 KB, downloaded 522 time(s).
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
|
Guest-1 |