Excel Formulas That Access Data on the Internet
If you have a spreadsheet that uses data from an on-line source, you may be able to update the spreadsheet data automatically--very lazy, very cool.
Example: I track the yield on the 10-year Treasury bond. There are a variety of places you can get it; I like the St. Louis Fed's Fred database. They have an option to download a spreadsheet with the data. Very convenient for copying and pasting, but not as convenient as this formula:
Most of the line is the URL for the Excel spreadsheet that the Fred database uses when people ask for a spreadsheet. They call the variable GS10; they keep it in a file called GS10.xls, inside a directory with that label. The tab on the spreadsheet file is also called GS10. The particular cell that holds the January 2008 data is B673. When I copy this down to my row which holds February data, the formula will autmatically become B674. I don't have to open a second spreadsheet with the updated data; I just drag the cell down.
Note that I have a $ in front of the B. That's because I can never remember the exact format for the formula, but I know the data is always in column B. I can copy this formula from one of my columns to another and the formula will always point to the B column.
I also use this approach from another data source, the Economagic web site. They create a URL specific to a subscriber, which looks like this:
(The wconerly is the subscriber part; it costs $50 a year to have this feature available; well worth the money.)
If you get data from another source that offers this option, see if you can copy the URL for the spreadsheet into your own spreadsheet. If you want to try your hand, the website for my book has a spreadsheet with information on the cyclicality of consumer spending. See if you can grab cell F3, which should equal 57. The spreadsheet is at http://www.businomics.com/assets/docs/VulnerabilityConsumer.xls.