Parabolic SAR in EXCEL

[krapdad]

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.

 

[rharmelink]

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.

 

[krapdad]

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]

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]

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]

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]

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]

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]

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]

#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]

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]

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)?

 

[foggy123]

I found their instructions useful, but they didnt mention the N/A value for the first entry.
Thanks.

 

[a6m]

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]

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