I have two tables in MYSQL: VAL10K and RETURN1MONTH.?
VAL10K contains monthly value of $10,000 data for mutual funds, RETRN1MONTH contains 1 month return data of mutual funds for the current month.
I need the php code to update VAL10K based on the 1 month return data in the RETRN1MONTH table.
## Deliverables
I have two tables in MYSQL: VAL10K and RETURN1MONTH.?
VAL10K contains monthly value of $10,000 data for mutual funds, RETRN1MONTH contains 1 month return data of mutual funds for the current month.
I need the php code to update VAL10K based on the 1 month return data in the RETRN1MONTH table.
I've attached a google docs spreadsheet with sample data ([login to view URL]).
The first part shows the VAL10K table before the addition of the latest 1 month return data.
The second part shows sample 1 month return data for a list of mutual funds for the month of December, includeing one, CZAXX, that is is appearing in the update list for the first time this month.
The third part is the VAL10k table after the addition of the latest 1 month return data (record #s 12-16).
The updated val10k figure is derived like this:?
1. Collect one month return (RT1MONTH) for each fund
An example RT1MONTH for MBFOX is 6.32
2. Convert RT1MONTH data point to actual decimal value of percentage return by dividing this number by 100.
?
6.32/100 becomes .0632
?
3. Add 1 to this value
?
.0632 +1 becomes 1.0632
?
4. Multiply this value by the latest val10k figure for that fund in the VAL10K table - in the case of MBFOX the number is 1900.
?
1900 * 1.0632 = 2020
?
Insert this result (2020) into new column with new VALDATE number for current month (VALDATE number can be simply a variable the I can update).?
Finally, if there is a fund in the RETRN1MONTH table that is not yet in the VAL10K table (such as with CZAXX in the sample data), the RT1MONTH figure should be multiplied by 10000 to derive the VAL10K number. The result of this for CZAXX can be seen in record 16.
Please let me know if you have any questions. I'm including the code below of what I've done so far (before I remembered that I'm a very poor coder) in order to give you an idea of what I'm looking for. The variables have different names etc. but you'll get the jist:
*****
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[login to view URL]">
<html xmlns="[login to view URL]">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<?php //VLINE
function max_db_connect() {
$server = "localhost";
$user = "root";
$password = "password";
$database = "data prep";
#$database = "db17273_maxfunds_data";
mysql_connect($server,$user,$password);
@mysql_select_db($database) or die( "max_db_connet error");
};
max_db_connect();
$query="SELECT [login to view URL], [login to view URL], [login to view URL], ifnull([login to view URL],0) as return_data_point, ifnull((SELECT [login to view URL] FROM val10ksteeldev WHERE [login to view URL] = '2009-12-01')),10000) as cur_val10k
FROM steele
LEFT JOIN val10ksteeldev ON [login to view URL] = [login to view URL]
GROUP BY [login to view URL]";?
$result = mysql_query($query) or die(mysql_error());
//print_r($result);
//*************************************DATA MONTH VARIABLES
$num = 0;
$valdate = '2009-12-01';
$dt_now = date("Y-m-d");
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
extract($row);
//****************************************************************************************VAL10k
//$data_point = ((($row["return_data_point"])/100)+1)*10000;
$data_point = ((($row["return_data_point"])/100)+1)*$row["cur_val10k"];
$insert_val10k_sql = " Insert Into val10k set STSECID='$STEELESECID', TICKER = '$SYMBOL', VALDATE = '$valdate', VAL10K = '$data_point', UPDATED='$dt_now' ";
//mysql_query($insert_val10k_sql);
echo $insert_val10k_sql."<br>";
}
?>
</body>
</html>