Sort Out All Bid Prices For A Specific Security

Jan 4, 2006

I have a security order list. This consist of several columns most important are

Column A Column B

Sec a 15,00
Sec b 14,50
Sec c 16,00
Sec a 15,10
Sec a 15,70
Sec b 16,70
Sec a 14,80

The first column gives the name of the security, the second column gives the bid price.

What I want is that I want to sort out all bid prices for a specific security. Vlookup works fine but again it just gives the first record. How can I get all bid prices for security a on a new worksheet without blank cells. If possible I would like to have it sorted as well.

View 11 Replies


ADVERTISEMENT

Macro Security (saying That The Macro Security Level Is Too High)

Nov 18, 2008

I have a sheet that will not let me use macros. I can record them, but when I go to use them it says that the Macro security level is too high. I change it to Medium or Low but it doesn't make a difference.

The same spreadsheet works with Macros on another computer, but not my main one.

View 5 Replies View Related

Sort By Specific Custom Order

May 23, 2008

I would like to sort my column A by "Urgent, High, Medium and Low". However, when I click on sort, it obviously sorts alphbetically. Need to code to sort all colums A:K.

View 5 Replies View Related

Sort Pasted Data Into Specific Columns

Jul 2, 2014

how to sort data pasted from word into specific columns e.g.

I paste: 1,2,3,4,8 into the ref row, and it poulates as such, leaving out the columns for which there is no corresponding value:

Competency: 1------2--------3-------4------5------6------7------8
Ref: -------WR1----WR1----WR1-----------WR5------------------WR1

View 2 Replies View Related

Sort In Specific Manner And Create Booklet In Publisher?

Apr 17, 2014

I have a 1640 line database in Excel to sort in a specific manner and create a booklet in Publisher for a Family History group.So here is the data as it appears at the moment;

SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH
DATE 2
Date 3
SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH
DATE 2
DATE 3
So A to Z sort Column A BUT retain DATE 1-3 in the finished state,and same for next 400 groups.

View 4 Replies View Related

Sort / Move Data In Accordance To Specific Column?

May 9, 2014

How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB

View 9 Replies View Related

Custom Sort / Filter Workbook By Specific Text

Jun 6, 2014

Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...

View 3 Replies View Related

Remove Specific Text From A Cell And Then Sort Cells By Month And Date

Jun 10, 2014

I have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.

demo.xlsx

View 2 Replies View Related

Rounding The Prices

Dec 15, 2008

I am looking at trying to do a complex rounding within Excel. I have a spreadsheet of all my product with my cost. I then convert to show what margin I want to sell my items at and then round to .99.

What I am looking at doing is getting my pricing more uniform so that instead of having 23.99, it is 24.99 or 20.99 it is 19.99. I want all the numbers in the ones' spot to round depending on a set parameter.

I have played around with if statements, but can not get it to work fully. Basically if the ONES number is 0 to 2 I want it to round down to 9.99 (ie first was 21.99 I want it to be 19.99) from 2.01 to 4.98 to round to 4.99 and then 5.00 to 9.98 round up to 9.99.

View 9 Replies View Related

Total Weekly Prices

Sep 13, 2009

I have a spreadsheet with rows of entries that are listed by date. I would like to create anther worksheet where i can refer back to the other sheet and calculate the total prices per work week. Is there a way to do this using a function(s)?

View 14 Replies View Related

Historical Stock Prices

Jul 24, 2008

how do i download historical stock prices for approx a hundred stocks i.e what code would suffice?

View 9 Replies View Related

Update Ticker Prices

Feb 9, 2006

I have to update our ticker(US and Can stock) prices manually in our portfolio system.

I use www.bigcharts.com as our source . I can import the file in Excel. So what I would like to do is update the prices in Excel from the bigcharts.com. Not just today's prices but prices from previous day or two as well. Once I go to the website, I click historical quotes, then type in the stock symbol, and the date ,then write down the price.

Would Excel web query be the best way to approach this? If so, how would I import the the price for that particualr ticker for that date from the website into my excel?

View 9 Replies View Related

Even Distribution Of Units Across All Prices

Sep 15, 2007

Refer to the attached sample workbook,
(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)

Cell F19 should contain =D19*B19

Column 1- These values will not change and represent price/cost of each UNIT
Column 2- "UNITS" the quantity or number of items at column1 value
Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2

Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed

