Parabolic SAR in EXCEL

krapdad

Junior member
10 0
Has anyone successfully implemented a PSAR indicator in excel?

I have trawled the net and think i understand the formula but simply cannot get it correct in excel for current backtesting.

Trying to do it without a macro which I guess needs formulas in several columns for extreme high, extreme low, current acceleration figure etc.

If anyone has done this, an attached excel file or pointers as to how the columns and formulas might be arranged would be very much appreciated.
 

krapdad

Junior member
10 0
Why reinvent the wheel? It's in the free TA-Lib EXCEL add-in:

http://ta-lib.org/index.html

Just download the ta-lib-0.4.0-msvc.zip file from their site and look for the XLL file in the EXCEL directory of the ZIP file.

Thanks, I have tried this before spent days trying to get it to work.

Just downloaded it again I get the file into the folder with other add ins and the template comes up allowing me to put the parameters in but TA-Lib does not appear on the addin ribon and I get #N/A in the cells. Tried entering as array formula and other stuff but as a noob I have never really got to grips with macros.

I did once get a macro function working but because it is inserted several hundred thousand times down the column it took hours to calculate wheras if I can get the normal formulas in it only takes about 10 secs. I also do thousands of repeated calcs with varying parameters.

Usually I am able to derive formulas from the wealth of info on this site:
http://www.gummy-stuff.org/
A truely fantastic source for excel formuals and info, but unfortunateley the one indicator not on theis site is the PSAR.

Has anyone done it without a macro?
 

rharmelink

Junior member
34 3
Array-entered formulas are actually pretty easy when you get used to them. For example, if:

-- Daily high prices are in the range E4:E27
-- Daily low prices are in the range F4:F27
-- Desired Acceleration (or Step) Factor is in cell G1
-- Desired Max Acceleration (or Step) Factor is in cell G2

...then all you need to do is:

-- Highlight range G4:G27
-- Edit this formula into the active cell of the range:

=TA_SAR(E4:E27,F4:F27,G1,G2)

-- Hit Ctrl+Shift+Enter instead of Enter to enter the formula

Cell G4 will contain #N/A (because you need more than one day's values to assign PSAR), but G5:G27 should contain valid PSAR values.

And you can change the parameter values in cells G1 and G2 to alter how PSAR is calculated.

And it is very fast. On over 10,000 data points, my recalculation time upon changing one of the input factors in under a second.
 

krapdad

Junior member
10 0
Thanks again, I followed your instructions and the array formula is working. I really must spend more time reading up on these, can you believe i have manually programmed every MA under the sun from simple to Hull together with ADX and bollinger into excel, all available through TA lib as array formula I suspect.

Calc time is only about a second, the last one I managed that took hours I think must have had some itterative calc withinn it.

Only one outstanding issue, that is through inserting various size arrays I heve determined that 65000 lines appears to be the max, any more returns the #value error in all cells. do you think the function programme has a limit?
 

rharmelink

Junior member
34 3
On the TA-Lib forum, there is a mention of "does not support Excel 2007 row/column limits" -- so that's probably why you have the issue at 65000 lines.
 

krapdad

Junior member
10 0
That makes sense, probably the 64k line limit of excel 2003.

Not a major problem for me now, I can easily copy a second array below the first one, i am currently working on 7 years of FTSE 10min data which equates to just over 90,000 lines.

Thanks again for taking the time to help me with this one.
 

amateur

Newbie
2 0
Hi,
Thanks very much for the formula.
I did everything right but still got #name? in all the rows.
0.02
0.2
Date Time Open High Low step factor
03/14/10 22:00 1.01795 1.01795 1.01672 #NAME?
03/14/10 23:00 1.01697 1.01738 1.01639 #NAME?
03/15/10 0:00 1.0166 1.01738 1.01628 #NAME?
03/15/10 1:00 1.01668 1.01739 1.01633 #NAME?
03/15/10 2:00 1.01733 1.01759 1.01698 #NAME?
03/15/10 3:00 1.01733 1.01792 1.01718 #NAME?
03/15/10 4:00 1.01787 1.01839 1.01757 #NAME?

Could you guide me where I am going wrong.

Thanks very much




Array-entered formulas are actually pretty easy when you get used to them. For example, if:

