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?
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.
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.
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] ...
I have been tasked with trying to find out the permissions on a set of folders on a company network. I know write vba code to manipulate a excel spreadsheet without any problems, but it is when i'm using objects that it starts to go beyond my capability. whilst searching i found this code:
Code:
Sub test1()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const FullAccessMask = 2032127, ModifyAccessMask = 1245631, WriteAccessMask = 118009
Const ROAccessMask = 1179817
[code].....
It does a perfect job but puts the data into an html file. i managed to get it to input the data into the spreadsheet as well but i would like to clean this code up so that it is not producing another file (namely the text file or the html file) and just put the results into the excel file I have entered the module into.
I did start to remove the references to any file name, but that is where i run into trouble and it wouldn't run any more due to object not found.
I'm on Win 7 using Office 2010
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?
I have two sheets that both have all the same company names on them but one of the sheets doesn't have ALL of the company names. So, one sheet might have 2500 rows with company names and data specific to that sheet, and the next sheet might have 2450 company names (all match up to the first sheet, but 50 are missing), with different data.
The first sheet is A through L, the second is A through K.
Attached is a sample spreadsheet expressing
First sheet:
A:[Name]
B:[DBA]
C:[Owner/Officer_1]
D:[Owner/Officer_2]
E:[Owner/Officer_3]
F:[Owner/Officer_4]
G:[Phone]
H:[TxDMV]
[code]....
Company Name and Name will match EXACTLY on each sheet, but the second sheet won't have quite as many. So, if possible, I would like to, where column A in matches in both sheet 1 and 2, move or copy all the data from sheet two into the columns on sheet one that come after L.
What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies View RelatedI have a spreadsheet in which I am trying to track invoices billed according to month in question. For example, I need to find total invoices billed in February for ABC Consulting Company. I have a database in the same spreadsheet that contains all invoices billed for an entire year for all companies. How do I pull invoices for a particular month only, in this case for the month of February? I have attached an example of spreadsheet in question. Included is a tab which indicates desired results.
View 5 Replies View RelatedI 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?
Conditional formatting: If a cell contains a string, say "NCT" or "nct", conditional format shall work. The string may be with a date entered i.e. "05/05/2006 NCT"
Same said cell is currently set to only allow dates. How can i achieve that Data Validation: Can data validation be set to only allow a date, and/or "date NCT" as above?
I get a ledger dump every month with 20,000 lines of all transactions. There are 1,200 ledger codes, ten departments, and thirty activities. Each of the 20,000 lines has columns with the ledger code, a department, an activity, and other data. There are tens of thousands of possible combinations of ledger code, department and activity.
I have most of the ones which are commonly used every month, and I use them to create a =sumif summary which totals all the same concatenations of ledger code&department&activity. However, every now and again a new permutation arrives and I need to find it and then create the new concatenation for the =sumif to use in the summary. Is there any way to do this by running a routine which will locate the new combination and create the concatenation in the =sumif summary?
I am using the following vba code to find numeric codes in a column and replace them with the same code along with descriptive text:
Range("Q:Q").Select
Selection.Replace What:="11", Replacement:="11 - GSA"
Selection.Replace What:="10", Replacement:="10 - NYS OGS"
Selection.Replace What:="1", Replacement:=" 1 - Dist Sale"
Selection.Replace What:="2", Replacement:=" 2 - Direct Sale"
Selection.Replace What:="3", Replacement:=" 3 - No Sale: Warranty Exchange"
The problem as you might already see is that when the replacement of "1" happens, Excel will replace the 1 in the 10 and 11. Of course I want to only replace the number 1. Is there a better way to code this operation?
, just by using the ISIN code of a Bond in bloomberg, to get in excel:
- Coupon
- Cpn Freq
- Day Cnt
- Maturity
I've been tasked with creating an updated booking system for a company which offers projects / services to the educational sector (UK Primary Schools.) I've done an alright job at augmenting their current Excel based system, but I would like to take it further so that the system is more all encompassing.
Currently, the booking form is filled in when a booking is made and the data from that is populated into their copy of the contract and our copy of the contract using simple "=" and to a certain extent, the text on the contracts is manipulated based on the booking form data using "IF" functions.
There is a basic macro which removes excess lines from the contracts if they aren't used, but that's about it. The difficulty we're having with the current system is that a lot of our projects are bespoke, happen over more than one date, and require a lot of manual manipulation in order to get them ready, which sort of defeats the object of having a 'clever' booking system.
I don't know very much about VB, but the sort of thing I would be looking to integrate is having drop down lists to select a 'project' which would load project-specific text into the contracts area. Also, instead of having to remove lines manually, I would like the data to be 'dropped in' creating rows as appropriate.
My question is how many parts havinig end with C?Also arrange these parts in ascending order in a new column. Refer to the attached file and expected result also.
View 5 Replies View RelatedI've created a Excel spreadsheet. In the Spreadsheet I've got one worksheet which has columns and list of codes.
I would like my other worksheet to look through find any products I've ordered and list them with their codes and quantities, creating a summary of just what I want so that I can e-mail just the important bits.
I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.
View 6 Replies View RelatedI got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)
the second next to it this =COUNTIF($D:$D;$D5)
and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
Example
4905524490831DPFV900B.CEU0649055244908312DUPLICATE
4905524490831DPFV900B.CEU502DUPLICATE
I am looking for this one function i need for a school project on historical stock prices in excel.....basically i need a way to perform this function:
In row 1, i have 30 RANDOM historical dates, starting in B1 and going right
In column A, i have 10 stock symbols, starting in A2 and going down
Is there a way to automatically grab the ADJUSTED CLOSING PRICE for each symbol for each corresponding date?? if not i have to do it manually!!
I have an excel file, attached. I have a system that output all the stock in the format of sheet "price listing".
At the moment I manually create the layout for the text in "sheet 1" and use a macro to update prices etc. There are about 400 lines in the full listing and having to update the layout constantly for new products, deleted products is very time consuming. I send this list to customers every few months for them to see the range and the prices, so it has to look well.
I am looking for a macro or a pivot chart or something that I would be able to run on the "price listing" sheet and would put it in some usable format. Different customers can have different prices so that it needs to be quick and flexible. I send this list to customers every month for them to see the range and the prices, so it has to look well. Is there any way to create an index also from an excel workbook?
I am trying to do in the title so here it goes. I want a user to be able (on form 1) to enter a list of codes that refer to specific requirements (e.g. code PB114 may mean the user needs to perform a pressure test) and then excel will automatically display the requirement somewhere below. It is going to be somewhat of a database. In otherwords lets say in cell A1 you type in a code, e.g. PB114. In a range of merged cells or somewhere below that cell I want the information "Perform a pressure test" to appear.
View 5 Replies View RelatedHow to loop a macro which downloads historical stock information into Excel. I had the macro working for a single sheet, but I want it to update all of the sheets in the work book except a few. Now I keep getting a Error # 1004 unable to open error on line .Refresh BackgroundQuery:=False
Code:
Sub GetData()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
' Exclude certain sheets
If ws.Name "Porfolio" Or ws.Name "Benchmark" Then
[Code]....
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A5"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
[Code]....
I would like to use this database list I export from another program to excel format. What I want to do is to change in the C column.
The value under Akt.saldo is how much of a given article we have in stock (saldo). And the Rest. in column B is the reservation, how much of that article thats is going to be shipped at that day. ex 14161 year(14) week(16) day(1).
So as you can see in column C the Rest. value never subtracts from the stock. What is want is something like that below.
Here is just selected all the values under stock and moved to the next column. Then used white color to hide it.
Here i subtract the hidden value with the reservation to be able to see how much is left after that order.
And the same here. Subtracting next order with the current stock.
How do i do this with a macro? The list is changing everyday.
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.
I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .
And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.
First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it. In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.
Next I need labels added to the tops of the rows
Next I need the description columns in C to be copied to column I
Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data. Attached is sample data.
I have an excel workbook with two worksheets. The first worksheet contains a lot of data with more than 20.000 rows and 20 columns. The important part is that there are two different columns (column F and I) containing codes of items. What I would like the macro to do is the following:
write codes of items in the second worksheet column B under each other. I would like the macro to lookup the codes from this column in the first worksheet (column F and I) and if it finds then display in worksheet 2 column C the column in which found it on worksheet1. So basically I write on worksheet2 column B many many codes and then I press a button to do a search and displays next to the items (in column C) if it found the code in worksheet1 column F, or column I or both, or did not found it.
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.