Message Boards Message Boards

1 Reply
4 Total Likes
View groups...
Share this post:

Import of Excel Data with empty fields

Posted 10 years ago

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!

Posted 10 years ago

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
POSTED BY: David Keith
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract