Quantitative Analysis.
Trading Platform.
Python for Excel.
Author.

I.Motivation and design (PFE).
II.Installation of the Python for Excel.
III.Tutorial introduction into the Python for Excel.
IV.PFE Programmer's reference.

Figure Define a two-dimensional array...

Figure ...and import it into Excel via pfeEval in-cell function.

FigureAll events may be trapped. There is autocompletion.

FigureUnicode data may be marshalled...

Figure...and manipulated.

FigureThere are calltips.

FigureEvery object has hooks for low level access.

Python For Excel: Python-based alternative to VBA.

ython for Excel (PFE) brings robustness to Python scripting for Excel and provides a complete set of tools for building fully featured applications with Excel front end. It is an in-process control of Excel with an out-of-process developement and debugging environment.


Python for Excel mimics VBA in the manner of control of Excel application, simplicity of programming style, syntax and naming of object model. Functionality of an Excel workbook is controled by a Python script placed in the same directory. In the event of exception, a control window pops up with an error message and a shell prompt. The user may choose to do post-mortem debugging of the exception. The control window may also be requested during normal execution by pressing a hot key combination. The control window runs several concurrent Python shells. It does not block during script execution, preserves namespace when closed and provides convenient access for examination, experimentation and debugging.


All Excel events may be trapped. There is in-cell execution of Python statements and two-way interfacing with VBA. The PFE's object model is extensive. Every object has a hook for IDispatch-based manipulation from Python shell and unrestricted C++ manipulation from extension library.


Python for Excel scripts are executed in-process with Excel. The PFE's object model is based on in-process COM calls to Excel API. The programmer may assume that third party applications and simultaneous user actions would not interfere with script execution. The PFE script may release control of Excel and regain such control later under set conditions.


Despite the fact that the PFE is based on COM technology it is a solution to the "DLL hell" problem for all Excel based programming. The PFE installs once. The functionality is controlled by Python scripts.


Python for Excel runs the tradditional Python, versions 2.5, 2.6 and 2.7 and supports Offices 2000,2003,2007,2010. There is, however, one limitation regarding third party's C++ extensions. For full compatibility use Python 2.5 with Excels 2000 and 2003 and use Pythons 2.5,2.6,2.7 with Excels 2007 and 2010. This limitation is only applicable to third party's C++ extensions. Pure Python libraries and the Python for Excel itself function for versions 2.5,2.6,2.7 of Python and Offices 2000,2003,2007,2010 in all combinations. The limitation exists because Microsoft introduced breaking changes into C++ runtime in 2005. One cannot safely load a C++ DLL dependent on later C++ runtime into an earlier version of Excel using the generic procedure inside of Python's "import" directive. Such problem manifests itself as a DLL load failure.


This is a "Beta" version of the product.


The Python for Excel is provided "as is", without warranty of any kind, expressed or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In no event shall the author be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the Python for Excel or the use or other dealings in the Python for Excel.


If the program does not install or you suspect that you have found a bug then send an e-mail to "kaslanidi@yahoo.com". The author will assist free of charge. The author provides commercial technical support for all other issues. To address possible concerns about interruption of service the author is prepared to enter into a long term contract with a clause that would force public release of the source code under the MIT license in the event of service interruption.


Python for Excel is compiled using MS Visual C++ 2003 Toolkit and Express edition of Visual Studio 2008. The book "Developer's Workshop to COM and ATL 3.0" by Andrew Troelsen was the principal source of information on COM technology. The book "Excel 2002 VBA" by Stephen Bullen, John Green, Rob Bovey and Robert Rosenberg was the source on VBA for Excel. The book "wxPython in action" by Noel Rappin and Robin Dunn was the source on wx library. The GUI is based on Scintilla editor at http://www.scintilla.org and wx library at http://www.wxpython.org. The source code of PyCrust by Patrick K. O'Brien was used as the source of research on Scintilla editor. Fragments of PyCrust code were copied without changes. The installation program is written using the Nullsoft Scriptable Install System at http://nsis.sourceforge.net/Main_Page. The e-mail activated distribution program is based on Python, SMTP and MySQL.



History of changes.


09/15/2010. Initial version posted.

01/15/2011. Support for Pythons 2.6 and 2.7 was implemented. Installation utility registers otsPythonForExcel.xla(m) for Offices 2000,2003,2007,2010 and adds PythonForExcel directory to trusted locations for Offices 2007 and 2010.

03/22/2011. Previously cumbersome notation for in-cell evaluation of Python statements with string constant has been corrected. Type =pfeEval(" 'exampleString' ") to get exampleString in-cell. Type =pfeEval(" 'a\""b' ") to get a"b in-cell.








Table of Contents.


I.Motivation and design (PFE).
II.Installation of the Python for Excel.
1.How to confirm that the PFE did properly install?
2.What to do if the PythonForExcel ControlShell does not activate?
3.The otsPythonForExcel.xla is registered among the Excel add-ins but the ControlShell still does not activate.
4.The Excel macros are operational but the ControlShell still does not activate.
III.Tutorial introduction into the Python for Excel.
1.PythonForExcel ControlShell.
2.What to do if the connection to Excel from the ControlShell is lost or if the prompt "waits" for too long.
3.Possible littering of memory with orphaned Excel processes.
4.In-cell execution of Python statements.
5.Calling Python from VBA.
6.Calling VBA from Python.
7.Debugging with Python for Excel.
8.Unicode support.
9.Deactivation of Python for Excel.
10.Very important note about pfe-script execution.
IV.PFE Programmer's reference.
1.Direct access to Excel API.
A.Application object (PFE).
a.ActiveCell property (vba.Application).
b.ActiveSheet property (vba.Application).
c.ActiveWindow property (vba.Application).
d.ActiveWorkbook property (vba.Application).
e.Calculate method (vba.Application).
f.CalculateBeforeSave property (vba.Application).
g.Calculation property (vba.Application).
h.Dispatch property (vba.Application).
i.EnableEvents property (vba.Application).
j.Evaluate method (vba.Application).
k.ExecuteExcel4Macro method (vba.Application).
l.Intersect method (vba.Application).
m.MemoryUsed property (vba.Application).
n.Name property (vba.Application).
o.Path property (vba.Application).
p.PathSeparator property (vba.Application).
q.Quit method (vba.Application).
r.Run method (vba.Application).
s.ScreenUpdating property (vba.Application).
t.Selection property (vba.Application).
u.StatusBar property (vba.Application).
v.Union method (vba.Application).
w.Windows property (vba.Application).
x.Workbooks property (vba.Application).
B.Workbooks object (PFE).
a.__call__ method (vba.Workbooks).
b.Add method (vba.Workbooks).
c.Close method (vba.Workbooks).
d.Count property (vba.Workbooks).
e.Dispatch property (vba.Workbooks).
f.Item method (vba.Workbooks).
g.Open method (vba.Workbooks).
h.Parent property (vba.Workbooks).
C.Workbook object (PFE).
a.Activate method (vba.Workbook).
b.ActiveSheet property (vba.Workbook).
c.Close method (vba.Workbook).
d.Dispatch property (vba.Workbook).
e.Name property (vba.Workbook).
f.Names property (vba.Workbook).
g.Parent property (vba.Workbook).
h.Path property (vba.Workbook).
i.Save method (vba.Workbook).
j.SaveAs method (vba.Workbook).
k.Worksheets property (vba.Workbook).
D.Worksheets object (PFE).
a.__call__method (vba.Worksheets).
b.AddAfter method (vba.Worksheets).
c.AddBefore method (vba.Worksheets).
d.CopyAfter method (vba.Worksheets).
e.CopyBefore method (vba.Worksheets).
f.Count property (vba.Worksheets).
g.Dispatch property (vba.Worksheets).
h.Item method (vba.Worksheets).
i.Parent property (vba.Worksheets).
E.Worksheet object (PFE).
a.Activate method (vba.Worksheet).
b.Calculate method (vba.Worksheet).
c.Cells property (vba.Worksheet).
d.Columns property (vba.Worksheet).
e.CopyAfter method (vba.Worksheet).
f.CopyBefore (vba.Worksheet).
g.Delete method (vba.Worksheet).
h.Dispatch property (vba.Worksheet).
i.Hyperlinks property (vba.Worksheet).
j.Index method (vba.Worksheet).
k.MoveAfter method (vba.Worksheet).
l.MoveBefore method (vba.Worksheet).
m.Name property (vba.Worksheet).
n.Names property (vba.Worksheet).
o.Parent property (vba.Worksheet).
p.Range method (vba.Worksheet).
q.Rows property (vba.Worksheet).
r.Visible property (vba.Worksheet).
F.Range object (PFE).
a.__call__ method (vba.Range).
b.Activate method (vba.Range).
c.Address property (vba.Range).
d.Borders property (vba.Range).
e.Calculate method (vba.Range).
f.Cells property (vba.Range).
g.Clear method (vba.Range).
h.Column property (vba.Range).
i.ColumnWidth property (vba.Range).
j.Columns property (vba.Range).
k.Count property (vba.Range).
l.CurrentRegion property (vba.Range).
m.Delete method (vba.Range).
n.Dispatch property (vba.Range).
o.EntireColumn property (vba.Range).
p.EntireRow property (vba.Range).
q.FillDown method (vba.Range).
r.FillLeft method (vba.Range).
s.FillRight method (vba.Range).
t.FillUp method (vba.Range).
u.Font property (vba.Range).
v.Formula property (vba.Range).
w.Height property (vba.Range).
x.Hidden property (vba.Range).
y.Hyperlinks property (vba.Range).
z.Interior property (vba.Range).
{.Item method (vba.Range).
|.Name property (vba.Range).
}.NumberFormat property (vba.Range).
~.Offset method (vba.Range).
.Parent property (vba.Range).
.Resize method (vba.Range).
.Row property (vba.Range).
.RowHeight property (vba.Range).
.Rows property (vba.Range).
.Select method (vba.Range).
.Show method (vba.Range).
.Value property (vba.Range).
.Width property (vba.Range).
G.Borders object (PFE).
a.__call__ method (vba.Borders).
b.ColorIndex property (vba.Borders).
c.Count property (vba.Borders).
d.Dispatch property (vba.Borders).
e.Item method (vba.Borders).
f.LineStyle property (vba.Borders).
g.Parent property (vba.Borders).
h.Value property (vba.Borders).
i.Weight property (vba.Borders).
H.Border object (PFE).
a.ColorIndex property (vba.Border).
b.Dispatch property (vba.Border).
c.LineStyle property (vba.Border).
d.Parent property (vba.Border).
e.Weight property (vba.Border).
I.Interior object (PFE).
a.ColorIndex property (vba.Interior).
b.Dispatch property (vba.Interior).
c.Parent property (vba.Interior).
d.Pattern property (vba.Interior).
J.Names object (PFE).
a.__call__ method (vba.Names).
b.Add method (vba.Names).
c.Count property (vba.Names).
d.Dispatch property (vba.Names).
e.Item method (vba.Names).
f.Parent property (vba.Names).
K.Name object (PFE).
a.Delete method (vba.Name).
b.Dispatch property (vba.Name).
c.Index property (vba.Name).
d.Name property (vba.Name).
e.NameLocal property (vba.Name).
f.Parent property (vba.Name).
g.RefersTo property (vba.Name).
h.RefersToRange property (vba.Name).
L.Font object (PFE).
a.Bold property (vba.Font).
b.ColorIndex property (vba.Font).
c.Dispatch property (vba.Font).
d.FontStyle property (vba.Font).
e.Italic property (vba.Font).
f.Name property (vba.Font).
g.Parent property (vba.Font).
h.Size property (vba.Font).
i.Strikethrough property (vba.Font).
j.Subscript property (vba.Font).
k.Superscript property (vba.Font).
l.Underline property (vba.Font).
2.Lower level access to Excel API.
A.Dispatch object (PFE).
B.PyCObject object (PFE).
3.Event trapping (PFE).
A.OnNewWorkbook property (ExcelListener).
B.OnSheetActivate property (ExcelListener).
C.OnSheetBeforeDoubleClick property (ExcelListener).
D.OnSheetBeforeRightClick property (ExcelListener).
E.OnSheetCalculate property (ExcelListener).
F.OnSheetChange property (ExcelListener).
G.OnSheetDeactivate property (ExcelListener).
H.OnSheetFollowHyperlink property (ExcelListener).
I.OnSheetSelectionChange property (ExcelListener).
J.OnWindowActivate property (ExcelListener).
K.OnWindowDeactivate property (ExcelListener).
L.OnWindowResize property (ExcelListener).
M.OnWorkbookActivate property (ExcelListener).
N.OnWorkbookBeforeClose property (ExcelListener).
O.OnWorkbookBeforePrint property (ExcelListener).
P.OnWorkbookBeforeSave property (ExcelListener).
Q.OnWorkbookDeactivate property (ExcelListener).
R.OnWorkbookNewSheet property (ExcelListener).
S.OnWorkbookOpen property (ExcelListener).
4.Configuration files (PFE).
A.Controlling individual workbook.
B.ThisWorkbook (PFE).
C.Initialization of add-in namespace.
D.Implementation of ExcelListener.
E.Script execution in response to activation of the ControlShell.
F.Finalization of the add-in.

Figure

Figure

Figure

Figure

Figure

Figure


















Copyright 2010.