Ideally speaking, As few units in one row as possible to allow for a good mix.
For example: What i don't want is $9000*86 for 774k.

If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.

I believe a Most Even distribution of units will work fine.

After the distribution/population of fields in column2;

D29 must equal F3
F29 must equal F7

I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.

The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.

View 9 Replies View Related

VBA VLookup - Add All Prices For Certain Product In Different Tabs

Oct 16, 2012

I am trying to have a Vlookup Macro to add all the prices for a certain product in different tabs.

VB:
Private Sub CommandButton1_Click() 'Generate Info Button
Dim ws As Worksheet
Dim ISSUEPRICE As Integer
Dim RESULT As Integer
Dim sheet As Worksheet
For Each ws In ThisWorkbook.Worksheets

[Code] ......

View 3 Replies View Related

Adding Prices On Top Of VLOOKUP Formula

Nov 21, 2007

I have a sheet with a price list of papers and sizes. I then use this list to calculate the price of each order. It's a basic VLOOKUP and it returns the price. (this is the section outlined in blue on the demo).

However, i now want to add on another section so that if the order has Lamination (Gloss or Matt) this automatically adds on 20p or 25p respectively onto the sheet price. (table outlined in red but doesn't yet work).

View 10 Replies View Related

Prices Not Rounded To 2 Decimal Points

Mar 5, 2009

I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.

I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros

View 5 Replies View Related

Comparing Items And Calculating Prices

May 5, 2009

I have attached a sample spreadsheet for reference.

What I am trying to do is eliminate column 'J' on the Distribution tab. I want to use column 'C' on the Totals tab to calculate the subtotal based on matching the products on the Distribution tab in column 'E' with the Products list on the Totals tab in column 'A'. Once the match is made, I need to multiply the associated price in column 'C' on the Totals tab and the associated quantity in column 'F' on the Distribution tab with the result going in column 'K' on the Distribution tab.

I tried

=VLOOKUP(E:E,Totals!$A:$A,3,TRUE)*F:F

and

=SUMPRODUCT(SUMIF(E3,Totals!A3,F3)*Totals!C3)

but neither works and I do not know what else to try.

View 12 Replies View Related

Calculate Two Different Prices Based On A Given Quantity

Aug 29, 2006

I want to be able to calculate a price based on a given quantity. But here is the trick.

Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.

If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15

But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.

View 14 Replies View Related

How To Keep Track Of Changes In Prices Imported From Exchange

Sep 30, 2013

I work with a broker that have an API function and all the prices goes directly to an Excel Sheet.

What I want is a way to keep track of this prices in a new sheet, as they change. Like a historical price.

Is there any way that excel can do it for me?

View 6 Replies View Related

A Formula For Copying Prices With Exceptions

Dec 26, 2008

I am having a problem combining two databases. I have database E and F. I need to take any price that is $0.00 from Database E and replace it with the data from database F. The catch is that there isn’t always a price to replace it with. Also, there are parts in database E that do not appear at all in database F. Below is an example (there are about 20,000 lines of data total)

In the examples above I have placed both databases together. Column four has the database designation. As you can see the 1748 Hose reducer has a price for F but none for E.

Basically I need some formula like the following:

If (part number xxx) and (part number XXX) from column 1 are the same, replace the price data from E with the price data from F but only if E = $0.00

Is this possible?

View 9 Replies View Related

Linking Codes To Prices In Another Workbook

Dec 7, 2009

I have an excel sheet that has a list of codes and next to each code there is a price list for that particular item.

What I am trying to do it: I have another workbook that I use to type up quotes. In this workbook there are 2 columns: one with the code for the item and then another with the price thereof.

What I'd like to do is when I enter the code of the part, I'd like the price to be referenced from the other workbook and be entered into the price column.

View 9 Replies View Related

Converting Prices In Ticks To Decimals

Feb 12, 2010

i am trying to convert 100.50 to 100-16, agency mortgage price formats ...

View 9 Replies View Related

How To Increase Existing Prices By 10% And Round Up To Next Dollar

Jan 6, 2014

I need to do a price increase on an Excel spreadsheet. I need to increase existing prices by 10% and round up to next dollar. I am not sure to to accomplish this in one step on an entire column at one time. I can copy and paste the existing prices into another column and increase them by paste special multiply by 1.1 but not sure how to also roundup the prices.

I have enclosed a copy of one of the pages in the price list so you can see what I am attempting to do. As you can see I need to increase the prices in column D. Column E and F auto increase off of D so I do not need to do anything to those columns. ... Example.xlsx

View 5 Replies View Related

Calculate Prices Using Values Entered By Size

Nov 11, 2008

I am filling out an order form. My values are being entered as sizes. I would like to caculate any entery as 1 X the amount of the item, but the entry is for example.. "YS" and not the number 1.

View 4 Replies View Related

Calculate Weighted Average Of Prices For 12 Months

Jul 9, 2012

I wrote code that works just fine, however, I realized there were parts of the data I was using that had 0's in it for holiday.

Bottom line, this is code that calculated the weighted average of prices for 12 months (12 rows across) and Drows across. I'm trying to use the "do while is empty" approach but can't get the syntax to work. Here's the code

Code:
For r = 1 To drows
Totwavg = 0
Tothours = 0
priceavg = 0

For n = 1 To ncontracts

[Code] .....

View 1 Replies View Related

Average Price With Multiple Date And Prices

Aug 1, 2012

Is it possible with 1 formula and avoid to create 300+ individual formulas.

The background info: each day we check the prices of bananas, sometimes we check 2, 3 or 4 times in the same day, and sometimes the price that is checked is the same, but most of the time the price is diferent throughout the day.

We need to know the average dayly price for a particular day, while taking in consideration that some days we have 3 diff prices, and some others we only have 2 or even just 1 price that was checked and thats the price for that day.

we have the following spreadsheet

Column A = Dates
Dates when a price was checked, note here that there might be days of the year when in a single day the price is checked 2 or 3 times, and sometimes the price is different on the same day.

Column B = Price
The price checked on the date indicated in column A

Column C = Daily Avg Price Date
is just a date range from 08/1/11 to 07/31/12, a single day on each cell, that is intended to be the day reference for column D. C1 = 8/1/11, C2 = 8/2/11, C3= 8/3/11 and so on until 7/31/12

Column D = Daily Avg Price
In this column we need to show the average daily price calculated from the prices and dates in columns A and B.

For example: when C1 equals 8/1/11, then D1 has to equal the average price for the product, taking in consideration the amount of prices annotated for that day in column A, with the prices of Column B for that day and providing the average price.

I could have had an easy =Average formula but, since sometimes we have 3 or 2 or 4 equal or different prices in one single day in column A, I dont have a constant for the data range for excel to calculate an average.

My only current solution is to go on each cell on column D and writing the average formula selecting the range that would apply for that average price. But that is taking so much time, and i have multiple other products and year ranges and markets that require this same answer.

View 6 Replies View Related

Grouping Same Part Numbers And Adding Their Prices

Mar 20, 2008

I have the following Macro which groups same part numbers in an excel sheet and also creating a blank row after each (or set of same) part numbers. Their prices are in the adjacent cells. I want to add the price cells and the total to appear in the blank cell below the prices.
I have one part number in Cell say A1, Its price in Cell B1
i have another same part number in A2, its price in Cell B2
(The macro has grouped them together)
Now I want to add cell B1 & B2 and the result in B3 (Row 3 is blank, created by a macro after each group of similar part numbers)

This process is to be repeted in the entire worksheet.

Sub InsertRow_At_Change()
'part number
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value Cells(X - 1, 1).Value Then
If Cells(X, 1).Value "" Then
If Cells(X - 1, 1).Value "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Formula- Sheet For Calculating Prices For My Work

Jan 17, 2009

I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example:

If price is $0-$74.99 than add $50
If price is $75-124.99 than add $75
If price is $125-174.99 than add $100
and so on til $7000

View 9 Replies View Related

Calculate Prices Based On Quantity Discounts

Dec 5, 2007

with outputting a table with calculations based on the quantity that the user enters, and depending on that quantity, will calculate the price based on if he/she will receive the discount.

I think I should use an IF statement and calculate depending on if they receive the discount or not, but I don't know how to do that for one type of product code and not have to go manually put it in one by one.

I've attached a speadsheet to show how I should output the data. The prices will be listed in column E, with the prices depending on the quantity that the user will input with an input box and then if he/she qualifies for a discount.

View 5 Replies View Related

Altering A Existing Formula For Converting Bond Prices

Mar 31, 2009

--------------------------------------------------------------------------------

Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:

=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)

This allows me to type 101-16 in and excel recognizes this as 101.5

My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).

I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved