Hedging Simulation, Solver To Obtain A Maximum Profit
Apr 18, 2007
Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.
I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.
I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.
What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?
I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).
ComponentMTBF(months) 115 224
Below are Company B's sales records for the past 36 months.
Has ever created a spreadsheet that used Monte Carlo Simulation?
I've been able to create one that works using the random number generator in conjunction with discrete probability distributions - that's fairly easy, just assign a probability to each discrete value in the distribution and then decide whether or not to select it using a randomly generated number.
However, I want to perform a Monte Carlo simulation that uses a continuous (not discrete) probability distribution, but how I would use the random number generator to then select a value from the distribution based on its probability of occurance.
I am trying to automate a simulation with the crystal ball add-in. Specifically the simulation will go through a set of workbooks in a directory - run the simulation (monte carlo x1000) and extra the "trial values" data in another spreadsheet.
I have tried to find crystal ball documentation but I'm not having any luck.
I am newbie in Excel so i got limited knowledge of programming in excel. I need to perform a dice simulation. I will toss a die repeatedly until I got all six outcomes, then record the numbers of tosses.
I tried to use the function RANDBETWEEN() in to simulate the toss of the die but I don't know how to stop my simulation.
For example, I can generate a sequence like this: 1 3 5 3 4 3 2 1 2 3 6 5 6
but I need to stop at the 11th generation since I achieve all possible outcomes (1,2,3,4,5,6) there.
I have to use use the solver to calculate something (a mean-variance framework).
I am using the solver to minimize a cartain cell (variance) by making two cells equal through (expected return) by varying 10 cells( weights of assets), but I have to repeat this for 500+ times (for different expected returns).
Someone told me that I could best use some sort of loop through VBA. But I don't have a clue how that works.
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example: type value A 15 B 6 A 21 C 7 B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
I just have a quick question with regards to two-way interpolation of data.
I have a data table which has x-values, y-values and z-values.
I need to obtain an interpolate value for z, given a value for x and a value for y.
The x value specified is 1.569 and the y value specified is 1.66.
I know how to use the FORECAST formula for a simple x, y table, but I have no idea what to do here when I need to find a z value, given the x and y values.
I'm trying to find a way to enter the value of a cell from another worksheet into my currently active cell. The (obviously incorrect) code I'm working on looks something like this:
I want to use solver program. But when solver "changes cells" i want it to trigger my pivot tables in the workbook. So i added the code to my worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
So when a change occurs, all my pivot tables will get refreshed and my data will change. Is solver able to trigger this event while solving an optimization problem?
I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.
I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.
Where: Market position is either 1 or -1 Entry price is the price where the position is reversed.
For example: H3=1 and I3=100.56, then H4=-1 and I4=100.88, then H5=1 and I5=100.53
I need to calculate the profit as follows: I4 - I3 (because market position of 1 expects the price to go up) and then, I4 - I5 (because market position of -1 expects the price to go down)
I want to calculate this as a running total to the bottom of the column.
This is the formula I was working with, but it doesn't work:
Formula to get the Quarter number for the highest profit per Product, like in B16:F16.
The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums.
I know how to find the qtr. Number - like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.
I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.
In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.
When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.
I would like to be able to keep track of inventory by vendor as well as profit by vendor.
Using the Consolidation PT does not seem to work.
I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.
I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?
I am currently running XC running races and am wanting to know how i can have a quicker way of sorting out the best of sex, best of age group and best overall. The age groups are Under 18, under 30 and over 30. What can i do with v- lookup and other formulas to make it easier to record when they come in with out a long wait, i just want to be able to type in thier number and get all thier details so i can add the time straight in, then from there work out the winners instantly
how to obtain what the formatting of a cell is, be it a date, number, custom, currency, etc? I want to acquire the format of the cell in vBA code, in order to display the value exactly as it was formatted in the cell when sending that value to a label on a UserForm or Field on a Microsoft Word form.
Example: Cell A1 has the value 12/25/2006 formatted with the Date format: "mmmm-dd-yyyy" appearing as December 25, 2006
Cell A2 has the value 9.247 formatted with the Number format: "#.##" appearing as 9.25 to show only two-decimals
Cell A3 has the value 899, formatted with the Custom format: "00000" appearing as 00899 to represent a five-digit US zip code
Cell A4 has the value 1234.56 formatted with the Currency format: I think -"$#,##0.00_);($#,##0.00_)" appearing as $1,234.56
For rownumber = 1 To 4 Entry = Cells(rownumber,1).Value 'gets whatever in cell Formatting = Cells(rownumber,1).Format 'makes no sense in vBa. msgbox Entry & " formatted as " & Formatting Next
How can I obtain the array values from a function?
For example: Those steps were made by cytop to split a text (btw it works perfecet!!), I just added Function splitText(strSplit As Variant) As Variant ... End Function
VB: Function splitText(strSplit As Variant) As Variant Dim datosColumnaIncio() As Variant Dim iTemp As Integer Redim datosColumnaIncio(Len(strSplit) - 1) For iTemp = 1 To Len(strSplit) datosColumnaIncio(iTemp - 1) = Mid$(strSplit, iTemp, 1) Next End Function
So I want to know if its correct like that:
VB: Function splitText(strSplit As Variant) As Variant Dim datosColumnaIncio() As Variant Dim iTemp As Integer
[Code] .....
So when I call the function:
VB : MsgBox splitText("F4")
It throws an error message :s what is wrong or how can I get the array values of a function ???
What I want to do is in column 5 row 1, I want it to show a value only if all 4 columns, row 1 are filled in. For example in the picture I want to show a blank in the first 8 rows but to start showing a value on the rest of the rows, in this example IPE 240 to be the first value. When it shows this value from this row I want it to obtain the value with the highest percentege inside. For example in this picture the correct value first value to be shown in column 5 will be IPE (84%)
Ive done this code so far: =IF(AND(B256<>"",D256<>"",F256<>"",H256<>""),Here I want it to show the value on the row that has the highest percentege,"")
I have attached a chart I'm working on. Each item is assigned a part number and it automatically takes away from the inventory each time an item is sold. I was wondering if the bottom half could work with the top to determine profit.
Example:
Customer purchases Part # 3 (C15) Quantity 1 (D15) for $450 (E15). Is there any way to set it up so whatever part number they enter...it subtracts their sold price from the cost (C5 through C11) to show the profit in (F15).
I Produce something with a €2000 cost price. this is variable.
I Sell it for lets say € 2500,- to someone this is also a variable.
That's makes a Profit of € 500,- on the sale. This is 125% revenue
Until here, no problem using a spreadsheet. but now the difficult part.
I have to split of the 25% margin over 2 persons. This has to be done as follow:
a. The first slice is 2,25 for Mr A b. The second slice is 15,00 for MR B c. The Third Slice is 2,75 for mr A
This divides 20 of the 25 % over 2 persons. Still 5% margin remains
The last 5 is devided as follow: d. 85% for MR A e. 15% for MR B
So in this case: MR A receives: a:€45 + c:€55+ d:€85 = €185,- MR B receives: b:€250 + e:€15 = €265,-
The tricky part is when the margin is less than 20%. for example; If the margin is 14%. than only slice a. and a part of slice b. an be paid off. so in that case; MR A receives: a:€45 + c:€0+ d:€0 = €45,- MR B receives: b:€230 + e:€0 = €230,-
Any formula which calculates the revenue for MR A and B automatically based on only the cost and selling price.
A B 1 Cost price - €2000 2 Selling Price - €2500 3 MR A - €185,- 4 MR B -€265,-
At any rate, for a statistics experiment, I need a way to obtain precisely 6 random, numeric values in a cell, separated by commas, with no duplicates.
For example, if cell A1 contains the following: 7, 12, 14, 7, 14, 3, then I need to remove the duplicate(s) -- in this case, the extra 7 and the extra 14. After the two duplicates are removed, I then would have to replace with two more values (non-duplicates, of course).
Here are the restrictions, in addition to having no duplicate values appear:
* The first 5 values need to be randomly drawn from values between 1 and 75, inclusive.
* The 6th value needs to be randomly drawn from values between 1 and 15 (inclusive).
* It would be nice - but not necessary - to have the first 5 values sorted in ascending order. For example: 7, 12, 14, 29, 70, 3.
I'm using a spread sheet to determine what I should charge customers. I want to make at least $300 profit on every job. If my profit is not at least $300 then I must up my price until profit reaches $300. Currently I'm manually adding a percentage (Factor) to the original price until I reach $300 profit. In the image below:
A is the original price B is the factor by which I multiply the original price to increase it C is the new figure which is reached after multiplying (1+B2)*A2 D is the costs related to that job (I enter these manually on a separate sheet within the workbook) E if the profit (C-D)
I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.
I've assigned a single macro to numerous Form Checkboxes (not ActiveX). I'd like the macro to be able to identify the Checkbox calling it, so that if for example, if it were fired by clicking "Check Box 41", the macro should know that.
Is this possible, or must I use an ActiveX control?