4
$\begingroup$

I'm trying to use the FinancialDerivative function in Excel and can't seem to get the formula to work, even without using cell references for the inputs.

In a Mathematica notebook I enter the following formula:

FinancialDerivative[
   {"AsianArithmetic", "European", "Call"},
   {"StrikePrice" -> 40, "Expiration" -> 1, "Inception" -> -1,"AverageSoFar" -> 50},
   {"InterestRate" -> 0.01,"Volatility" -> 0.2, "CurrentPrice" -> 50,"Dividend" -> 0.05}
]

This function evaluates normally. If I use either the EVAL function or the function builder within Excel I get an error that says FinancialDerivative called with 11 arguments, between 1 and 5 arguments are expected.

I am using Mathematica Link for Excel 3.6 and Mathematica 11. I have a number of these options to value and gathered inputs in cells. I would like to create a formula to reference the cells and calculate the values accordingly.

I have tried

=EVAL("FinancialDerivative","AsianArithmetic","European","Pu‌​t",C5,D4,D3,C7,C8,C9‌​,C6,C10) 

and also using explicit values as

=EVAL("FinancialDerivative","AsianArithmetic,European,Put","40,1,-1,50",".01,.2,50,.05")

each time with no success. I've also tried using the function builder.

The function builder provides input boxes for function (the first three items in the curly brackets in the original formula, parameters (second set) and ambient parameters (third set). I still get same error message. I've also tried this with simple vanilla options, but even then you have 8 inputs and still get the message that no more than 5 are expected.

What is the correct syntax to get this to work in Excel?

$\endgroup$
  • 2
    $\begingroup$ Could you give more details on how you are calling the MMA function from within Excel? I.e. are you using Mathematica Link for Excel, how are you constructing the formula in Excel, etc.? $\endgroup$ – MarcoB Jan 25 '17 at 16:54
  • $\begingroup$ Using Mathematica Link for Excel 3.6 and Mathematica 11. I have a number of these options to value and gathered inputs in cells. I would like to create a formula to reference the cells and calculate value. I have tried =EVAL("FinancialDerivative","AsianArithmetic","European","Put",C5,D4,D3,C7,C8,C9,C6,C10) with no success and also tried using the function builder. Function builder provides input boxes for function (the first three items in the curly brackets in the original formula, parameters (second set) and ambient parameters (third set). Still get same error message. $\endgroup$ – Stephen Fowler Jan 25 '17 at 18:16
  • $\begingroup$ Stephen, unfortunately I don't own Excel Link so I am not familiar with it, but somebody in the forum might be. I would suggest that you add the details you mentioned in the comment to your original post (you can edit it at any time using the "edit" link at the bottom left of the post). $\endgroup$ – MarcoB Jan 25 '17 at 18:34
  • $\begingroup$ I tried using =EVAL("FinancialDerivative",DATA(H2:H4),H5:H8,H9:H12) with no success. The output shows up as FinancialDerivative[Excel["H2:H4"],{40,1,-1,50},{0.01,0.2,50,0.05}] So it is clearly referencing the data in the cells, but won't evaluate the formula. Inputting as {H5:H8},{H9:H12} makes it so that it can't reference the cells and inputting as =EVAL("FinancialDerivative",DATA(H2:H4),DATA(H5:H8),DATA(H9:H12)) doesn't work either, presumably because the arguments and adjacent arguments are data rather than text cells so the DATA field isn't applicable. $\endgroup$ – IronMarshal Jan 27 '17 at 16:50
2
$\begingroup$

Solution - you have to enter the arguments and counterarguments using the EXPR tag in the same way you used DATA for the instrument type.

=EVAL("FinancialDerivative",DATA(D3:D5),EXPR(D15),EXPR(D16))

The two EXPR portions of the formula can be built up in Excel using the Concatenate function to build the proper input syntax required by the FinancialDerivative function (e.g. {"StrikePrice" -> 40, "Expiration" -> 1, "Inception" -> -1, "AverageSoFar" -> 50}).

$\endgroup$
4
$\begingroup$

This works for me when I attmept to enter a FinancialDerivative call into Excel using ExcelLink:

=EVAL("FinancialDerivative","{""AsianArithmetic"", ""European"", ""Call""}","{""StrikePrice"" -> 40, ""Expiration"" -> 1, ""Inception"" -> -1, ""AverageSoFar"" -> 50}","{""InterestRate"" -> 0.01, ""Volatility"" -> 0.2, ""CurrentPrice"" -> 50, ""Dividend"" -> 0.05}")

In short, you only have 3 arguments here. The 3 lists. Each list needs to by properly formatted as a single argument, so the error message you got was valid.

Hope this helps.

PS, ExcelLink has a "Input Builder" tool (not sure if this it the proper name) which allows you to input a function and its individual arguments. When I'm not sure how to properly input something this typically works. Using the "Preview" button I can usually troubleshoot where things are going wrong with my input.

enter image description here

$\endgroup$
  • $\begingroup$ Thanks for the help. Now how do I reference cell inputs? I've used DATA(H2:H4) to reference the AsianArithmetic, European, Call instrument type (and have no problems evaluating the function), but I can't seem to find the correct syntax for referencing the other data in cells H5:H8 and H9:12 respectively. $\endgroup$ – Stephen Fowler Jan 26 '17 at 18:39
  • $\begingroup$ Spent more time trying to solve this problem of referencing cell inputs in Excel. As mentioned, using the DATA(H2:H4) to reference the Instrument Type settings works fine. However, even if I take the first set of arguments in the formula above "{""StrikePrice"" -> 40, ""Expiration"" -> 1, ""Inception"" -> -1, ""AverageSoFar"" -> 50}" and put those contents into another cell and then try to reference that cell directly (or even by using DATA(cell id)) I can't get get the function to work. The formula looks correct in the function builder, but won't evaluate correctly. $\endgroup$ – Stephen Fowler Jan 30 '17 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.