Tracking and charting financial data with PHP

Lately I’ve wanted to do more with wordpress, and adding dynamic data seemed like a cool project.  Although I’m still a few days from posting my ‘mortgage rates’ page to monsterhash.com, I thought I’d offer a glimpse into the technology behind it.

First I had to ingest the data and schedule the data.  For the first task, I used a simple HTTP proxy for the XML data published on Freddie Mac’s website, something I covered in a previous lesson, How to set up an HTTP Proxy in PHP.  I then posted the ingested data to a SQL table, which is a fairly trivial task covered in any MySQL tutorial.  Next, I had to schedule that task (also covered in a previous lesson on chron jobs).

Now that the data is coming in predictably, it would be nice to display it graphically.  I explored a number of solutions, including Flash and jQuery implementations.  But for sheer simplicity and wide compatability, the Google Charts Tool won hands down.

In theory, this tool allows you to ping the Google servers with comma-delimited data, and then the server pings you back with a PNG image.  In practice, both the data and the styling needed a little massaging to get the beautiful graph you see above.

First off, I wanted to grab the data from my server, but I only wanted to grab the most recent data.  The problem here was that, when you pull the most recent rows from a database, it returns them in the order of newest to oldest.  If I feed them into the Google API, it’ll read from left to right, causing the first point in the graph to be the most recent date, and the last point in the graph to be the oldest point before the cutoff.  To reverse the order, I throw the data into two arrays, one for x variables and one for y variables, and then reverse sort them by key before passing them to the function that interacts with the Google server:

So all I’m passing into the function are the x and y coordinates, which will make my code ultra-reusable.  That function looks like this:

With a modest effort, I’ve converted raw data into something visual.  The next, and final step, will be to also set this process on a timer, so that the image gets re-cached to the same location every week.  I’ll set this process to fire shortly after the data gets loaded into the table on Saturday, and then I’ll be able to dynamically track mortgage rates in my wordpress blog!