| Welcome Guest, please sign in to participate in a discussion. | Search | Active Topics | |
Registered User
Joined: 10/7/2004
Posts: 286
|
The forumla at the bottom was posted here recently as a 20d Price/LReg20 R-Squared PCF. It differs from my spreadsheet RSquared number where =RSQ(prices for 20days,LReg20 values for 20 days)nets .792 while the below formulat nets .776 for symbol HHH. But more importantly their trends don't peak and reverse at the same times (about a four day difference?).
Any help in understanding and/or reconciling these differences would be appreaciated.
thanks.
jynkin
((((20 - 1) / 2) * AVGC20 - (C1 + 2 * C2 + 3 * C3 + 4 * C4 + 5 * C5 + 6 * C6 + 7 * C7 + 8 * C8 + 9 * C9 + 10 * C10 + 11 * C11 + 12 * C12 + 13 * C13 + 14 * C14 + 15 * C15 + 16 * C16 + 17 * C17 + 18 * C18 + 19 * C19 ) / 20) / SQR(((20 * 20 - 1) / 12) * ((C ^ 2 + C1 ^ 2 + C2 ^ 2 + C3 ^ 2 + C4 ^ 2 + C5 ^ 2 + C6 ^ 2 + C7 ^ 2 + C8 ^ 2 + C9 ^ 2 + C10 ^ 2 + C11 ^ 2 + C12 ^ 2 + C13 ^ 2 + C14 ^ 2 + C15 ^ 2 + C16 ^ 2 + C17 ^ 2 + C18 ^ 2 + C19 ^ 2 ) / 20 - AVGC20 ^ 2))) ^ 2
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
If you are using something similar to RSQ(A1:A20,B1:B20) in Excel, where A1:A20 is a range of prices, make sure B1:B20 is a range of consecutively numbered trading days. If B1:B20 is a range of dates, Excel will produce incorrect results because of weekends and market holidays. I've tested this using HHH with the supplied formula as a Custom Indicator and it seems to be the issue.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 10/7/2004
Posts: 286
|
Yes, I am using the RSQ(A1:A20,B1:B20) formulation and the B1:B20 range of LReg20 values are in a series not broken by weekends or holiday dates.
Wondering if there is a statistical population vs. sample formula variation?
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
I don't know what else to suggest. I've had time to test it using HHH. While there are minor value differences, the trends peak and reverse at the same times for me when B1:B20 is a range of consecutively numbered trading days. When I change B1:B20 to dates, I see the types of differences you seem to be describing. Can you give me the values of A1:A6 and B1:B6?
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 10/7/2004
Posts: 286
|
A1:A40
56.74
57.14
57.19
57.46
57.43
57.25
58.00
55.65
58.75
58.54
58.40
58.60
60.60
61.12
60.59
61.31
62.65
62.68
62.05
61.59
61.80
62.14
61.30
61.73
61.07
61.47
60.76
60.91
60.54
60.28
59.31
59.14
58.97
58.86
58.97
59.25
59.20
59.81
59.53
59.18
B1:B40
54.43
54.76
55.10
55.49
55.90
56.27
56.78
56.85
57.55
58.05
58.46
58.79
59.45
60.04
60.42
60.82
61.43
61.90
62.20
62.36
62.60
62.87
62.92
63.02
62.93
62.84
62.64
62.19
61.91
61.53
60.94
60.31
59.85
59.45
59.04
58.78
58.68
58.74
58.73
58.64
|
|
Registered User
Joined: 10/7/2004
Posts: 286
|
RSQ spreadsheet values from the two above series
0.87
0.87
0.85
0.85
0.81
0.78
0.73
0.67
0.60
0.49
0.34
0.22
0.33
0.49
0.57
0.63
0.73
0.78
0.79
0.79
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
OK. Here's the thread where I assume you are getting your formula:
Need help writing a PCF for r-squared
The formula supplied is for R-squared. R-squared measures how closely prices follow a Linear Regression Trendline.
If the formula in Excel is RSQ(A1:A20,B1:B20), the values for A1:A20 would be Prices and the values for B2:B20 would be consecutive numbers. Let's say A1:A20 are the following:
59.18
59.53
59.81
59.20
59.25
58.97
58.86
58.97
59.14
59.31
60.28
60.54
60.91
60.76
61.47
61.07
61.73
61.30
62.14
61.80
B1:B20 should be something like:
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1
For a range of dates (which aren't used in the RSQ() function):
9/2/2005
9/1/2005
8/31/2005
8/30/2005
8/29/2005
8/26/2005
8/25/2005
8/24/2005
8/23/2005
8/22/2005
8/19/2005
8/18/2005
8/17/2005
8/16/2005
8/15/2005
8/12/2005
8/11/2005
8/10/2005
8/9/2005
8/8/2005
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 10/7/2004
Posts: 286
|
Okay...My spreadsheet and PCF trends now peak at the same time like you said. I did not realize that B1:B20 was suppose to be a number series like 20, 19, 18...etc. The 20d linear regression calculation is built into the RSQ formula.
Thanks Bruce for the excellent help.
jynkin
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
You're welcome.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
|
Guest-1 |