Friday, July 01, 2005

My first macro

I've been doing some really repetitive data work in excel so I decided to automate some of it.

Sub sel()
Dim id As Integer
id = Selection.Value
Windows("Adis Data.xls").Activate
Sheets("1997").Select
Selection.AutoFilter Field:=1, Criteria1:=id, Operator:=xlAnd
Sheets("1998").Select
Selection.AutoFilter Field:=1, Criteria1:=id, Operator:=xlAnd
End Sub

I love computers...

Leon's work on the AEI website

Monday, June 27, 2005

The Library of Congress

Some background: I've been looking for information on what percentage of rent goes to pay real estate taxes. Thankfully the Census Bureau (in conjunction with the commerce department and the housing and urban development department) publish the residential finance survey with every census. The 2001 version hasn't been summarized but the raw data is available. The 1991 version has a summary available. But the census bureau hasn't bothered to put the 1981 version on the web and still sells it on a reel to reel computer tape for $175. Needless to say, this wasn't an option. After many days of futile searching, today I discovered that the 1981 residential finance survey was better known as the 5th volume of the 1980 census of housing (apparently there are 3 censuses: population, housing, and population & housing). With this information I was soon able to track the volume down to several area libraries, the nearest of which was the library of congress. After a short discussion of the complexities of getting into the LOC, I set off for it at around 11:30. I grabbed a quick lunch (at the local Quizonos, the AEI restaurant was closed anyway) and took the blue/orange line from Farragut West to Capital South. From web research and the previous experiences of a fellow intern, I headed to the Madison building to get my card. The process was surprisingly quick (20 minutes?) and I was off through an underground tunnel to the main reading room in the Jefferson building. After learning the drill from a helpfully librarian, I put in a request (only librarians are allowed in the stacks) for 1980 census of housing. Volume 5, Residential finance. It turned out that this volume was in the Madison building, and would thus take longer to get (they had signs up that said jefferson building requests were expected to take 60 minutes, madison requests were to take 90 minutes). So I waited, and waited, and waited. I had my laptop (although I didn't bring an ethernet cable to access the web) so I had stuff to do. But after 3 hours, I was getting a little board. I check with a librarian and was told that they'd check on my order. A half hour later I was told that my request was on the way. A half hour after that I finally had the volume. It was laid out just like the 1991 pdf file, so I quickly found what pages I was looking for. The data was broken up into three groups based on how many housing units were on a property, and I quickly got data for the first group. However, the second group's data was missing: ~150 pages were gone. At one point the pages jumped from ~400 to ~650 without noticeable damage. To further confuse things, pages 650 to 800 or so were repeated verbatim at the end of the book. The data for the third group fell shortly after the pages reappeared, so it was there, but the second group was nowhere to be found. I went through the whole volume several times and it simply wasn't there. I talked to a reference librarian who was similarly baffled. She suggested that I request the library's second copy. It was almost 6 at this point so I put in an overnight request, so it should be there tomorrow in case I get sent back in. All in all, not a very productive day. Thankfully, I had a copy of the Apple 1 manual on my computer so it wasn't a complete loss.