Geometric Return

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've inherited an front end Access database ....
The user selects a Start date, End date and account number (exp: 1/1/2004 -
6/30/2004) from a form --- which then uses a pass through query which then
executes a stored procedure in SQL.

How can I convert the following calculation into VBA using the selected
dates/accts# in a stand alone Access DB?


SELECT ((power(@geometricreturn,(4/cast(@cnt as float)))) -1) * 100 AS
'AnnualizedReturn'
GO
 
That doesn't look like a stored procedure - the power function is native to
T-SQL and just raises a number to a power. So power(2,3) is 8.

The @ symbol refers to a parameter that is being passed to the stored
procedure. In order to answer your question I'd need some more
information....

I'd guess that there is something in the code before the select command
which sets the values of @geometricreturn and @cnt.... can you see what this
is?

I'm not sure what the 4/cast thing is... I think that is a stored procedure.
Can you find any more detail about it?
 
Cast is just a T-SQL function that converts a number from one format into
another (in this case into a floating point value). If Rob is right about
the power function: Power(x,y) = x ^ y then your VBA might look like the
following, after you replace @Cnt with a variable called count, and
@GeometricReturn with a variable called GeoRtn

AnnualizedReturn = ((GeoRtn ^ (4/cdbl(Count))) -1) * 100

HTH
Dale
 
Dale Fye said:
Cast is just a T-SQL function that converts a number from one format into
another (in this case into a floating point value). If Rob is right about
the power function: Power(x,y) = x ^ y then your VBA might look like the
following, after you replace @Cnt with a variable called count, and
@GeometricReturn with a variable called GeoRtn

AnnualizedReturn = ((GeoRtn ^ (4/cdbl(Count))) -1) * 100

HTH
Dale
Click to expand...

Doh. How could I miss that 4/cast is just 4 divided by a cast function?
<<Hits self round the head with a plank>>
 
I hate to do this to you but I've copied the SQL Proc created to pass the
dates from the Access form to the SQL table. I understand from the original
developer that Access does not have a function for 'power' so he had to use a
cursor in SQL to calculate Annulized Return.

CREATE PROCEDURE usp_CalcAnnualizedGeometricReturn
@startdt datetime ,@enddt datetime,@composite varchar(50)
AS
DECLARE @return float,@geometricreturn float,DECLARE @cnt int

SET @geometricreturn = 1
SET @cnt = 0

DECLARE cursorCalcGeometricReturn CURSOR GLOBAL SCROLL STATIC FOR

SELECT
(total_return + 1) as return_and1
FROM
tbl_quarterlydata
WHERE
Composite_ID = @composite and
quarter between @startdt and @enddt

OPEN cursorCalcGeometricReturn

FETCH FIRST FROM cursorCalcGeometricReturn INTO @return

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @geometricreturn = @geometricreturn * @return
SET @cnt = @cnt + 1

FETCH NEXT FROM cursorCalcGeometricReturn INTO @return
END

CLOSE cursorCalcGeometricReturn
DEALLOCATE cursorCalcGeometricReturn


SELECT ((power(@geometricreturn,(4/cast(@cnt as float)))) -1) * 100 AS
'AnnualizedReturn'
GO
 
Beats me. That's beyond my SQL knowledge - though if I had to guess I'd say
it's doing something like looking at the table tbl_quarterlydata, taking the
records where the quarter field falls in a date range (with a particular
CompositeID?), and multiplying them together. Then raising that figure to
the power of 4/(the number of records that it found), subtracting 1 and
multiplying by 100.

Think PC might be right - that you should go back to the originator and ask
him what it needs to do. (There is a power function as Dale has already
pointed out - 2^3 = 8)
 
.....where 'them' is the total_return field plus 1. A percentage increase
figure presumably.
 
Back
Top