Free Microsoft Excel 2013 Quick Reference

Volatility Surfaces In Excel

Has anybody ever built volatility surfaces in excel for stocks,futures,options etc. I was told that you could use an add-in feature, but that was it. If anybody has any good ideas I would be very grateful for the information.


Post your answer or comment

comments powered by Disqus
can someone plz help me with this:

i have the following variables: Date, Rate, LnRate. Rates are provided for each day, all the way from October, 2001 till today. LnRate is = ln (today's rate / yesterday's rate). What I want to do now is I want to take teh standard deviation of all the LN values for each month. So, for example, Oct 2001 should have 30 different values under LnRate. I want to take the standard deviation of these values to calculate October 2001's volatility. Then, I want to do teh same for November 2001, Dec,.. etc. any ideas how i can go about this in excel? thx.

hi, i need to calculate surface areas in excel chart ( excel 2002) under 4
different lines and i have no idea how to do it so if anyone can help me with

How could one plot the volatility of a series of numbers in Excel?


I finally got things figured out (with some help here) regarding the formula and function code to number the sheets in my workbook as 1 of 3, 2 of 3, 3 of 3 etc. Now..... when I move the file over to Excel 2010 I error out. The formulas work fine in Excel 2003 XP. The Error Note indicates "Invalid Name Error" and when I to to help on error or Error Checking Options, I still do not know what I need to do. Do I need to give the cells on the first worksheet a Name?

If someone could explain what Excel is asking me to do, define - I'm ready to be educated! Regards - Lenny2

