function A = FastGetGrid(HandleOrFilename)
% FASTGETGRID extracts values and formuals form all worksheets in a
% Microsoft Office Spreadsheet Object (OWC). It also provides the
% numerical and the string variables seperately
%
% USAGE:
%
% >> h = actxcontrol('OWC11.Spreadsheet.11',[0 0 550 420]);
% >> FastFillGrid(h, ...
% {'Maturity' ,'4/15/09','' ; ...
% 'coupon' ,8.63 ,'ANN' ; ...
% 'redemption' ,100 ,'' ; ...
% 'Yield' ,10.1660 ,'' ; ...
% '' ,'' ,'' ; ...
% 'dirty price' ,97.2088 ,'' ; ...
% 'accrued interest',6.2088 ,'' ; ...
% 'clean price' ,91.0000 ,'' });
% >> A = FastGetGrid(h)
% A =
% Sheet1: [1x1 struct]
% Sheet2: [1x1 struct]
% Sheet3: [1x1 struct]
%
% >> % LOOK AT SHEET 1
% >> A.Sheet1
% ans =
% Formula: {8x3 cell}
% Value: {8x3 cell}
% String: {8x3 cell}
% Number: {8x3 cell}
%
% >> % LOOK AT THE FORMULAS IN SHEET 1
% >> A.Sheet1.Formula
% ans =
% 'Maturity' '39918' [NaN]
% 'coupon' '8.63' 'ANN'
% 'redemption' '100' [NaN]
% 'Yield' '10.166' [NaN]
% [ NaN] [ NaN] [NaN]
% 'dirty price' '97.2088' [NaN]
% 'accrued interest' '6.2088' [NaN]
% 'clean price' '91' [NaN]
%
% >> % LOOK AT ALL OF THE VALUES IN SHEET 1
% >> A.Sheet1.Value
% ans =
% 'Maturity' '4/15/2009' [NaN]
% 'coupon' [ 8.6300] 'ANN'
% 'redemption' [ 100] [NaN]
% 'Yield' [ 10.1660] [NaN]
% [ NaN] [ NaN] [NaN]
% 'dirty price' [ 97.2088] [NaN]
% 'accrued interest' [ 6.2088] [NaN]
% 'clean price' [ 91] [NaN]
%
% >> % LOOK AT ONLY THE STRINGS IN SHEET 1
% >> A.Sheet1.String
% ans =
% 'Maturity' '4/15/2009' []
% 'coupon' [] 'ANN'
% 'redemption' [] []
% 'Yield' [] []
% [] [] []
% 'dirty price' [] []
% 'accrued interest' [] []
% 'clean price' [] []
%
% >> % LOOK AT ONLY THE NUMBERS IN SHEET 1
% >> A.Sheet1.Number
% ans =
% [] [] [NaN]
% [] [ 8.6300] []
% [] [ 100] [NaN]
% [] [10.1660] [NaN]
% [NaN] [ NaN] [NaN]
% [] [97.2088] [NaN]
% [] [ 6.2088] [NaN]
% [] [ 91] [NaN]
%
% KEY WORDS: Excel spreadsheet workbook sheet formula extract i/o input
% file grid
%
% SEE ALSO:
% http://www.mathworks.com/matlabcentral/fileexchange/loadAuthor.do?objectId=828141&objectType=author
% Good MATLAB Programming Practices, graph_and_table, TechAnalTool,
% MagnetGInput, Spreadsheet, df, fts, dlmreadall, robustreadcsv,
% plottt, txt, db, mode, matlab, BreakAxis, Tick, Bar, plot, axisatorigin,
% RegExTools, pcode_helper, SplitTxtFile, PlotDataDays, graph_and_table_,
% ShadePlotForEmphasis, perl_regex_rep, DatabaseCell, Mat, SelectBox,
% evaldialog, fixfilename, ExpandRegexSet, FillGrid, load, var,
% SearchReplaceManyGUI, ToSavedWorkspace, AnonymousCompile,
% SearchAndReplaceMany, StringIsNumber, DepSubFun, CheapHLOCPlot,
% AUTOHELP, FastFillGrid, OKDLGWITHLISTBOX, QUESTDLGWITHGRID,
% Spreadsheet, Structure, BullInAChinaShop, repfloat, GetSubFunNames,
% REORDERPDF, SelectDatesAndSecs, WideOKDlgWithListBox,
% StrrepLikeRegexprep, AddRecursivePath, SelectDateParameters,
% ParseVarArgIn, AssignVarargin, CatOrSplitFile, SplitFunctions,
% FixFileName, BuildKeyWhereClause, CellOrNoCellRef, AnalyseExcelFormulas
% It's not fancy, but it works.
% Michael Robbins, CFA
% Michael Robbins, CFA
% michaelNOrobbinsSPAMusenet@yahoo.com
if ishandle(HandleOrFilename)
h.activex1 = HandleOrFilename;
else
errordlg('Input must be a handle to a spreadsheet',mfilename);
end;
A=[];
NumWorksheets = h.activex1.Worksheets.Count;
if NumWorksheets<1
errordlg('No worksheets',mfilename);
else
for iWorksheets = 1 : NumWorksheets
try
% GET VALUES AND FORMULAS FROM THE EXCEL SHEET
TargetSheet = get(h.activex1.sheets,'item',iWorksheets);
t=regexp(TargetSheet.Name,'\w','match');
F{iWorksheets} = TargetSheet.UsedRange.Formula;
V{iWorksheets} = TargetSheet.UsedRange.Value;
S{iWorksheets} = cell(size(V{iWorksheets}));
N{iWorksheets} = cell(size(V{iWorksheets}));
if ~iscell(F{iWorksheets})
F{iWorksheets}={F{iWorksheets}};
V{iWorksheets}={V{iWorksheets}};
end;
[L,W]=size(F{iWorksheets});
for i=1:L
for j=1:W
e=V{iWorksheets}{i,j};
if isstr(e)
S{iWorksheets}{i,j}=e;
elseif isnumeric(e)
N{iWorksheets}{i,j}=e;
end;
end;
end;
% POPULATE A STRUCTURE
s(1).type='.';
s(1).subs=[t{:}];
s(2).type='.';
s(2).subs='Formula';
A=subsasgn(A,s,F{iWorksheets});
s(2).subs='Value';
A=subsasgn(A,s,V{iWorksheets});
s(2).subs='String';
A=subsasgn(A,s,S{iWorksheets});
s(2).subs='Number';
A=subsasgn(A,s,N{iWorksheets});
end;
end;
%close(h.activex1);
end;
function B = AnalyseExcelFormulas_(r,c,F,V);
B=[];
v = V{r,c};
f = F{r,c};
good_v = ~isempty(v) && any(~isnan(v));
good_f = ~isempty(f) && any(~isnan(f)) && f(1)=='=';
if good_v | good_f
B{1}=nn2an(r+2,c+1);
B{2}=v;
if good_f
B{3}=['''' f];
end;
end;
function cr = nn2an(r,c)
% Thanks Brett Shoelson
t = [floor((c - 1)/26) + 64 rem(c - 1, 26) + 65];
if (t(1)<65), t(1) = []; end
cr = [char(t) num2str(r)];
