Percentile UDF

[Sako]

Hi,

I have a UDF that calculates the percentile of a range of row. However, I've been uncapable of modifying the UDF to really do what I need to do.

in the tbl_Nasdaq,

there are 3 columns:
-Ticker
-Date_Text
-Open

What I'd like to have is a query that contains:

-Ticker
-Date_Text
-Open
-Percentile

Quick Example:
Ticker, Date_Text, Open, Percentile
Goog, 20100218, 100.00, 0.8
Goog, 20100217, 85.00, 0.4
Goog, 20100214, 57.00, 0.2
Goog, 20100212, 96.00, 0.5
Goog, 20100128, 153.00, 1
IBM, ...
IBM, ...
IBM, ...

 

[jdraw]

Hi,

I have a UDF that calculates the percentile of a range of row. However, I've been uncapable of modifying the UDF to really do what I need to do.

in the tbl_Nasdaq,

there are 3 columns:
-Ticker
-Date_Text
-Open

What I'd like to have is a query that contains:

-Ticker
-Date_Text
-Open
-Percentile

Quick Example:
Ticker, Date_Text, Open, Percentile
Goog, 20100218, 100.00, 0.8
Goog, 20100217, 85.00, 0.4
Goog, 20100214, 57.00, 0.2
Goog, 20100212, 96.00, 0.5
Goog, 20100128, 153.00, 1
IBM, ...
IBM, ...
IBM, ...

Many of us don't have acc2007 so perhaps youcould show the code for your UDF.

 

[Sako]

Hi,

I didn't think about that! Here I attached a 2002-2003 Access db.

Thanks,

I'll post the UDF down here:

Option Compare Database


Public Function SAS5Q1(TableName As String, ValueField As String, TickerName As String, p As Single) As Single
' this function determines the value for the percentile (p) specified according to SAS method 5
Dim str As String ' the SQL statement to fill the recordset

Dim rst As DAO.Recordset ' recordset to get the data, in order
Dim n As Long ' number of records in the recordset
Dim x As Variant ' intermediate result
Dim j As Long
Dim g As Single

Dim db As Database: Set db = CurrentDb

str = " FROM " & TableName

str = str & " WHERE Ticker='" & TickerName & "'"




Set rst = db.OpenRecordset("SELECT " & ValueField & str & " ORDER BY " & ValueField, dbOpenForwardOnly, dbReadOnly)

n = CurrentDb.OpenRecordset("SELECT COUNT(*)" & str).Fields(0).Value

j = Int(n * p)
g = (n * p) - j

Dim s1 As Single
Dim s2 As Single



If n = 1 Then
'if there is only 1 value in the dataset, set the percentile as that value
    SAS5Q1 = rst.Fields(0)
End If

If n = 2 Then
    If g = 0 Then
        s1 = rst.Fields(0)
        rst.MoveNext
        s2 = rst.Fields(0)
        SAS5Q1 = (s1 + s2) / 2
    Else
        rst.MoveNext
        SAS5Q1 = rst.Fields(0)
    End If
End If

If n > 2 Then
    If g = 0 Then
        rst.Move (j - 1)
        s1 = rst.Fields(0)
        rst.MoveNext
        s2 = rst.Fields(0)
        SAS5Q1 = (s1 + s2) / 2
    Else
        rst.Move (j + 1) - 1
        SAS5Q1 = rst.Fields(0)
    End If
End If
rst.Close
j = 0
g = 0
s1 = 0
s2 = 0

End Function

 

[gemma-the-husky]

percentile?

that means you have to establish where in a range of items your value lies? yes?

so you need a sorted query with, say 50 items

and your item is positioned at number 36

so you are in the 72nd percentile? is that it?

---------------
i think this is quite tricky, as access/databases in general dont have an absolute ordering concept - in that you have a set of data and the order is whatever you want it to be.

not sure of the easiest way to go about this.

 

[Sako]

Hi Gemma,

yes that is exactly what I want in the extra field.

Any ideas?

Thanks

 

[jdraw]

Hi Gemma,

yes that is exactly what I want in the extra field.

Any ideas?

Thanks

I found this code on another site. Author says "percentile could be calculated using SQL facilities only.
The following example gives 90th percentile of [Value] field in [tbl]".


SELECT Max([Value]) AS Percentile
FROM
(SELECT TOP 90 PERCENT [Value]
FROM [tbl]
ORDER BY [Value] ASC);

Hope this is useful.

 

[Sako]

Hmm, thanks jdraw.

my UDF does that exactly. I'll keep searching I guess...

 

[gemma-the-husky]

percentile?

that means you have to establish where in a range of items your value lies? yes?

so you need a sorted query with, say 50 items

and your item is positioned at number 36

so you are in the 72nd percentile? is that it?

its the red bit that is hard. Access has no easy way of calclulating and storing a position value. I know others have functions that will do this, but I can't recall needing to do this.

If it was a one-off, i would probably store the data in a temporary table, add a sequence field, and populate that with VBA (iterating a recordset) - but others may have an easier solution


Note that if this is required for a report, then its much easier. Because Reports inherently DO have a sequence, then its easy to get at the positonal value in a report.