Like many students, I paid for parts of my education with a line of credit. I amassed quite a bit of debt over the years, but not quite the national average of $18,000.
Now, less than four months out of school, I’m very close to paying it off. To celebrate, I’ve created this visualization using Google Charts!
See my other more dynamic Google Chart, which uses PHP!
How I made this
This actually took me quite a while to figure out. The first step was gathering data from TD Canada Trust. I downloaded about 13 months of data from my line of credit history as separate CSV files. (I did this one at a time because TD only lets you view data one month at a time…) Then I copied and pasted them into an Excel file to create a consolidated CSV.
Next step was getting Google Charts to work in WordPress. Posts can accept Javascript, so I embedded a .js file as a script in this post and added the required div tag. But since I only had values from Excel, and I needed to create Google Chart values, I had to create a snazzy Excel formula.
But first, since Microsoft is incredibly stupid, I had to convert my dates into simple strings, since Microsoft enjoys outputting dates into special numbers like 01/20/2011 into 3930293…
I did this using:
=TEXT(C42,"mm/dd/yy")
Good, now that it’s just text, I needed to get two cells containing both a date and a balance to look like this:
data.setValue($row, $column, $value);
So since my table looked like this:
| Row ID | Date | Balance |
| 0 | 08/02/11 | 1024 |
| 1 | 09/02/11 | 800 |
My Google Charts formula would look like:
data.setValue(0, 0, '08/02/11');
data.setValue(0, 1, 1024);
data.setValue(0, 0, '09/02/11');
data.setValue(0, 1, 800);
The formula I used was this:
- For the date: =”data.setValue(” & A2 & “, ” & $A$2 & “, ‘” & D2 & “‘);”
- For the amount: =”data.setValue(” & A2 & “, ” & $B$1 & “, ” & E2 & “);”
It looks kind of complicated, but essentially it’s just taking a few strings and adding cells. I put the value 0 and 1 into A2 and B1 so that it would cycle through properly for each row. My actual table looked closer to this:
| Row ID | Date | Date (as text) | Balance | Date value | Balance value |
| 0 | 02/10/11 | 02/10/11 | 1024.23 | =”data.setValue(” & A2 & “, ” & “0″ & “, ‘” & C2 & “‘);” | =”data.setValue(” & A2 & “, ” & “1″ & “, ” & D2 & “);” |
In the end I got a big mass of data that I could paste into the Google Chart Javascript and voila! See the raw Javascript file.