![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
A Good Day
Sunday, 17 August 2008 08:04 pmToday was great; I went to Quaker Meeting, had lunch with a group of 12, including ![[livejournal.com profile]](https://www.dreamwidth.org/img/external/lj-userinfo.gif)
hajen, who I just discovered is LJ-enabled today!
melted_snowball and I went for a country drive with roverthedog, and he made tasty duck and broccoli stir-fry, and since then I've been playing on teh computer.
Yesterday I whinged about Quicken; one of the frustrations was with historical USD/CAD exchange rates. Getting the proper exchange rate for a list of 30 dates is... somewhat labour intensive.
Apparently not one for the simplest route to an answer, I had to do this:
http://spreadsheets.google.com/ccc?key=puQxoYrt-CYqRz0mGUzsVAA&hl=en
...What you'll see there is: a list of dates on the left; columns of calculated data in the middle, and a graph showing USD/CAD exchange rates on the right, with google-finance style sliders.
The graph is sort of pretty, but it's entirely for show. The calculated data's the good part as far as I'm concerned. Each row is doing a lookup on oanda.com for an arbitrary date's USD/CAD exchange rate. The formula for each line is something like:
That is: get the HTML from this URL, replacing this parameter with whatever is in cell B2 of the spreadsheet, and display whatever is in the 7th table in that HTML.
Basically the entire internet is scriptable into a google spreadsheet. ...Maybe excel or openoffice can also do this, I really don't know (I know excel can download a webpage, but this was MUCH easier for me to understand.) And the updates are quite impressively fast; I cringed every time I changed the date-range because it instantly meant 20 more hits on oanda's server. But it was quite speedy. And this whole experiment was maybe an hour of playing. Google docs, you rock.
(If anybody with a google docs account wants to play with the source, you're welcome to it, just let me know; I apparently can't share an editable version with the wider world, only with "invited" people. Though, you should be able to see the formulas yourself. And I'm happy to answer questions...)
hajen, who I just discovered is LJ-enabled today!melted_snowball and I went for a country drive with roverthedog, and he made tasty duck and broccoli stir-fry, and since then I've been playing on teh computer.Yesterday I whinged about Quicken; one of the frustrations was with historical USD/CAD exchange rates. Getting the proper exchange rate for a list of 30 dates is... somewhat labour intensive.
Apparently not one for the simplest route to an answer, I had to do this:
http://spreadsheets.google.com/ccc?key=puQxoYrt-CYqRz0mGUzsVAA&hl=en
...What you'll see there is: a list of dates on the left; columns of calculated data in the middle, and a graph showing USD/CAD exchange rates on the right, with google-finance style sliders.
The graph is sort of pretty, but it's entirely for show. The calculated data's the good part as far as I'm concerned. Each row is doing a lookup on oanda.com for an arbitrary date's USD/CAD exchange rate. The formula for each line is something like:
=ImportHtml("http:// www.oanda.com/convert/fxhistory?expr2=cad& lang=en&date="&B2&"...&format=HTML&redirected=1","table",7)That is: get the HTML from this URL, replacing this parameter with whatever is in cell B2 of the spreadsheet, and display whatever is in the 7th table in that HTML.
Basically the entire internet is scriptable into a google spreadsheet. ...Maybe excel or openoffice can also do this, I really don't know (I know excel can download a webpage, but this was MUCH easier for me to understand.) And the updates are quite impressively fast; I cringed every time I changed the date-range because it instantly meant 20 more hits on oanda's server. But it was quite speedy. And this whole experiment was maybe an hour of playing. Google docs, you rock.
(If anybody with a google docs account wants to play with the source, you're welcome to it, just let me know; I apparently can't share an editable version with the wider world, only with "invited" people. Though, you should be able to see the formulas yourself. And I'm happy to answer questions...)
