Tuesday, December 27, 2011

Fed Loan Data Part 1

This is the start of analyzing the Federal Reserve and Banking data mentioned in my "A Christmas Miracle". The file is a combination of summary data and actual data from each of the 400+ banks that recieved funds from the Federal Reserve during 2007-2009.

Like so many data sets there are some data clean up challenges. The first is the use of excel, which is not a problem, but the authors decided to add considerable headers, and unusual formats to the summary tables. Here is my attempt to get one of the first data files cleaned up and working. One big problem is changing $1,000.00 into 1000.00, I have some code below, but would appreciate any help in making my code better. Below are the summary graphs of the data:








The basic graphs are pretty self explanatory, for fun I did a regression to see if there is any correlation, the first regression was okay, but i noticed it could use a semi-log transformation. After taking the log of the average daily balance, i got a much better looking regression as well as r^2.




Below is my R code:



#Fed Data
library(stringr)
fed.01<-read.csv(file.choose(), header=T)
summary(fed.01)
 
#Cleaning up the Data- removed the $ sign and the ',' in 1,000
average<-str_sub(fed.01$Average.Daily.Balance..in.Millions.of.U.S..Dollars.,
                 start=2, end=-1)
average<-as.numeric(gsub(",", '', average))
fed.02<-cbind(average, fed.01)
 
#Exploritory Graphs
hist(fed.02$Days.in.Debt, main='Histogram of Days in Debt',
     col='red', xlab='no. Days in Debt')
years.debt<-fed.02$Days.in.Debt/365
hist(years.debt, main='Histogram of Years in Debt', col='red',
     xlab='no. Years in Debt', breaks=15)
 
#Bar graphs the the data
#The country of origin
par(las=2, mar=c(5,12,4,2), mfrow=c(1,1))
country<-sort(table(fed.01$Country))
barplot(country, main='Nation of Banks', col='blue', horiz=TRUE)
 
#Type of Bank or Industry
par(las=2, mar=c(5,17,4,2), mfrow=c(1,1))
industry<-sort(table(fed.01$Industry))
barplot(industry, main='Type of Industry',
        col='blue', horiz=TRUE)
 
#Organizations with average balances greater than $5 billion
five.bill<-subset(fed.02, average>5000)
par(las=2, mar=c(5,19,4,2), mfrow=c(1,1))
barplot(sort(five.bill$average),names.arg=five.bill$Company,
        main='Companies With Average Daily Balance Greater
        than $5 Billion', col='blue', hor=TRUE)
 
 
#Organizations with debt more then 730 days (2 years)
year.comp<-subset(fed.02, Days.in.Debt>730)
par(las=1, mar=c(5,20,4,2))
barplot(sort(year.comp$Days.in.Debt),
        names.arg=year.comp$Company,
        main='Companies With Days of Debt Greater
        than 730 Days (2 Years)
        days', col='red', hor=TRUE, xpd=FALSE, 
        xlim=c(720, 830))
par(las=0, mar=c(5,4,4,2))
 
#Regression of Days in Debt to Ave. Daily Balance
 
#ploted the data, the r2 is poor, and the slop is positive, 
#nothing to get too excited about, took the log
plot(fed.02$Days.in.Debt, fed.02$average, xlab='Days in Debt',
     ylab='Ave. Daily Balance', main='Scatter Plot:
     Daily Balance and Days in Debt')
lm.01<-lm(fed.02$average~fed.02$Days.in.Debt)
abline(lm.01)
summary(lm.01)
 
#log of fed$average to reduce the outliers
log.aver<-log(fed.02$average)
plot(fed.02$Days.in.Debt, log.aver, xlab='Days in Debt',
     ylab='Log of Ave. Daily Balance', main='Scatter Plot:
     Log Daily Balance and Days in Debt')
lm.02<-lm(log.aver~fed.02$Days.in.Debt)
abline(lm.02)
summary(lm.02)
Created by Pretty R at inside-R.org

Friday, December 23, 2011

A Christmas Miracle

Data files on 407 banks, between the dates of 2007 to 2009, on the daily borrowing with the US Federal Reserve bank. The data sets are available from Bloomberg at this address data 

This is an unprecedented look into the day-to-day transactions of banks with the Feds during one of the worse and unusual times in US financial history. A time of weekend deals, large banks being summoned to sign contracts, and all around chaos. For the economist, technocrat, and R enthusiasts this is the opportunity of a life time to examine and analyze financial data normally held in the strictest of confidentiality. A good comparison would be taking all of the auto companies and getting their daily production, sales, and cost data for two years and sending it out to the world. Never has happened.

Thank you Bloomberg for making it available and Drudgereport.com for the link to it.