How to Calculate the PSAR Using Excel - Revised Version

The PSAR is a popular technical indicator developed by J.Welles Wilder. It is useful for identifying the trend and as a trailing stop to lock in profits.
▶To get the formulas and more information read the accompanying article here: wp.me/p32qmZ-Uo
▶Improve your trading profits with Tradinformed Backtest Models: bit.ly/Backtest...
▶Check out my other Technical Indicator videos: • How to Calculate the S...
▶How to calculate popular indicators using Excel is the subject of my eBook, available in the Amazon Kindle Store: amzn.to/1smFKHr
Follow me on twitter: / tradinformed
Facebook: on. 1715mCE
Or Google+ plus.google.co...
www.tradinformed.com

[@cnsnmms3708]

You are using lower cases for "L" in formulas and it seems like "I" in video that's confusing but I finished my matlab function thanks to you. Good job!

[@tilttrader]

you just did the example for us.....it would have been really nice if there was a general formula involved

[@TheMystica]

Mark, thanks for the video, a lot of cool concepts. You probbaly don't monitor this anymore, but if you do, there seems to be a discrepancy of what you say and what you type: @9:33 You Type the Basic formula: PSAR > CLOSE = Falling But @ 9:51 you Say 'Closing Price Greater than PSAR' is Falling (which would be CLOSE > PSAR = Falling) Am I missing something? Which is correct? Thanks, Mike

[@George-op2qc]

really you are the best and the easiest one who explained it , can you please tell me why the calculation is different from Oanda charts ?

[@DontTrustMeImASc]

Just used guide this on Nationwide's UK House Price Data (transformed into OHLC candles)...very useful, thanks Mark.

[@MarkUrsell]

Matthew Clarke That's great Matthew. That's an interesting project. Are you trading the price data or just interested in the direction of house prices?

[@DontTrustMeImASc]

***** The latter Mark. We don't own - I wanted something to put the prices into some sort of context. I'll send you the .xlsm if you'd like to see.

[@malgorzataciszak5291]

Hi mark,im getting a circular ref error @h8 and i8

[@sv0079807]

good explanation.. suggestion to viewers. watch at 1.25 Speed

[@patiukan]

And how we make it on excell chart

[@kazmyuksel6501]

In the definition of PSAR, you calculate tomorrow's SAR with todays values. But in this, you calculate today's SAR with todays values. And checked it with TradingView, PSAR values are not correct.

[@ducciorssi]

Hi Mark, there are two points that according to my understanding diverge from the definition en.wikipedia.org/wiki/Parabolic_SAR 1) @5:50 use C6,C5 instead of C5,C6 (and the same for the reverse trend) 2) @7:12 use K5 instead of J6 Can you please let me know your thoughts on this. Thanks

[@malgorzataciszak5291]

I figured it out,works now.

[@matiassande9669]

Hello! Excellent video. Do you have the link to download the excel spreadsheet? It would be very useful. Thanks a lot

[@MarkUrsell]

Hi Matias, you can get all the formulas (and 20 more) plus full explanations in my Spring Store: tradinformed.creator-spring.com/listing/21-technical-indicators-spread

[@Devin_Downing]

Hello Mark great video much appreciated! I noticed in your video that when the price touched the PSAR it flipped the trend. Would it be simple to add in the formula =< PSAR? For Example: lets say the trend is falling and the PSAR value is 2800.00 and todays high was equal to the PSAR value of 2800.00 but didn't exceed it, I still want the trend to be falling. Does that make sense?

[@olivierdanhoffre5754]

Hello M. Ursell great work. I've checked NDX with a Web Stock site results (Boursorama), but from 20/08/2021 to 09/11/2021. For 10 September 21, (Open at 15648.05) I find with your formula a PSAR = 15701.40 which is exact and correspond to a change trend from rising to falling. But after I note slight discrepencies with Excel computation. I suspect rounding problems in Excel, which cumulate and at the end diverge from values. For example, for 09 Nov 21 (open at 16389.98) I find a PSAR = 16084.85 and In the web site a PSAR = 16160,48, which is a lot. Perhaps other web sites would give an other value. How many decimals do you use for values ?

[@user-yu7uk5zw4i]

Mark TY! rly gr8 job!

[@gurujiramadal]

thank

[@joelstewart385]

So. I am trying to rework this for a spreadsheet stock screener. But either I need to re-re-recheck my english-ing of your cell points and logic strings, or it is a cyclical thing, where all the parts depend on the other parts? In Wilder's book, it looks non-cyclical; any thoughts? That is, if I have about 5000 stocks down the left side, dates across top y-axis, I want to split the calculation up across several sheets to reduce the power needed for doing each step of the calculation. Also it means I can more easily autofill and have no difficulties with columns misinterpreting the cells I want them to point to. But with the layout appearing to be cyclical, it gets funky. Any thoughts?

[@joelstewart385]

"cell points" is "which cells you point certain instructions to in order to complete calculations"

[@joelstewart385]

This is my english'd version: drive.google.com/open?id=1w0s3dIuPg4mSGZPmCVVX_QXXfYJT-Fmo

[@joelstewart385]

I corrected my englishing of your stuff; I do not know if it is cyclical at this point, I am a bit fried at the moment; been trying to actually make sense of Wilder's words properly after struggling to recreate your thing in the way I wish to do it. I shall return home and try again.

[@changhao2253]

thank you sir

[@MarkUrsell]

You are most welcome!

[@StephJ-yn2sw]

Hello, When I enter the formula on J6, Excel 2016 says that there is an error in the formula. Any idea ? Thank you.

[@StephJ-yn2sw]

Ok, I figured it out by myself. I had to replace some "," by ";". Also in my French version of Excel I need to enter "SI" in place of "IF" (that's very strange). The right formula for J6 is : =SI(L5="falling";MAX(K5-I5;C5;C4);SI(L5="rising";MIN(K5-I5;D5;D4);""))

[@StephJ-yn2sw]

This is fixed formula for Excel 2016 French version (for English version I assume that you should just replace "SI" by "IF" and "ET" by "AND" : G6 =SI(L6="falling";MIN(G5;D6);SI(L6="rising";MAX(G5;C6);"")) H6 =SI(ET(L6=L5;G6G5;H5=J6);G5;SI(ET(L5="rising";D6E6;"falling";"rising")

[@MarkUrsell]

Thanks for posting the update Stéphane, that is useful to know. There is a useful guide here for Excel 2007 functions: www.piuha.fi/excel-function-name-translation/index.php?page=english-french.html It seems that a lot of the functions have to be entered differently for the French version of Excel.

[@StephJ-yn2sw]

Thank you. Actually I try to calculate SPAR in a C++ application so your Excel method was a great help to understand how SPAR works. There is just something I'm not sure to understand : I noticed that some values was arbitrarily defined on the first line. Is that mean that SPAR have more precision when you have more historical datas ? Is that a problem if I calculate a SPAR only with the datas of the previous period ?

[@remziseciller828]

Hi Mark; Can you distrube excel file with us,please? Thank you.

[@MarkUrsell]

Hi Remzi, thanks for the comment. The PSAR is one of the indicators in my 21 Technical Indicators Package. You can see it here in here in the Tradinformed Shop: bit.ly/2QYmqQU

[@billmietelski]

Bill was here..