Aggregate User Function

Topic

New Reply

If I define a Public Function in an Access VBA module, then I can use that function to manipulate fields that are selected in a SELECT statement. I wonder if I can define such a function to be used as an aggregate user function (i.e. similar to SUM, AVERAGE)

The idea is the following: suppose I have table which for several project contains fields (CashFlowDate, Amount) that describe the cashflow related to that project. If I could define a user function XIRR then I could in a single statement calculate the XIRR for several project as:

SELECT ProjectName, XIRR(CashFlowDate, Amount) as IRR
FROM tblProjects
GROUP by ProjectName

Probably wishfull thinking?

Reply | Quote

Replies

Have you tried it? Most existing VBA functions functions can be used in queries, although complex statements can get problematic. Another possibility would be to create a class module to create a recordset that gives you the result, but that’s a concept I find hard to get my arms around most of the time.

Reply | Quote

NO, I didn’t try yet. I went for the VBA class approach: read a recordset, convert to an array, do the XIRR calculation. It just occurred to me that there could be a more elegant approach. I’ll give it a try.

Reply | Quote

It won’t work: as soon as I use a public user function, I get an SQL error message.

SELECT ProjName,mySum(Amount) FROM tblProj GROUP BY ProjName

where mySum is a Public function defined in an Access VBA module

produces:

You tried to execute a query that does not include the specified expression ‘mySum(Amount)’ as part of an aggregate function.

Reply | Quote

A corrected syntax will work. Try something like:

SELECT ProjName,mySum(Amount) as myNumber FROM tblProj GROUP BY ProjName, mySum(Amount);

Worked for me.

Reply | Quote

Of course that will work, that’s what the error message says. The point is: I only want to group by Project not by Project and mySum.

Reply | Quote

Of course that will work, that’s what the error message says. The point is: I only want to group by Project not by Project and mySum.

Why don’t you just build a query and test it. Then you can look at the SQL for the query and put it in code, if that is your objective.

I must admit, the problem and objective are not clear to me.

Reply | Quote

Do you need the Group By at all?

Code:

SELECT ProjectName, XIRR(CashFlowDate, Amount) as IRR
FROM tblProjects

You are selecting from, tblProjects, so presumably there is only one record per Project anyway.

Are CashFlowDate and Amount fields in tblProjects?

Reply | Quote

In posting the original question, I simplified the problem to its bare essentials. By using some unfortunate names or wording I seem to confuse everybody, for which I apologize.
So let’s rephrase the question.

The goal of the problem is to calculate for each project an internal rate of return. I essentially have two tables:

tblProjects: contains fields like ProjectID and ProjectName (one row per project)
tblCashFlows: contains fields ProjectID, CashDate and Amount. (more than one row per project, describes expenses and revenues for the projects)

One way of doing it is joining the two tables and throwing the result into EXCEL which has a native XIRR function (albeit in an add-on Analysis Pack)

SELECT ProjectName, CashDate, Amount
FROM tblProjects, tblCashFlows
WHERE tblProjects.ProjectID=tblCashFlows.ProjectID

That supposes to automate EXCEL, with the further difficulty of getting the results back into an Access Report.

The other way of doing it, is to write an Access class or module that executes that query, analyzes the resulting record set and passes the group of records that
belongs to a same project to a user written XIRR function that performs the calculation. It avoids calling upon EXCEL at the expense of writing the function yourself.
This is the approach I have taken today.

So the XIRR function is a function that acts on a group of records, just like Access’ aggregate functions (COUNT,AVG,…). It then occurred to me that it would be
nice if I could use my function in just the same way, directly in an SQL query, thereby avoiding writing the class or module:

SELECT ProjectName, XIRR(CashDate,Amount) as IRR
FROM tblProjects, tblCashFlows
WHERE tblProjects.ProjectID=tblCashFlows.ProjectID
GROUP BY ProjectName

This should produce one row per project with its name and it (financial) return.

However, SQL will not let me do it.

Reply | Quote

So the XIRR function is a function that acts on a group of records, just like Access’ aggregate functions (COUNT,AVG,…). It then occurred to me that it would be
nice if I could use my function in just the same way, directly in an SQL query, thereby avoiding writing the class or module:

SELECT ProjectName, XIRR(CashDate,Amount) as IRR
FROM tblProjects, tblCashFlows
WHERE tblProjects.ProjectID=tblCashFlows.ProjectID
GROUP BY ProjectName

But XIRR(CashDate,Amount) does not act on a group of records, it acts on two values from tblCashFlows.
So you will get a different answer for each record in tblCashFlows.

So I think, (and I don’t know anything about IRR) that you need to you rewrite the XIRR function so that you can use this SQL

Code:

SELECT ProjectName, XIRR(ProjectID) as IRR 
FROM tblProjects

This function would then have the job of retrieving the relevant records from tblCashFlows and using them to work out the IRR for the relevant project.

Reply | Quote

But XIRR(CashDate,Amount) does not act on a group of records, it acts on two values from tblCashFlows.
So you will get a different answer for each record in tblCashFlows.

That’s true John. In the same way if you use SUM(Amount) it also acts apparently only on a field, but under the cover of SQL the GROUP clause forces it to act on all selected records. So SQL must somewhere retrieve and bundle the records. I dreamed of something similar for a User defined function.

So I think, (and I don’t know anything about IRR) that you need to you rewrite the XIRR function so that you can use this SQL

Code:

SELECT ProjectName, XIRR(ProjectID) as IRR 
FROM tblProjects

This function would then have the job of retrieving the relevant records from tblCashFlows and using them to work out the IRR for the relevant project.

This solution is a step in the right direction. It repacks the code to retrieve the records, which I previously wrote in a class module, into a function. But I still have to retrieve the records myself. This is probably as far as one can get.

Reply | Quote