Matching Stock Portfolios With Differing Company Names
Jan 8, 2012
I'm trying to match stock portfolios of mutual funds with stock indexes to find out how much the portfolios deviates from the indexes (active share).
The deviation is simply calculated as the percentage of the portfolio holdings not included in the index.
The data I have is 10 years of monthly portfolio weightings for 30 mutual funds and 8 indexes, organized with company names in column A and the percentage weight for each company in each month in the adjacent rows (see simplified screenshot). I want to match all companies with all indexes.
Twofold:
1)The mutual fund data comes from one source and index data from another. This means company names are not spelled exactly the same.
2)Within the mutual fund datasets, a company might be listed more than once with different names. The different names refer to different periods in time, so what should be matched with the index is the sum of these columns.
The specific task that I need to accomplish is to match company name in the portfolio with the index and then return the absolute difference between weight in the portfolio and the weight in the index for each month. (see screenshot).
Hence the result I'm looking for is a matrix of company names (of each portfolio) in col A and the absolute difference between the portfolio and the index weight in the adjacent row.
The best solution I've come up with is to device a list of all the portfolio company names, including "doubles", and then for each index, add these company names and copy the row weightings. This I'm not particularly happy with because index weightings would no longer sum to 100 and it would be difficult to check for errors. Also, it would still take a lot of time.
View 6 Replies
ADVERTISEMENT
Jul 20, 2014
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
View 3 Replies
View Related
Mar 9, 2008
What I have is an output of about 30 worksheets of individual stock data in a workbook, all with the same column headings, laid out like the following (so that the Column Headings are stock data as recorded on the Date in Column A):
<Stock Name>
<Date> <Price> <Price to Book> <etc.>
with the stock titles also as the names of the worksheet.
What I want to do is create a macro that outputs to a single worksheet where I can input the stock name and a certain date for the stock, and then have it insert the appropriate row from the stock's worksheet underneath.
So, if I say I want the data for Stock A on March 24, 2007, I can input those two and have it output the appropriate rows from the worksheet.
Or, more generally, I can create a list of stock names and dates, and have the macro output that data underneath.
View 3 Replies
View Related
May 15, 2014
I am trying to match up company codes across three different columns. I am inserting rows to get them to match up. However, there has to be an easier way.
1017
1017
1027 Total
1051
1021
[Code] ...
View 8 Replies
View Related
Oct 29, 2012
I need to map two lists of company names. 1st list is master list having around 1lakh company names. 2nd list have around 500 records which i need to manually match bcoz index and match functions just dont work.
What i currently do is copy a part of company name from list2, go to list1 and apply a contain filter and if any matches are found, take the closest match to list2.
This is a tedious task for 500 companies as it involves to many mouse clicks. We just need to paste a part of company name in search box and a contain filter is applied to list 1.
View 5 Replies
View Related
Feb 6, 2013
I have a 97-2003 spreadsheet with approximately 22,000 lines of seperate companies. I need to know how to insert a blank line between the company names. I have been using the "Right Click - Insert" method but soon realized there must be 2000 different companies. The Company Name column is A.
View 7 Replies
View Related
Jan 28, 2010
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.
I should also point out that most company names appear multiple times in column [C7].
a) Count the unique company names in column [C7]...
b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records
c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records
Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
View 2 Replies
View Related
Jun 26, 2014
For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. I also have a sample file.
I have, on Sheet1, 8 columns I'm working with:
R3:R132 is the currently hourly rate
L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.
AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.
AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.
On Sheet2, I have 4 columns I'm working with:
A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.
They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.
My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:
1) Attempt to reduce labor hours to get to suggested hourly rate - This is to calculate new labor hours on Sheet1 in cells AF3:AF132.
View 1 Replies
View Related
Dec 11, 2008
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
View 5 Replies
View Related
Jun 1, 2006
I have a nested if formula that isn't working and I know there is a real simple fix.
Here is the situation: If Company A (A1) and Company B (B1) both have indices greater than 125, I want that to show that in my recommendation. For example: I used if(A1>125&B1>125,"Company A and Company B",if(A1>125,"Company A", if(B1>125,"Company B","None"))). For whatever reason, the first nested if statement isn't working correctly. what I am doing wrong with this if/and statement?
View 3 Replies
View Related
Nov 11, 2013
I am creating 100 random portfolios from a list of 10 assets. I have the return history of the 10 assets and I'm able to calculate the mean and standard deviation of each asset as well as the covariance/correlation between each of the 10 assets. I want to choose to portfolio with the least variance as well the portfolio with the lowest and highest return.
Would it also be possible to introduce a limit that constrains the maximum allocation to each asset at 25%?
View 7 Replies
View Related
Aug 7, 2012
What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies
View Related
Jan 8, 2014
I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
View 7 Replies
View Related
Aug 18, 2014
I have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name and the other column lists the names as first name and then last name without the comma .
I am trying to see if the list of names in one column exists in the other.
Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?
Name Lists (1).xlsx
View 3 Replies
View Related
May 1, 2009
I'm trying to find a way to check if the first and last name that are the content of cell A are anywhere in cell B and beyond. I've included an example: http://tinypic.com/view.php?pic=28cpkr9&s=5. In the example A1 must match B1, C1 and C2.
View 5 Replies
View Related
Mar 24, 2008
I have a list of names in a column and another list of names in another column. The columns are named patient and user. Is there a way to right a formula that would highlight a name match when a name matched in the two columns in the same row?
View 9 Replies
View Related
Sep 21, 2005
I have 2 reports that I want to merge which are exported from different applications. The problem is that the names appear in a different format and I can't work out how to match them up. The fact that not everyone appears on every report I have solved by using lookup as in the attached workbook. What I need to do is use a formula that will recognise the two different name formats and match them up. i.e. Campion, Ben P. and Ben Campion. See attachment.
View 6 Replies
View Related
Sep 1, 2009
In Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
View 2 Replies
View Related
Jul 17, 2014
I have a worksheet that has a compilation of reports, each of which has multiple columns that aren't necessarily organized in an Excel-friendly way. However, the first column contains a list of "name" headings (titled "Name") and the second adjacent column always lists the actual name right beside it. Similarly, each person has a value heading in the first column (titled "The Value") and the actual value in the second column. What I would like to be able to do is have Excel search for the Name heading, paste the value in the adjacent column (the actual name) in a cell in a new worksheet, then search for "The Value" and do the same thing in the column adjacent to the one where the actual name was copied. The catch is I need Excel to do this and then search again for "Name", skip it, and start again with the first part of the procedure until all names and their associated values are matched. how to do something like this in VBA?
View 6 Replies
View Related
Sep 3, 2012
I'm working on a project that has 2 sheets. the 2nd sheet has the raw data and the first sheet will be the sorted data.
To deliver the raw data on sheet 2 to sheet 1, I need the different names on sheet 1 to match to its corrosponding name on sheet 2 then deliver the value in another cell. I don't need the zero values but on some occosions the zeros and numerical values will be switched so i will need a formula that will deliver the numerical value to sheet 1 after it checks which cell contains the numerical value between columns B and C. there will always be a zero and a numerical value in either column B or C for each.
It will look like this. (assume 3 rows A, B and C and each value and name is in its own cell).
Sheet 1
Name
$
Interest
Cash
Dividends
[Code] ........
View 9 Replies
View Related
Jan 12, 2010
writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.
e.g Company = Co. or
Insurance = Ins
Please see my attached excel document for an example.
I have over 27000 lines so doing this manually will take forever!
Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.
Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.
The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.
View 10 Replies
View Related
Sep 25, 2011
I have a list that looks sort of like this:
Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..
This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:
Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...
I tried using the vlookup function but then it confused me when i also needed to match the names with their respective service days.
View 7 Replies
View Related
Apr 5, 2007
I have an excel sheet which displays the data in the following format:
(See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates.
I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
View 4 Replies
View Related
Nov 26, 2012
I'm trying to do a countif that matches 2 names and returns the amount of times the 2 name matches is in the list. Ihave first names in column a and the surnames in column B.
And there are lots like so
John
Greenwood
Bill
Griffiths
Melanie
Griffiths
View 3 Replies
View Related
Oct 21, 2013
Identical Data exists on Sheets1 and 2 (A3:V50)
A3:A50 = human names
B3:V50 = numeric values
I'm trying to VBA code:
If human names on Sheet2(A3:A5) match human names on Sheet1(A3:A5)
Move Sheet2 matching name row(s) to the bottom of the data range on Sheet2.
Sheet1
Sam 4 4 4 4 4 4 4 4 4 36 4 4 4 4 4 4 4 4 4 36 72
Bill 4 4 4 5 4 4 4 5 4 38 5 5 4 4 4 4 4 4 4 38 76
Bob 5 5 5 4 4 4 5 5 5 42 4 4 4 4 4 4 4 4 4 36 78
[Code] ......
View 3 Replies
View Related
May 21, 2014
How do I create a macro to sort rows into groups, add break rows in between & insert a product sourced from another excel workbook onto the break line?
I receive a spreadsheet each day with a series of requests, most of the information is the addresses & names etc with the important data in columns K,M & O. Data in each of these columns is a code that refers to a product and information sheet to be sent out to the person requesting it. They may want just one in which case data will only be in column K, if they want two there will also be data in M and if they require three there will be data in column O as well. The bit I do know is how to sort the important data into groups of the same by the country (column I) and the product sku's.
Whilst I can do the sort as a recorded macro I really need it to be part of a larger macro that does the following bit I can't get my head around. I need to insert a break row above each group then by cross referencing the data in the product sku columns to a separate workbook insert the appropriate item / letter combination onto the break line row, telling the volunteer which items to send out. This is a v look up?
I have attached an example of the files we work with, the first tab is with the data simply sorted and the second is what I need it to look like. I normally receive it as a CSV file, there are around 8000 possible combinations of product and instructions and the files are normally around 4000 records long. At present we insert the break lines and copy/paste the information manually, seriously slowing down the process.
Orders Example EUR RM.xlsx
View 12 Replies
View Related
May 21, 2013
I need a macro to create a hyperlink i.e....... I have an excel sheet with numbers in column A.....I have files in a folder that matches the numbers in column A....
How would I create a macro to create a hyperlink from the numbers in column A to the "matching" file names in a folder?
View 3 Replies
View Related
Aug 7, 2013
I'm trying to work out a way to use a MAX function and IF Function to determine how i can find the highest number since a trade began. For example, i have a countif function that is identifying each trade (Column B) so what i want to do is to find the highest number for each trade (Column A) which is represented by dummy variable 1,2,3,4 etc. Please see example below: what i want to do is write code which runs for 3500 lines and finds the highest price since trade began and trade's are represented by 1,2,3,4,5,6,7 etc.
Close
Number
TRADE PRICE
HIGH PRICE
0.6347
1
$0.63
0.6565
0.6373
1
$0.63
[Code] ........
View 7 Replies
View Related
Dec 21, 2009
I want the macro to go to column F and copy down from the row containing AO NAMES down to the row containing TOTAL with the value within cell C3.
So basically AO NAMES will be the starting point and TOTAL will be the end point for a value to be copied down in a different column.
Example: Value in C3 = EU11
Column B Column F
AO NAME EU11
Chris Wright 1355 EU11
Hazel Thomas 4015EU11
Julie Cunningham 8426EU11
Dawn Cumming 8748EU11
Jacqui Connolly 16597EU11
David Newton 17978EU11
Duncan Cowen 25781EU11
0EU11
0EU11
0EU11
0EU11
0EU11
TotalEU11
View 5 Replies
View Related
Mar 18, 2007
I need to return a value if multiple criterion are met from different rows. I tried an if and formule but will not get a value since information is not entered on the same row. My criteria is text and numbers.
View 9 Replies
View Related
