Looking for a function to rank over a time period
Author: CarmaAdvisory
Creation Date: 9/6/2017 12:30 PM
profile picture

CarmaAdvisory

#1
Hi everyone, I'd need your help on ranking. I'm looking for a function able to rank a price, for example the Close, over a specific time period (last 20 days), assigning value 1 to the highest price and 20 (in this example) to the lower price.

I tried using PercentRank but it doen't work in the way I want.


Any idea?

Thanks!
profile picture

Eugene

#2
Maybe something like this should do as an idea: HiLoLimit?
profile picture

CarmaAdvisory

#3
Thanks Eugene but actually I'm not interested exclusively to the extreme values but I need to assign a "score" to the middle values also. In other words I'd want the funftion does the same of Excel function "Rank".

Just an example:

CLOSE RANK
10 3
11 2
9 4
12 1
8 5
profile picture

Eugene

#4
If PercentRank doesn't suit you then you might want to come up with your own function e.g.

VBA/Excel RANK in C#
profile picture

CarmaAdvisory

#5
Thank you, but I'm not sure my programming skills will be enough...
profile picture

Cone

#6
I'm trying to understand this in terms of an indicator. Maybe the Median indicator can help?

It makes sense if you're searching for a specific rank in the past for each new bar; this could be put into an indicator series. But if you need to know the specific rank for each bar in the lookback period, the rank value could change for every new bar. In this case, you'd need to save a ranked list in a Dictionary for every bar.

Using Excel, give me an idea of how you need to use the Rank.

profile picture

CarmaAdvisory

#7
Ok, i try to explain better. I need to assign a rank to the Close price so that I can create a rule to establish when to buy for example.

Focusing on the last 3 bars current rank for the close can be 1, 2 or 3.

Close prices:
8
10
12
9

Current rank is 3 because 9 is the lowest close of the latest 3 bars
Rank of the previous bar is 1 because 12 is the highest close

Hope this clarify. Please let me know if it's not clear yet.

Thanks!
profile picture

Cone

#8
Give this a try. Don't forget to add the References indicated.

Notes:
1. Assuming a 5-bar lookback, 1 is the highest, and 5 is the lowest.
2. If a number is repeated in the lookback, its rank value is as if it were the highest in the list. For example, in the 5-lookback series [10,8,7,7,8], the ranking indicator below would return 10 as 1, 8 as 2, and 7 as 4.


CODE:
Please log in to see this code.