How to merge multiple excel files?

Question

The whole idea is the following, I get monthly reports in excel files about large number of companies with couple of columns of data such as their closing share price for month, rating, p/e, and maybe one more column. Each company is sorted by a unique company ID, but from month to month the reports will contain about 70% records of same companies while for the rest some companies from previous months will not be there, and some new companies will appear.

So I have around 10 Excel files by now, and I need to combine them into one excel file that will have data sorted by months and in column it will have companies sorted as they have unique company ID. So if company has records in only two months it will place records of it in exact same months, if it has in all, it will place data in all months..

How can I do this? I need help, and I know only Excel and Access..

Answer 1

Unfortunately, I don't have a simple, one-click solution for you.

If the information is uniform in that for instance that Company ID is always in column A, P/E in column B, etc. Then it is fairly trivial (if you know some basic programming) to write a macro that extracts the information from the excel files and puts it in one file. I've done many such mini-applications, aggregating data from different excel files into one.

The macro editor (VBA-editor) is very powerful and the help-files are fairly complete, so if you know at least some basic programming, and the data is uniformly presented in the spreadsheets, then you shouldn't have a problem writing a macro that extracts the information.

Just to get you started.

Public Sub GatherData()
    Dim wb as WorkBook
    Dim ws as Worksheet

    set wb = Application.Workbooks.open("file.xls")
    set ws = wb.worksheets("sheet1")
    ws.cells(1,1).value = "Hello"

    wb.save()
    wb.close()
    set ws = Nothing
    set wb = Nothing
End Sub

This is written freehand, and might contain coding mistakes, but should give you an idea on how to write the macro. A good way of learning the syntax is to record a few macros and do some basic operations and then look at the code being generated. It gives hints to what operations are available. Hopes this helps.