JJ Webb - FDIC data generation

Excel Code File: http://files.gove.net/shares/files/10w/jjwebb90/JJWebbFDICProject.xlsm
PDF WriteUp: http://files.gove.net/shares/files/10w/jjwebb90/JJWebb_ProjectWriteUp.pdf

My friend needed to download and save separate reports as excel files from the FDIC website. The challenge was that they are dynamic pages. The program had to go through steps online to retrieve each report. For my project I automated this process for him.

The FDIC site offers statistics on depository institutions. You can generate historical reports by going to http://www2.fdic.gov/SDI/main.asp and going through 12 steps including selecting radio buttons, selecting check boxes, and navigating through pages. Then you can save the report to Excel. Because a large number of reports needed to be generated, this process was time consuming and cumbersome.

I found a really cool way of automatically generating and saving these reports using VBA. A major part of the solution was discovering that each of the steps to generate a report doesn’t actually take you to a new web page (even though it appears to). Each “new page” was actually just part of a form that adds the variables you select to create a final URL for the report. So I drilled down to the code on the webpage to identify what the URL of one of the final report looks like (shown below).

“http://www2.fdic.gov/SDI/download_exect.asp?selInFlags=None&selections=28000%2CSZLNCRCD%2CSZLAUTO%2CSZ30CRCD%2CSZ30AUTO%2CSZ90CRCD%2CSZ90AUTO%2CSZDRCRCD%2CSZDRAUTO%2Cszuccrcd%2Cszucauto&selInReportDate=12%2F31%2F2009&IncomeBasis=&NumOfRpts=59&repincbas0=&repincbas1=&repincbas2=&repincbas3=&repincbas4=&repincbas5=&repincbas6=&repincbas7=&repincbas8=&repincbas9=&repincbas10=&repincbas11=&repincbas12=&repincbas13=&repincbas14=&repincbas15=&repincbas16=&repincbas17=&repincbas18=&repincbas19=&repincbas20=&repincbas21=&repincbas22=&repincbas23=&repincbas24=&repincbas25=&repincbas26=" & _ "&repincbas27=&repincbas28=&repincbas29=&repincbas30=&repincbas31=&repincbas32=&repincbas33=&repincbas34=&repincbas35=&repincbas36=&repincbas37=&repincbas38=&repincbas39=&repincbas40=&repincbas41=&repincbas42=&repincbas43=&repincbas44=&repincbas45=&repincbas46=&repincbas47=&repincbas48=&repincbas49=&repincbas50=&repincbas51=&repincbas52=&repincbas53=&repincbas54=&repincbas55=&repincbas56=&repincbas57=&repincbas58=&repincbas59=&SQL=SELECT+stru.Cert%2Cstru.MSA+from+risstru+stru" & _ "++where+stru.Active+%3D+1+++++++++++and+stru.repdte+%3D+%27" & Replace(CStr(DateAdd("q", quarters, CDate(theDate))), "/", "%2f") & "%27++order+by+stru.NAME+&submitButton2=submit"

By manipulating this URL I generated the correct reports. As an example of how this works, look at the variables that follow %2C such as %2CSZLNCRCD and %2CSZLAUTO. By including these variables in the URL I indicated that I want the report to include bank securitization activities in relation to the banks’ principle balances for credit cards and auto loans (CRCD = credit card & AUTO = auto loans). If that doesn’t make a lot of sense, then hopefully the project write up will clarify it.

With the URL for the information I needed, I used VBA code to save the data as an excel file. Then I used a loop to automatically change the date of the quarterly report (Replace(CStr(DateAdd("q", quarters, CDate(theDate))) and save the reports going back 5 years.