We having some issues with Excel and Bloomberg. We are pulling daily data from bloomberg using the compustat database's records of S&P constituents, but it is taking an extremely long time to pull the data for the S&P 100 and 500. The s&p 100 constituents will take around 2 hours to pull, and we're estimating that for the 500, it will take about 10 hours. We are also having problems with excel crashing due to the size of these spreadsheets (around 11,000,000 data points in our S&P 500 spreadsheet). Any suggestions that you could give us would be greatly appreciated.

Sorry to hear you're having problems. These are big files, others are around 1GB or so. Here you really kind of have to leave the Excel world; it generally will not able to deal with it. Office 2007 can only handle 1 million rows.... R has problems enough, but will work. Much bigger files than these are found in the business world, but most everyone uses SQL databases. We typically query the database to get toy datasets for Excel analysis. If you cannot use R or SAS, then you might have to split the exercise into 30 downloads for only one year?

BTW, How is Bloomberg getting the data? I thought it only went back to around 1985? Please write-up your Bloomberg procedures since this is unique and we would appreciate seeing it.