-- Daily high prices are in the range E4:E27
-- Daily low prices are in the range F4:F27
-- Desired Acceleration (or Step) Factor is in cell G1
-- Desired Max Acceleration (or Step) Factor is in cell G2

...then all you need to do is:

-- Highlight range G4:G27
-- Edit this formula into the active cell of the range:

=TA_SAR(E4:E27,F4:F27,G1,G2)

-- Hit Ctrl+Shift+Enter instead of Enter to enter the formula

Cell G4 will contain #N/A (because you need more than one day's values to assign PSAR), but G5:G27 should contain valid PSAR values.

And you can change the parameter values in cells G1 and G2 to alter how PSAR is calculated.

And it is very fast. On over 10,000 data points, my recalculation time upon changing one of the input factors in under a second.
 

rharmelink

Junior member
34 3
Sounds like you didn't download and activate the TA-Lib add-in. The TA_SAR() function is not an EXCEL function, but part of that add-in.

Thanks very much for the formula.
I did everything right but still got #name? in all the rows.

Could you guide me where I am going wrong.

Thanks very much
 

A Dashing Blade

Experienced member
1,373 170
#NAME error means Excel is telling you "I don't know what you've asked me to do"
Almost certainly in your case you havn't loaded the TA-Lib addin.
 

stepture

Newbie
1 0
Hi Rharmelink,

Thanks for your tips and I can use TA_LIB now. How do I use AddIns function such as TA_SAR in a macro? Could you please give me a reference VBA code just for your example: TA_SAR(E4:E27,F4:F27,G1,G2)?

Many thanks.

Array-entered formulas are actually pretty easy when you get used to them. For example, if:

-- Daily high prices are in the range E4:E27
-- Daily low prices are in the range F4:F27
-- Desired Acceleration (or Step) Factor is in cell G1
-- Desired Max Acceleration (or Step) Factor is in cell G2

...then all you need to do is:

-- Highlight range G4:G27
-- Edit this formula into the active cell of the range:

=TA_SAR(E4:E27,F4:F27,G1,G2)

-- Hit Ctrl+Shift+Enter instead of Enter to enter the formula

Cell G4 will contain #N/A (because you need more than one day's values to assign PSAR), but G5:G27 should contain valid PSAR values.

And you can change the parameter values in cells G1 and G2 to alter how PSAR is calculated.

And it is very fast. On over 10,000 data points, my recalculation time upon changing one of the input factors in under a second.
 

rharmelink

Junior member
34 3
Sorry, but I've not done it. I don't see a reference library available...

Thanks for your tips and I can use TA_LIB now. How do I use AddIns function such as TA_SAR in a macro? Could you please give me a reference VBA code just for your example: TA_SAR(E4:E27,F4:F27,G1,G2)?
 

a6m

Newbie
1 0
hello everybody

I have found this thread, and I believe there are here sono Ta-lib experts

now, I wish to know if it's possible to change in Ta-lib the parameters, that is if the parameters are only to be put in a cell or if it is possible to specify an array form them

to bring an example:
if I write
TA_MA(E1:E50,5) I have a moving average for the last 5 cells - fixed
TA_MA(E1:E50,A1) I have a moving average for the last cells - the value is changeable in the 1 cell
may I write:
TA_MA(E1:E50,A1:A50) ? I coukd have a different MA for every cell, thus I have a adaptive MA , if I change the A colum value with a formula

does anybody knows?

thank you :):)
 

bahetibraj

Newbie
1 0
Hello everyone

I used the TA_SAR function and compared the results with the Reuters Eikon historic data for the same. I observed that most values matched the Eikon calculated values to the last decimal however its giving differences at odd intervals, there is no pattern in the differences (occurrence or amount), I wanted to know if anyone has encountered the same problem

Also if anyone has any ideas as to why this would be happening

I have attached the file. Waiting for inputs thanks again
 

Attachments

  • Differences in PSAR Calc.xlsx
    14.8 KB · Views: 824
 
AdBlock Detected

We get it, advertisements are annoying!

But it's thanks to our sponsors that access to Trade2Win remains free for all. By viewing our ads you help us pay our bills, so please support the site and disable your AdBlocker.

I've Disabled AdBlock