Financial Returns

Hi Guys,

I am currently working on a putting together an sql query that
calculates, given an index, the percentage returns over a given period.



I have written the qeury that can calculate the percentage return.
However, the problem that I have is trying to combine these queries
into one large query that will return the date and the different
returns (1month, 3month, 6month......5year) at that point in time..


The following SQL returns the portfolio code, the date and the 3 month
return, (the sql was written to work in ms access.. but a mysql answer
would be fine..)


SELECT t1.PORTFOLIO_CODE, t1.DATE,
((t1.GROSS_INDEX-t2.GROSS_INDEX)/t2.GROSS_INDEX) AS 3MONTH_RETURN
FROM PORTFOLIO_PERFORMANCE AS t1
LEFT JOIN (SELECT PORTFOLIO_CODE, DATE AS ORIG_DATE_T2,
dateadd('d',-1,dateserial(Year(ORIG_DATE_T2), MONTH(ORIG_DATE_T2)+4,
1)) AS NEW_DATE_T2, GROSS_INDEX FROM PORTFOLIO_PERFORMANCE) AS t2
ON (t1.DATE=t2.new_DATE_T2) AND (t1.PORTFOLIO_CODE=t2.PORTFOLIO_CODE);


I know that for each return, i need to effectively offset the table,
portfolio_performance with itself by the period im calculating the
returns for and then join them. but how do i do this multiple times?


Hope this question makes sense, any help would be appreciated, thanks
alot!!!

jared.pohl@gmail.com wrote:
> SELECT t1.PORTFOLIO_CODE, t1.DATE,
> ((t1.GROSS_INDEX-t2.GROSS_INDEX)/t2.GROSS_INDEX) AS 3MONTH_RETURN
> FROM PORTFOLIO_PERFORMANCE AS t1
> LEFT JOIN (SELECT PORTFOLIO_CODE, DATE AS ORIG_DATE_T2,
> dateadd('d',-1,dateserial(Year(ORIG_DATE_T2), MONTH(ORIG_DATE_T2)+4,
> 1)) AS NEW_DATE_T2, GROSS_INDEX FROM PORTFOLIO_PERFORMANCE) AS t2
> ON (t1.DATE=t2.new_DATE_T2) AND (t1.PORTFOLIO_CODE=t2.PORTFOLIO_CODE);

FWIW, this uses some MS Access specific functions. DATEADD() and
DATESERIAL() do not exist in this form in MySQL.

Also, what happens if there does not exist a row for the date 3 months
ago? The subquery for t2 will return an empty set, and the LEFT JOIN
will make that return NULL for t2 columns. Thus your calculations
involving t2 columns will yield NULL. That may be okay, but I wanted to
bring your attention to it.

> I know that for each return, i need to effectively offset the table,
> portfolio_performance with itself by the period im calculating the
> returns for and then join them. but how do i do this multiple times?

Any time you need to base calculations on more than one row from the
same table, you need to do a self-join for each additional row.

SELECT curr.portfolio_code, curr.`date`,
(curr.gross_index-mo1.gross_index)/mo1.gross_index AS 1MONTH_RETURN,
(curr.gross_index-mo3.gross_index)/mo3.gross_index AS 3MONTH_RETURN,
(curr.gross_index-mo6.gross_index)/mo6.gross_index AS 6MONTH_RETURN,
(curr.gross_index-yr1.gross_index)/yr1.gross_index AS 1YEAR_RETURN,
(curr.gross_index-yr5.gross_index)/yr5.gross_index AS 5YEAR_RETURN
FROM portfolio_performance AS curr
LEFT OUTER JOIN portfolio_performance AS mo1
ON curr.`date` = mo1.`date` + INTERVAL 1 MONTH
LEFT OUTER JOIN portfolio_performance AS mo3
ON curr.`date` = mo3.`date` + INTERVAL 3 MONTH
LEFT OUTER JOIN portfolio_performance AS mo6
ON curr.`date` = mo6.`date` + INTERVAL 6 MONTH
LEFT OUTER JOIN portfolio_performance AS yr1
ON curr.`date` = yr1.`date` + INTERVAL 5 YEAR
LEFT OUTER JOIN portfolio_performance AS yr5
ON curr.`date` = yr5.`date` + INTERVAL 5 YEAR

This uses plain ANSI SQL syntax, and no subqueries, so it should be
pretty portable. Except for the use of back-ticks for MySQL delimited
identifiers.

Regards,
Bill K.

bill

thanks for the help!! i can see that it isnt that complex of a query...
however i doesnt seem that i can add more than one outer join because
it says my syntax is incorrect.. despite copying and pasting correct
syntax in!!

jared.pohl@gmail.com wrote:
> however i doesnt seem that i can add more than one outer join because
> it says my syntax is incorrect.. despite copying and pasting correct
> syntax in!!

What version of MySQL are you using?

Can you show the exact error message, so we can see what part of the
statement causes the syntax error?

Regards,
Bill K.

Im using microsoft access at the moment ( eww, i know!) to develop this
database then once all the reports are constructed (using crystal) we
are going to port it to a mysql server..

either way this works..

SELECT
curr.portfolio_code,
curr.date,
(curr.gross_index-mo1.gross_index)/mo1.gross_index AS 1MONTH_RETURN
FROM portfolio_performance AS curr
LEFT OUTER JOIN portfolio_performance AS mo1
ON (curr.date =
dateserial(year(dateadd('d',-1,dateserial(Year(mo1.date),
MONTH(mo1.date)+2, 1))),
month(dateadd('d',-1,dateserial(Year(mo1.date), MONTH(mo1.date)+2,
1))),day(dateadd('d',-1,dateserial(Year(mo1.date),MONTH(mo1.date)+2,
1)))))
AND (curr.portfolio_code = mo1.portfolio_code);

but the second i add the second left outer join (using copy + paste,
then changing the relevent name references, it freaks out..

Im using microsoft access at the moment ( eww, i know!) to develop this
database then once all the reports are constructed (using crystal) we
are going to port it to a mysql server..

either way this works..

SELECT
curr.portfolio_code,
curr.date,
(curr.gross_index-mo1.gross_index)/mo1.gross_index AS 1MONTH_RETURN
FROM portfolio_performance AS curr
LEFT OUTER JOIN portfolio_performance AS mo1
ON (curr.date =
dateserial(year(dateadd('d',-1,dateserial(Year(mo1.date),
MONTH(mo1.date)+2, 1))),
month(dateadd('d',-1,dateserial(Year(mo1.date), MONTH(mo1.date)+2,
1))),day(dateadd('d',-1,dateserial(Year(mo1.date),MONTH(mo1.date)+2,
1)))))
AND (curr.portfolio_code = mo1.portfolio_code);

but the second i add the second left outer join (using copy + paste,
then changing the relevent name references, it freaks out..

jared.pohl@gmail.com wrote:
> Im using microsoft access at the moment ( eww, i know!)

This is a MySQL newsgroup. You might want to ask Access-specific
questions on an Access newsgroup. You're more likely to get expert help
and accurate answers there.

When I google, I see that there are a bunch of weird behaviors in Access
regarding outer joins, that make its behavior different from MySQL. But
I can't discern a pattern. It might also be Access version dependent.

Regards,
Bill K.

thanks bill. I have googled, and posted on numerous newsgroups
however, you are the only person whos helped me..

thanks for your help anyway!