I’ve been working today on the Quick Start manual for KydWeb, which I still hope to introduce on Monday.
Originally, I explained how to write a formula that references a page at http://www.eia.gov to return historical data for the the average price of gasoline in US$.
But this evening, I made a great discovery. I stumbled across a site that offers historical prices for many commodities in many currencies. It only took me about two minutes to write formulas that returned prices in Euros for two yellow products, gold and bananas. So that’s what I’ll cover in the Quick Start Manual.
But because many Excel users would find the gasoline data useful, I’ll explain how to reference it here.
The US Energy Information Administration provides the monthly price of gasoline at this link. Here’s the top of their price-history table:
And here’s a simple Excel worksheet that returns data from this table for any month and year.
The truncated URL in cell A1 is the same URL as the link to the monthly gasoline prices above.
Here are the formulas:
A2: =KydWeb_HtmlFind(A1,”Year”)
This formula finds the position in the HTML where “Year” first appears. As it turns out, that “Year” is the “Year” shown at the left of the red title bar in the image above. So the position number returned by cell A2 is within the table of data, and therefore defines the table for the formulas that follow.
(By the way, there would have been no problem if the “Year” text that interests us weren’t the first time that “Year” appears on the page. Unlike Excel’s FIND and SEARCH functions, we could have used any number of arguments in KydWeb_HtmlFind to specify the correct instance of “Year”.)
Cells A5 and B5 contain numbers that specify the date that interests us. Then this formula…
C5: =KydWeb_HtmlTableRow($A$1,$A$2,$A5)
…finds the row number within the table where “1994″ is found in the first column. Now, because we know the row and the column (as specified by the month number), we can use this formula to return the appropriate value:
D5: =KydWeb_HtmlTableData($A$1, $A$2, $C5, $B5+1)
Here, the KydWeb_HtmlTableData function acts much like an INDEX function, where the first two arguments combine to specify the table, and the next two arguments specify the row and column numbers, respectively.
You can download this simple workbook of gasoline prices here.
Now if I can only get KydWeb done…
(Update: It’s done! You can learn about KydWeb here.)
