Registered User
Joined: 10/7/2004
Posts: 4
|
How do I create a formula to calculate Std Deviation of a series? In Excel
this is done through the command STDEV. Please help as I am trying to
calculate the volatility of particular stocks over a certain time period.
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
There is an explanation of how this can be done here:
Modelling Bollinger Bands (& Standard Deviation) in a TC PCF
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 10/7/2004
Posts: 4
|
Thanks Bruce; I could not understand the posting enough to apply it to the problem I am trying to resolve. I am trying to build a PCF that does the following:
1. Calculate the Std deviation of a series of LN of (Closing price/previous days price)of an optionable stock for the last 20 days.
2. This standard deviation of logarithms is then annualized by multiplying the standard deviation by the square root of 365.
This gives us the volatility of the stock which can then be used to calculate probability
Hope you can help me construct this pcf
Thanks
BillyD
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
You may want to compare this to your Excel results. I think it is correct, but don't have anything to test it against:
19.105 * SQR((LOG(C / C1) ^ 2 + LOG(C1 / C2) ^ 2 + LOG(C2 / C3) ^ 2 + LOG(C3 / C4) ^ 2 + LOG(C4 / C5) ^ 2 + LOG(C5 / C6) ^ 2 + LOG(C6 / C7) ^ 2 + LOG(C7 / C8) ^ 2 + LOG(C8 / C9) ^ 2 + LOG(C9 / C10) ^ 2 + LOG(C10 / C11) ^ 2 + LOG(C11 / C12) ^ 2 + LOG(C12 / C13) ^ 2 + LOG(C13 / C14) ^ 2 + LOG(C14 / C15) ^ 2 + LOG(C15 / C16) ^ 2 + LOG(C16 / C17) ^ 2 + LOG(C17 / C18) ^ 2 + LOG(C18 / C19) ^ 2 + LOG(C19 / C20) ^ 2 - ((LOG(C / C1) + LOG(C1 / C2) + LOG(C2 / C3) + LOG(C3 / C4) + LOG(C4 / C5) + LOG(C5 / C6) + LOG(C6 / C7) + LOG(C7 / C8) + LOG(C8 / C9) + LOG(C9 / C10) + LOG(C10 / C11) + LOG(C11 / C12) + LOG(C12 / C13) + LOG(C13 / C14) + LOG(C14 / C15) + LOG(C15 / C16) + LOG(C16 / C17) + LOG(C17 / C18) + LOG(C18 / C19) + LOG(C19 / C20)) ^ 2) / 20) / 19)
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 1/1/2005
Posts: 2,645
|
Bruce,
Please notice that
LOG(C/C1)+LOG(C1/C2)+...+LOG(C19/C20)
can be replaced by
LOG(C/C20)
if desired.
Also, the "/19" in the PCF should be "/20".
Thanks,
Jim Murphy
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
bustermu,
Thanks for the optimization. I used Standard Deviation of Sample instead of Standard Deviation of Population since it is what TeleChart uses for its Bollinger Band calculations and was the denominator presented in the Tanstaafl post to which I'd referred BillyD.
Optimized SD of Sample Version:
19.105 * SQR((LOG(C / C1) ^ 2 + LOG(C1 / C2) ^ 2 + LOG(C2 / C3) ^ 2 + LOG(C3 / C4) ^ 2 + LOG(C4 / C5) ^ 2 + LOG(C5 / C6) ^ 2 + LOG(C6 / C7) ^ 2 + LOG(C7 / C8) ^ 2 + LOG(C8 / C9) ^ 2 + LOG(C9 / C10) ^ 2 + LOG(C10 / C11) ^ 2 + LOG(C11 / C12) ^ 2 + LOG(C12 / C13) ^ 2 + LOG(C13 / C14) ^ 2 + LOG(C14 / C15) ^ 2 + LOG(C15 / C16) ^ 2 + LOG(C16 / C17) ^ 2 + LOG(C17 / C18) ^ 2 + LOG(C18 / C19) ^ 2 + LOG(C19 / C20) ^ 2 - (LOG(C / C20) ^ 2) / 20) / 19)
Optimized SD of Population Version:
19.105 * SQR((LOG(C / C1) ^ 2 + LOG(C1 / C2) ^ 2 + LOG(C2 / C3) ^ 2 + LOG(C3 / C4) ^ 2 + LOG(C4 / C5) ^ 2 + LOG(C5 / C6) ^ 2 + LOG(C6 / C7) ^ 2 + LOG(C7 / C8) ^ 2 + LOG(C8 / C9) ^ 2 + LOG(C9 / C10) ^ 2 + LOG(C10 / C11) ^ 2 + LOG(C11 / C12) ^ 2 + LOG(C12 / C13) ^ 2 + LOG(C13 / C14) ^ 2 + LOG(C14 / C15) ^ 2 + LOG(C15 / C16) ^ 2 + LOG(C16 / C17) ^ 2 + LOG(C17 / C18) ^ 2 + LOG(C18 / C19) ^ 2 + LOG(C19 / C20) ^ 2 - (LOG(C / C20) ^ 2) / 20) / 20)
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 10/7/2004
Posts: 4
|
Thanks, I have tried the pcf for a stock against an excel model. The resulting values of voltality are close. Volatility by Excel .774970
Volatility by PCF using /20 is .745
I do not yet know if this difference is of significance. However looking at the PCF some anamolies might be causing the difference:
1. PCF uses the LOG function Vs the volatility model called for the LN or natural Log function.
2. Std deviation = Sqroot (sigma (xi-xm)squared/N)
where xi are the data values and xm is the mean not the average.
Any thoughts ??
BillyD
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
I could be missing something, but I'm pretty sure the LOG function in TeleChart is natural and that an arithmetic mean is a simple average. Anyway, I’ll do some exports to text to try and figure out what is causing the difference between the PCF and Excel results.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 10/7/2004
Posts: 799
Location: Duluth, GA
|
TC's LOG is in fact the natural logarithm.
I suspect that the problem you are running into is related to internal roundoff error ... TC uses single precision variables, and excel uses quad precision.
To reduce errors like that in TC, it is often worth trying to eliminate the power operator "^". This is easy to do in the StdDev calc's, since it is an even power of two.
That is, replace terms of the form:
LOG(C/C1)^2
with terms of the form:
LOG(C/C1)*LOG(C/C1)
Compare the results (I have not in this case, but have in many other cases) and you might find that you get more net precision from the latter form.
Jim Dean
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
BillyD,
My tests were not all that sophisticated, but I have compared the results of the Excel formula STDEVP(A1:A20)*365^0.5 (where LN(C1/C2):LN(C20/C21) are A1:A20 and C1:C21 being closing prices exported from TeleChart) against the Optimized SD of Population Version formula plotted as a Custom Indicator (multiplied by 1000 to produce enough decimal places for a comparison) in TeleChart. In every comparison I tried, the first 4, and usually 5, digits matched (I’m not saying it will be true for all symbols since I only tried 30). I am not sure why my Excel results seem to match and yours do not.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 1/15/2005
Posts: 60
|
How can I do this for 50 days?
Thanks
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
Try this:
19.105 * SQR((LOG(C / C1) ^ 2 + LOG(C1 / C2) ^ 2 + LOG(C2 / C3) ^ 2 + LOG(C3 / C4) ^ 2 + LOG(C4 / C5) ^ 2 + LOG(C5 / C6) ^ 2 + LOG(C6 / C7) ^ 2 + LOG(C7 / C8) ^ 2 + LOG(C8 / C9) ^ 2 + LOG(C9 / C10) ^ 2 + LOG(C10 / C11) ^ 2 + LOG(C11 / C12) ^ 2 + LOG(C12 / C13) ^ 2 + LOG(C13 / C14) ^ 2 + LOG(C14 / C15) ^ 2 + LOG(C15 / C16) ^ 2 + LOG(C16 / C17) ^ 2 + LOG(C17 / C18) ^ 2 + LOG(C18 / C19) ^ 2 + LOG(C19 / C20) ^ 2 + LOG(C20 / C21) ^ 2 + LOG(C21 / C22) ^ 2 + LOG(C22 / C23) ^ 2 + LOG(C23 / C24) ^ 2 + LOG(C24 / C25) ^ 2 + LOG(C25 / C26) ^ 2 + LOG(C26 / C27) ^ 2 + LOG(C27 / C28) ^ 2 + LOG(C28 / C29) ^ 2 + LOG(C29 / C30) ^ 2 + LOG(C30 / C31) ^ 2 + LOG(C31 / C32) ^ 2 + LOG(C32 / C33) ^ 2 + LOG(C33 / C34) ^ 2 + LOG(C34 / C35) ^ 2 + LOG(C35 / C36) ^ 2 + LOG(C36 / C37) ^ 2 + LOG(C37 / C38) ^ 2 + LOG(C38 / C39) ^ 2 + LOG(C39 / C40) ^ 2 + LOG(C40 / C41) ^ 2 + LOG(C41 / C42) ^ 2 + LOG(C42 / C43) ^ 2 + LOG(C43 / C44) ^ 2 + LOG(C44 / C45) ^ 2 + LOG(C45 / C46) ^ 2 + LOG(C46 / C47) ^ 2 + LOG(C47 / C48) ^ 2 + LOG(C48 / C49) ^ 2 + LOG(C49 / C50) ^ 2 - (LOG(C / C50) ^ 2) / 50) / 50)
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 1/28/2006
Posts: 291
|
Forgive my lack of depth in things mathematical Bruce, but ...
1. Is your 'natural logarithm' described as log [x] whereas the 'common' is log 10 [x]?
2. Can one design pcf's to function in logs other than base 10?
3. Can one design pcf's to run on intraday data? If so, I may be tempted to go back to platinum if #2 and #3 are possible.
Thanks for all your time and effort...have learned and am learning quite alot.
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
awshucks,
The LOG() Function in TeleChart is the Natural Logarithm. We do not have a built in function for a Base 10 Logarithmic function.
This means Personal Criteria Formulas will use the Natural Logarithm by default. You would have to alter the PCF to create a Logarithm using Base 10. LOG_Base10(C ) could be written as:
LOG(C ) / LOG(10)
PCFs always use the twenty-minute delayed Daily data. It is not possible to create PCFs that use the Streaming Real Time Data. It is possible to create Custom Indicators or Custom Percent True Indicators that use Streaming Real Time Data and sort by them however. You may wish to review the following:
Plotting Custom Indicators with Examples
Visually Backtesting Specific Symbols
Sorting price and indicators by their Actual Value
Real time indicator sorting (available at the Platinum level)
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 1/28/2006
Posts: 291
|
My thanks Bruce...I'll take this and run with it. Have a great weekend.
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
awshucks,
You're welcome.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 1/28/2006
Posts: 291
|
Hi Bruce, I'm baaa-aack. ;~)
It is interesting that the natural log was chosen over Base 10. I suppose it was done to molify those married to a constant linear universe and/or constant growth (e^x) and it gives you the time needed to achieve a certain mark. Its a simple enough thing to change the natural LOG into Base 10 function, at least in close approximation, and I have done so.
Now, I wish to translate that output (Base 10 function) into a different Base. Theoretically, how could I do so in Telechart?
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
awshucks,
You would use the same technique to convert the formula into a different base that you used to convert it to base ten. You would just use:
LOG(C ) / LOG(base)
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
|
Guest-1 |