Implementation, steps 1 through 5 - PHP

Step 1: Download raw S&P 500 index data from http://finance.yahoo.com/q/hp?s=%5EGSPC and format the Excel table to look like the image below (delete unneeded columns like trading volumes and add the new column “entry”):

Your MySQL table should look like the one above. However, the above screen shot is still in .csv format

Step 2: Convert the .csv to .sql and upload to your MySQL server. You can read an article that explains how to convert your Excel worksheet. 

Step 3: It's time to write the PHP script. First we connect to the database using the script below:

//connect to mysql database

$username = "your mysql username";

$password = "your mysql password";

$hostname = "your mysql hostname";

$database = "your mysql database";

$dbhandle = mysql_connect($hostname, $username, $password)

or die("Unable to connect to MySQL");

//select a database to work with

$selected = mysql_select_db($database,$dbhandle)

or die("Could not select $database");

Step 4: In order to know how many rows are in the database, which is a value that will be used in our computation, we will query MySQL:

//count the number of rows in the database including the latest entry

$result1 = mysql_query("SELECT * FROM `sp500`")

or die(mysql_error());

// store the record of the "example" table into $row

$row1 = mysql_num_rows($result1)

or die("Invalid query: " . mysql_error());

// Print out the contents of the entry

$numberofentries = $row1;

The $numberofentries contains the maximum number of rows in the MySQL table (in the above screenshot it is 15020). The data in the MySQL table will be arranged from the latest entries all the way down to old entries. Refer to the screenshot above.

The mysql_num_rows will be used to count the number of rows in the table SP500.

Step 5: Compute the last entry to be shown in the HTML table. Since we will be showing only the latest 50 rows, the following will be used:

$lastentry = $numberofentries -50 +1;

This means that, for example, we have 15020 entries in the table; we would like to show them starting from Entry # 15020 to (15020-50+1) or Entry # 14971. This value, as well as the maximum entry above, will change as trading days goes by.