Import of Excel Data with empty fields

Questions
, ,
1

Hello everybody,

i have a big excel file with several sheets. In each sheet are option prices with dates and Strike prices for different expiration dates.
For some dates or Strike prices there are no corresponding option prices and as a result there are some empty fields.
This leads to errors, when I try to calculate implied volatilities.
Is there any way where Mathematica could ignore these empty fields?

Thanks in advance!

2

You can detect and replace the missing values:

In[1]:= SetDirectory[NotebookDirectory[]]

Out[1]= "C:\\Users\\David\\Documents\\temp"

In[2]:= raw = Import["missings.xlsx"][[1]]

Out[2]= {{"a", 11., 12., 13.}, {"b", 21., "", 23.}, {"c", "", 32., 
  33.}, {"d", 41., 42., 43.}}

In[3]:= data = Replace[raw, "" -> 0, {2}]

Out[3]= {{"a", 11., 12., 13.}, {"b", 21., 0, 23.}, {"c", 0, 32., 
  33.}, {"d", 41., 42., 43.}}

But that does not necessarily ignore them. What that means depends on what you are trying to do. For example, you might want the mean of all the elements in a column after nonnumerical values are deleted, so they do not affect the mean.

In[4]:= (* take the mean after removing all nonnumerics *)
mean[list_] := Mean[Select[list, NumberQ]]

In[5]:= (* mean of column 3 *)
mean[raw[[All, 3]]]

Out[5]= 28.6667