• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Combining data from two different sheets?

Joy Pathak

Joy Pathak

Swaptionz
Joined
8/20/09
Messages
1,330
Points
73
Hey guys, so I have a small problem.

I have two excel sheets. One of them has company names and years... and a set of variables with data.

I have another excel sheet with company names and years...and another set of data with years.

Is there a way for me to combined these two sheets with the data automatically getting put in columns with the appropriate years and names of companies matched?

The company names on both the sheets are different with many similar companies. I want to be able to have the similar companies that are on both the sheets matched together in a sheet.

Any help will be appreciated.

My only alternative is...going through every data...looking through...and copy pasting similar company data on one sheet......and I have 11,000 rows of company names/years.
 
I guess I don't fully understand your problem but sounds like a simple vlookup?
 
Well....

I have..

1000 companies in a data set I got from compuSTAT with all kinds of vairables...long term debt...current assets..etc.

I have another set of companies.. 600 lets say...from SDC (TR) database with Principal ammount of debt issued.


I want to see which companies SDC database put out thats similar to the compustat companies and put the ammount of debt issued in a column next to the companies on compustat dad with the year they were issued... there are years on compustat also..so the years have to match also.

That is my dilemma. But I will look into what andy suggested. thank you.
 
The MS Query worked for the company names well. Unfortunately Debt issued dates were different than the compustat data.

Just trying to get the dates to match.
 
Alright. So...the two excel sheets company names were in different formats, and the for one year there were mutiple observations on one sheet and only one on the other. So they had to be added and put in the one cell.

Unfortunately...Going to have to add it manually per year...then join through Query all the companies with similar names...and add the ones spelt different manually.

Wow. I feel like a robot.
 
It's not even the fact that I hate it or anything.

It's just....soooo....manyyyy.

I am slaving through. Once I get done, the actual "data crunching" part is only like 5 mins on SAS or PASW. But getting the data cleaned up and set up is the gruesome part.

The issue is also financial. The databases I have access to have not subscribed to the entire database. The same variables can be extracted from the 1 database but no subscription to those variables so have to take from 2 different and combine.
 
I've used SAS to merge datsets..... it is really simple and fast....
 
Yeah. The issue comes when the formats of the string variables are different. Otherwise I could just use query.

Also one database had 5 different values for each year that had to be added and matched with the year and company name on another set.

It got a bit intense.
 
Back
Top