Workbook Support

#NAME? Error

The formulas in the pricing / volatility sheets are driven by VBA Modules. For modules to work your Excel needs to have Macros enabled.

If you load the spreadsheet and #NAME appears as the output in the cells it means that Macros are not enabled. See instructions below on how to enable Macros for your version of Excel.

Excel 97 - 2000

  • Go to Tools/Macro/Security
  • Change the setting to Medium
  • Close and reopen the workbook
  • It will ask you if you want to enable Macros, click Yes

Excel 2003

  • Go to Tools/Options
  • Click on the Security tab
  • Go to Macro Security
  • Change the setting to Medium
  • Close and reopen the workbook
  • It will ask you if you want to enable Macros, click Yes

Excel 2007

First, make sure that you can see the Developer tab.

If there is no Developer tab, click on the Office icon to the top left of the application and choose Excel Options, which is located at the bottom right of the popup.

Excel Options

Now click on Popular at the top left. Check the box titled "Show developer tab in the ribbon".

Show Developer Tab in Ribbon

Now, click on the Developer tab.

Click on Macro Security.

Select "Disable all macros with notification" and press ok.

Macro Security

Close and reopen spreadsheet.

Now you should see a Security Warning appear in the toolbar that reads "Macros have been disabled". Click on the options button.

Select "Enable this content" and press ok.

Enable this content

If you have any more trouble feel free to contact me.

MostarNovember 28th, 2014 at 3:09am

Hello Peter

I believe I solve the problem. Modify all functions by adding
If Time<=0 Then
EXIT FUNCTION
End If

There is other issue regarding libreoffice and openoffice.
It is not possible to run a user defined function, which is stored in a library except the Standard libraries. So I fixed it also.

If you want I can email you working spreadsheet in libreoffice/openoffice format so that you can add it to your site.

You welcome to email me (email removed)

Mostar

MosterNovember 28th, 2014 at 12:45am

Hello Peter,

Regarding my previous post. I found the cause. If "Expiry Date" past meaning
"DTE in Years" negative then it hang.

For example

Function dOne(UnderlyingPrice, ExercisePrice, Time, Interest, Volatility, Dividend)

dOne = (Log(UnderlyingPrice / ExercisePrice) + (Interest - Dividend + 0.5 * Volatility ^ 2) * Time) / (Volatility * (Sqr(Time)))

Give the error.

MostarNovember 28th, 2014 at 12:20am

Hello,

> When you receive the error message, does the program jump into the code editor?

Its hang, if I click OK on the error message it pop-up immediately again. The only way out is to kill the process :-(

> It sounds like one of the functions doesn't exist in libreoffice.

I don't think so because it's not happen immediately, every thing work for few minutes and then it happen.

I try on Linux Libreoffice V4.2, V4.4 and on Windows XP old Openoffice 3.2 all with the same problem.

I have several sheet type base on your original one, and it happen in all of them.

Thanks

PeterNovember 16th, 2014 at 10:34pm

Hi Mostar,

Mmm, no, I've not used libreoffice - I built the spreadsheet to support Excel only. It sounds like one of the functions doesn't exist in libreoffice. When you receive the error message, does the program jump into the code editor? Just wondering if it takes you to the function by way of highlight to show you where the problem is?

MostarNovember 13th, 2014 at 11:56pm

Hi,

Recently I migrate from excel to libreoffice calc spreadsheet. From time to time the spreadsheet hang on the following error:
"BASIC runtime error. '5' invalid procedure call"
The only way out is stopping the process.

I try to look at the VB functions but have no clue whith can cause this error and how to fix it.

Do you have any idea?

Thanks

PeterApril 30th, 2014 at 5:55pm

Yes, in Excel 2013 there is a new function called GAMMA, which conflicts with the function I created. I've sent you via email a modified version, which contains the change and will update one on the download page the same way.

I changed the function to read OptionGamma instead of Gamma.

PeterApril 28th, 2014 at 7:03am

Hi Michael,

Thanks for brining this to my attention - let me check this out tomorrow and come to you here. I might need to just modify the function name and release a new spreadsheet.

MichaelApril 28th, 2014 at 2:55am

In Excel 2013, the function GAMMA and your Gamma function conflict. How do I make Excel know that I want to use your Gamma function not the built-in GAMMA function?

thanks,

AbhishekOctober 11th, 2013 at 8:29am

Its inspiring the effort you have made to put together all the content in you site to be freely available to the public. Very educational and helpful.
Thank you.

Ty KhimNovember 29th, 2012 at 10:11pm

Great help, thanks so much

LearnerOctober 2nd, 2012 at 4:44am

Thanks a lot for your hard work.

Rupesh ShuklaSeptember 12th, 2012 at 1:52am

for LOG function in Excel use:

Log(value,base value)
by default base value=2.7182818

use LOG((underlyingprice / exercise price)2.7182818
)

for LOG function in VBA

no need to use base value:

PeterOctober 20th, 2011 at 5:18am

Hi Gopal, don't worry about the calculated premium if you're using a stock leg. Just enter "stock" or "s" and use the stock price as the strike price.

GopalOctober 19th, 2011 at 4:00pm

How do I add stock to one of the legs (e.g. delta /gamma neutral strategy) ? I tried putting an "s" but the calculated premium number seems strange ?
thanks
gopal

WaheedSeptember 10th, 2011 at 4:13am

fantstic--Thank you

PeterJuly 27th, 2011 at 5:59am

That's right - so if you're using Excel formulas you will need to specify the natural logarithm LN i.e. LN(underlyingprice / exercise price)

vishnuJuly 27th, 2011 at 4:34am

The log in Functions result different than if log taken in excell cells, e.g
in VBA
log(underlyingprice / exercise price=8.5369958
while in excel cells
LOG(underlyingprice / exercise price)=-0.040617851

Lucio MirandaMarch 2nd, 2011 at 5:12am

Thank you for the free options study software.

Add a Comment


  8
     Subscribe for Comment Updates