MySQL Forums

financial database / data management
Posted by: Federico valori
Date: August 16, 2005 03:39AM

Hello,

This is a simplified description of the project I am working on. I am building an on-line fund management database system. (Mysql/PHP). This will track info on up to 500 funds. For each fund there are three main monthly entries: Net Asset Value, Asset Under Management, and monthly performance. These data can be uploaded all at once via an excel spreadsheet, and then updated month by month by the each fund manager. I have, among others, the following tables:

company [company_id + other columns]
products [product_id, company_id (foreign key), + other columns]
monthly_perf [monthly_perf_id, product_id (foreign key) + other columns]

So far, so good. The problem I have is that with the very basic data provided (NAV/Monthly Performance/AUM) for each fund I then need to calculate and populate the website with all sort of derived data such as volatility, sharp ratio, standard deviation, annualized return etc.. etc… Users will then be able to search funds according to their selected criteria. For example, they could search for all funds with asset under management above 1 mil and with annualised performance above 5% and with annualised volatility under 3. Also they will need to see the result not only as a list of products matching their criteria, but ideally, for each product I will need to create visual dynamic graphs.

There are formulas to calculate these financial funds data which are quite difficult for those who do not work in the financial industry. This is an example:

Standard Deviation - Standard Deviation measures the dispersal or uncertainty in a random variable (in this case, investment returns). It measures the degree of variation of returns around the mean (average) return. The higher the volatility of the investment returns, the higher the standard deviation will be. For this reason, standard deviation is often used as a measure of investment risk.

Where R I = Return for period I
Where M R = Mean of return set R
Where N = Number of Periods
N
M R = ( S R I ) ¸ N
I=1
N
Standard Deviation = ( S ( R I - M R ) 2 ¸ (N - 1) ) ½
I = 1

I am at the very beginning of this project and I am now assessing the best, fastest way to accomplish this. At the moment I am considering the following:

- Do I (can I?) work out all these financial data in SQL LOGIC or do I Do I need to create php function to evaluate volatility, sharp ratio, standard deviation, annualized return etc…

- Are there server behaviours or ready-made PHP function that could help me?

- Once I create the financial data required, how do I deal with it? Do I use temporary tables to store this data and allow the web-user to search funds according to their selected criteria during their web-session? Or do I build all the columns into the product table? The thing is that all this info will always be changing on a month to month basis as the user updates the monthly performance table with the new Net Asset Value new performance and new Asset under management info.

I am looking forward to any feedback you could give me.

Best Regards,

Fredo

Subject
Written By
Posted
financial database / data management
August 16, 2005 03:39AM
August 16, 2005 08:38AM
August 16, 2005 10:15AM
August 16, 2005 12:41PM
August 17, 2005 01:52AM
August 17, 2005 02:56AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.