On each worksheet there are two cells: for example (A1 is [=SheetPosition()] and A3 is [=GetSheetCount()]

The code in 'This Workbook' is:

    Application.Volatile True 
    SheetPosition = Application.Caller.Parent.Index 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

    If Worksheets_only = True Then 
        SheetCount = Worksheets.Count 
        SheetCount = Sheets.Count 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Improving Performance in Excel 2007

Volatile functions and conditional formats. Excel continues calculating cells that depend on ... If you cannot use two cells, use COUNTIF . It is generally faster than an exact match ...

Improving Performance in Excel 2007

... because it does not involve volatile functions like OFFSET coupled with counting functions ... All user-defined functions in Excel 2007, regardless of the language and add-in method ...

Hi guys,

Is there anyone who has some code to run Teradata Queries in Excel?

I've got some code that runs single queries but as soon as I have to run a query with volatile tables it doesn't work.


We are running an application which merges several Excel workbooks into one.
This workbook then gets printed. The problem is how-ever, that our printer is
set to print banner pages for each user. When printing this specific
workbook, we get banner pages where-ever the merge occured.
We have a similar workbook(also gets merged), but on this one the above
problem does not surface. Could there be a workbook specific setting
somewhere in excel which forces banner sheets to print?

I wanted to make a 3-dimensional plot in Excel and discovered that I cannot
do this. All you can do is make 3-D charts over two dimensions, you cannot
make 3-D plots over three dimensions. There's the surface plot, but that
only plots x and y along with the series. I want to plot true x, y, and z.

John W. Mordosky

Hi All,

I have a strange issue.

The application coded in excel 2000 is not performing nicely in excel

I have gone through the folowing link:

The steps like switching to calculation manual and then screen
updating= false is taken care in my code.

The functions like offset() and other volatile functions which are
used is same for excel 2000 and excel 2003, then I fail to understand
why it is taking more time.

Even some of the links asked to install the drivers. I have
application which I have coded in 2003 which are performing better but
they are not taking care of much of reporting.

I have gone throughn the following links:

Please can some one judge the solution that what might be the problem
in calculations of the same functions.

I do understand it is a big application but what might or what
settings might have changed that the same functions are taking double

Please reply out of the box as I am in need of all my friends advice
and expertise.

Thanking all of you,



I'm wanting to plot a 3D XYZ scatter graph (surface) but couldn't out a way to do it in excel.

Do you know how I can go about it? If it's not possible in excel is there some free software that I can download that will do the job?

To make the porblem clear, we measured the height of water at different points in a river ( height difference due to surface waves or ripples) and we know the exact position of the points with respect to an origin. We were looking at plotting a 3D graph to simulate the river surface.

It will be great if anyone could help!



I have a workbook that I developed in Excel 2002 that uses does a lot of statistical computation and numerical recursion.

Whenever I try and open the file in Excel 2003, the file seems to go into an endless loop and just freezes the application. I've seen this on three different machines.

Is there some setting in Excel 2003? I don't think any of my functions are volatile, so am not sure why the spreadsheet goes crazy.

I can either post the code up here or email the spreadsheet to any volunteers!

Thanks in advance.

I would like to plot 3D (X,Y,Z) points in excel, similar to and XY Scatter
but with a third axis. The surface graph type does not do what I want because
it can't plot scaled numerical values on 2 of 3 axis. Does anyone know how I
can do this?

Thank you

I have some embedded PDF files in Excel file. I want to save them in a separate folder as seperate files, by pressing a button in toolbar.
How to do that using VBA?



I am trying to figure out a way to work with the date functions in Excel 2007. I have noticed when you enter a wrong date in a cell such as April 34, 2012 excel considers that cell as an error. I am trying to figure out a formula (not sure if an IF Statement will do it) to refer to the date cell and tell me the Day portion of the cell must be adjusted.
The same issue with Month. If I enter a month that is greater than 12 for example 17th month, 10th day and 2012 year, the formula will display adjust month. I know I could use the IFERROR function but I have 2 cells to help me with the date errors, and if I use IFERRORS in both cells, both cells display Adjust Day and Adjust Month not clearly identifying which part of the date enterd is in error.

Thank you.

Hi, I'm playing this game and would like to calculate which buildings are worth my time

Following things are from a game and I want to calculate what's the best things to use

Buildings make cash for me

Building 1 makes 80 coins every 2 min
Building 2 makes 120 coins every 5 min
Building 3 makes 160 coins every 8 min
Building 4 makes 200 coins every 12 min

Question is, how can I make excel calculate it on an hour and 24 hourly base?

How would I put these calculations into Excel. I figured it out with other stuff, but they were hourly bases
Building 1 makes 600 coins every 4 hours, that's 3600 in 24 hours. In Excel that would be =(D3/B3)*24
D3 being the 600 B3 being the 4 hours

I hope this is allowed to be asked on this forum, as it's not the normal kind of Excel question I suppose.

Thanks for any help!

Hi all,

Apparently one 'save as picture' option should be available in shortcut menus when I rightclick any image or picture in excel 2007, but I just couldn't find it.

I was trying to save a smartart as jpeg and I came across this article I tried following it, but no luck so far, that option is just not there.

Any idea on why my excel 2007 is missing that option?


Is it possible that if i press tab in excel my active cell offsets by a row and two negative columns.

Any Help will be appreciated.

Hello everyone,

Looking at Mr. Ron's VBA script examples (, I modified one accordingly for one of my office work.
I am a recruiter for a company and maintain a database of all candidates in excel. Every time a candidate gets accepted or rejected, I have to send him an e-mail of acceptance or rejection. To avoid the repetitive task of manually logging in and typing the text, I thought of making my life easy by working on excel macros.

I would be very thankful if someone may have a look at the spreadsheet I created. I would like you to modify the code a bit if you can spare some time for me in the following manner.

1)I created a list box in column "I" for status. If you select Accept, it sends an acceptance e-mail which is shown in column J along with a time-stamp in column K.
Same thing happens if you select Reject in Status (I column).

The first option in the drop down menu is a BLANK value, incase I do not want to send any emails to particular candidates.

Now what I would is if I add more rows for candidates and want to send an email to only those, how can that be done?
In the current code, it is resending all candidates again.

Is it possible to add a dialogue box which would ask the user :- Would you like to resend "acceptance" or "rejection" email [depending on what you selected in column J] to [persons name] from column A?"

If selected "yes" then resend the email, if selected "no" then do not do anything and proceed to the next row. It would be great to have an additional button of "NO TO ALL" along with "Yes: or "No".

Suppose I had earlier sent emails to 30 candidates and added only a few candidates at the end of the sheet. Now if I only want to send email to those new ones, if we run the code, I do not want to click 30 times on "No" button when I get a pop up which says " Would you like to resend " acceptance" or rejection email to "XYZ" candidate"?

Rather there should be a button of "No to all" by which it sends email only to the newly added candidates and doesnt ask me to resend email to the above already sent candidates.

