# Import of Excel Data with empty fields

Posted 9 years ago
6261 Views
|
1 Reply
|
4 Total Likes
|
 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! Attachments:
1 Reply
Sort By:
Posted 9 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  Attachments:
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments