Professional Web Applications Themes

Financial Returns - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Financial Returns

    [email]jared.pohl[/email] 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 Karwin Guest

  3. #3

    Default Re: Financial Returns

    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 Guest

  4. #4

    Default Re: Financial Returns

    [email]jared.pohl[/email] 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.
    Bill Karwin Guest

  5. #5

    Default Re: Financial Returns

    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 Guest

  6. #6

    Default Re: Financial Returns

    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 Guest

  7. #7

    Default Re: Financial Returns

    [email]jared.pohl[/email] 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.
    Bill Karwin Guest

  8. #8

    Default Re: Financial Returns

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

    thanks for your help anyway!

    jared.pohl@gmail.com Guest

Similar Threads

  1. Replies: 0
    Last Post: November 6th, 08:47 AM
  2. Replies: 0
    Last Post: November 5th, 11:29 PM
  3. Replies: 1
    Last Post: November 5th, 08:20 AM
  4. Replies: 0
    Last Post: November 5th, 08:13 AM
  5. Financial Hub
    By Jaz in forum Linux / Unix Administration
    Replies: 6
    Last Post: July 22nd, 04:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139