I would be very thankful if you can help me.

Best regards,


Hello Sir,

Kindly help me in excel

I have one doubt that is

I put the text in the columns of A B C D and now I would like to lock the C Column if something enter in B Column and it will open Automatically if nothing in B Column
how can it will work,

2. now I am maintain the stock book in excel and I want the reports individual brand in next sheet

Ex A - OB, B - Receive, C - Total, D - Sales, E - Balance and in F column I mention the Brand of the Item

now I want the stock report in each sheet with the brand items

3. I Share my excel Work Book, it's working but it's open solely in other system why it's happen and how can I manage multiuser password for each system to the same excel Work Book with different access

Kindly help me about the above problems

Hence I oblige

Thanking You

How to use this code in excel 2000?


[COLOR=#333333][FONT=Lucida Grande]Dim arquivo As String[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]Set fd = Application.FileDialog(msoFileDialogFolderPicker)[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]If fd.Show = -1 Then[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]arquivo = fd.SelectedItems(1)[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]MsgBox "A pasta selecionada é " & arquivo[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]Else: arquivo = ""[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]End[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]End If[/FONT][/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a worksheet in Excel 2010 and I want to set a reminder to look at a particular row of information on a date in the future, can be by message/pop-up box or just the row changing colour. I do not have any dates set within the core information, and want to avoid putting in an additional column for a date if possible.

Can I use Conditional Formatting to say something like - when today is x change the format of the cell (or row) to...?

I don't think I can put an actual date into the formula line, or can I?

I've been doing some sql in excel; so far so good. Sometimes I will mistakenly run a query that takes a long time; requiring a restart of excel to be reasonable. I'd like to abort queries that are out of control. Here is my code, with the things I've tried that didn't work commented out. Can queries be stopped or limited beforehand? Also posted:

 'ISeriesDataCN.CommandTimeout = 1 'did nothing: I assume these are seconds
 'ISeriesDataCN.ConnectionTimeout = 1 'did nothing
ISeriesDataCN.Open "Provider=IBMDA400;Data Source=censored; Force Translate=0", "", "" 
Set ISeriesDataCM = New ADODB.Command 
ISeriesDataCM.ActiveConnection = ISeriesDataCN 
 'build sql statement
sqlsel = "Select *" 
 'sqlsel = "Select top 100 *" 'token 100 not valid
sqlfrm = " From PRDDTA73.F4101" 
sqlwhr = " Where IMITM

Hello, I’ve lately been struggling to speed-up a process of compressing data in cells by utilizing the Range.SpecialCells method. Everything works fine in Excel 2003 and 2007 with execution time of 15 to 20 seconds. Very recently I upgraded to Excel 2010 (32 bit) and over there the execution takes 4 minutes and in some cases even much more. I have a couple of questions in that regard:

Would an upgrade to Excel 2010 (64 bit) dramatically speed up the process at least to the speed I experienced with Excel 2003 / 2007? From my understanding, an upgrade should be considered when dealing with 100,000’s of rows. The range my code covers is only B20:MC3020.Is there a parallel method to compress the data in Excel 2010 that I'm unaware of? I tried applying the data compression through an array which I found in MrExcel forum. The speed remains the same in any of the Excel versions.
This is the code I’m trying to use:

    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    With Sheets("5 Day") 
        .Range("B20:MC3020").SpecialCells(xlCellTypeConstants, 2).ClearContents 
        .Range("B20:MC3020").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft 
         'Compressing the data
    End With 
    Application.Calculation = xlCalculationAutomatic 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
SpecialCells(xlCellTypeConstants, 2).ClearContents is needed because the cells in the range are results of formulas which are being pasted special (values only) from another sheet. The result can either be 1 or "" (double quote signs). There’s no problem with the execution of that line however.

This is the line that causes the HUGE delay in 2010:

.Range("B20:MC3020").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All I want to achieve is to delete blank cells in between the 1’s in each column and compress the 1’s to the left as a consequence. I attached a file that contains the code and some sample data ready for a test run. I would really appreciate if somebody can test how fast it runs in Excel 2010 (64 Bit) and shed light on a possible solution to the low speed performance if an upgrade to 64 Bit won't help.

No luck finding an answer? You could always try Google.