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?
I have a large employee spreadsheet list with first name then last name in the same cell. Is there a formula that will change the order so it will read last name, first name?
View 9 Replies View RelatedI wanted to exchange data between two cells. i.e if i have mistakenly put one value of A1 in A2 and A2 in A1. How can I exchange it. I am asking this bec by copy paste method one cell is overwritten so the value of that data is lost.
e.g A1 has value 200 but the correct value is 150
A2 has value 150 but the correct value is 200
How can i exchange/swap the values between the two cells..
Is possible exchange text in a cell like this "street San Miguel de Allende-Cp 4780 031 1304" to "street San Miguel de Allende, 1304 -Cp 4780 031". how to do this?
View 3 Replies View RelatedIs it possible when i am in my current workbook to refer to the value in another cell in another workbook within a formula?
Example:
I have a workbook named "Sample1.xls" that contains a spreadsheet "revenue" and in cell C24 is the value that i want to have in my current workbook.
As you can see in the attached sample file, I have different currencies, that all need to be converted to USD.
The exchange rate are displayed on sheet 2.
Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.
Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.
The formula needs to:
Find out what currency the value is in and automatically convert it to USD via the exchange rate values. It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.
I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
I work with different currencies in my company, now I would to get an up to date state of the cask book. So I have $250, and 500EUR, now I want a formula (connecting to internet) that automatically multiplies the $250 with the current exchange rate, so I know how much I have in Euros in total.
View 5 Replies View RelatedI have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:
View 5 Replies View RelatedI have two different columns of currency time series data with dates that do not align. One trades Monday-Friday, the other seven days a week.
I want to either a) add dates to the shorter series that only trades Monday - Friday (and just use the closing price on Friday for the Saturday and Sunday dates)
b) (not as good an option, as I will lose data) is delete the Saturday and Sunday data from the longer series that trades on the weekend.
Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C
And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.
In Sheet 2 I have a table of the historical exchange rates like this:
DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089
I'm looking at publishing a number of spreadsheets to the Public Folders section on our local Microsoft Exchange Server.
In Excel I can select File > Send To > Exchange Folder...
but I was wondering if there is a way to automate this in a macro?
I would also like to specify a folder it should be posted?
My task is to somehow access [url] and copy the exchange rates on the table to my Excel workbook. Obviously a macro will not record outside of the Excel program, so I am just wondering if this is possible at all (without hacking of course). My plan B is to have the user manually enter the exchange rates.
View 3 Replies View RelatedIs it possible in Excel 2013 to have one cell show current exchange rate of Euro to dollar?
View 1 Replies View RelatedThe file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8).
In between the process involves changing the currency from US$ to PKR, make some calculations, and changing back the currency again to US$.
The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate.
Problem:
Everything is working just too perfect. But probs occur when i manually enters the exchange rate.
It does successfully changes the US$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US$ successfully instead it keeps using the default value instead of user's value
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.
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 Relatedhow do i download historical stock prices for approx a hundred stocks i.e what code would suffice?
View 9 Replies View RelatedI 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?
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.
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] ......
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).
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
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.
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.
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.
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?
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.
i am trying to convert 100.50 to 100-16, agency mortgage price formats ...
View 9 Replies View RelatedI 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