Table Of Contents
- 7. Notes on spreadsheets etc
Here notes on spreadsheets are kept. Spreadsheets is an interesting framework for doing scientific/financial calculations.
Contents
Abstract:
In spreadsheets like [KSpread], [GNumeric] or the Open Office [Calc] dates are handled the following.
- You type in a date in a date-specific format
- The spreadsheet saves the date in the cell as an integer, but keep showing the date like the format
This way a date is defined as an integer being the number of days since 1899-12-31 (date = 0). This is inspired by the way it is done in the overall spreadsheet benchmark [msExcel].
The benefit is that it is easy to a number of days to a date or finding the number of days between 2 dates as a difference.
This chapter is about how this way of adding functionality to a new class is generalized in Python. And showing that spreadsheets might benefit from this sort of generalisations.
Contents
The picture shows how dates are represented in spreadsheets in order to simplify calculations.
The formulas in column A is shown in column B. This way both values and formulas are shown.
Note how the format defines appearance while the fact that a date is actually is an integer makes it possible to do calculations on dates similar to operator overload which is to be presented below.
The change of format on the other hand is in fact a case of method overload which also is to be presented below.
The functionality of dates in spreadsheets is mirrored in the class bankdate in the finance package.
This very intuitive way of working with dates and period we would like to transfer to Python.
To examplify I will introduce some concepts from the finance module.
You can do timeperiod calculations within the same time unit:
>>> from finance import TimePeriod
>>> 5 * TimePeriod('4m') - 3
17m
You can a bankdate with one or more timeperiods and get a new bankdate. And you can subtract 2 bankdates and get the number of days inbetween. Note that no value at instantion means that bankdate becomes current date.
>>> from finance import BankDate
>>> BankDate('2010-09-14') # No value set means today
2010-09-14
>>> BankDate('2010-09-14') + '3m'
2010-12-14
>>> BankDate('2010-09-14') + '3m' + '2y'
2012-12-14
>>> BankDate('2010-09-14') + '3m' - BankDate('2010-09-14')
91
Inheritance and method overload is one of the cornerstones object oriented programming. Below it is shown used to ease coding with these mathematically inspired objects.
In python it is easy to set up eg bankdate and timeperiod calculations. In every class definition it is possible to define internal methods like __add__ and __radd__ for left and right addition. The methods are used when an instance of this class is added something in the code.
In Python there are also some standard methods used for presentation:
A lot of times these 2 methods are set to the same. But there is no reason not to shown the values of the properties in eg __repr__ and some modified version in the __str__./reference/datamodel.html
For further reading see eg Python
As it has been demonstrated spreadsheets does have the need for defining new datatypes and new definitions for one or more operator.
It would be nice if the set of standard class functions were available in the scripting within spreadsheets.
This way it would fairly easy to implement mathematical objects like complex numbers, vectors and matrices.
Here the operator definitions are clear. But it could be nice if it was possible to show different information at different times, eg to show a matrix by its values some times and sometimes to show the dimensions of the matrix.
Another line of objects could be objects to build binomial trees, Monte Carlo paths etc.
This is a bit like a formula in spreadsheets. It shows a value most of the time. But in a certain mode the formula is shown instead.
A further development in Python could be generic operator overload. Why is the developer limited to specified number of operators. In order to implement new operators it would be nice to specify a operator in the code. The need for new operators could be inspired from mathematical concepts like eg differentiation, summation and integration.
If code objects could more be similar to mathematical objects it create a integration between programming and applied mathematics which yet has to be seen.
It would affect both education and the use of applied mathematics when the writing language are almost the same in theory and in practise.
Abstract:
Despite most homepages it is actually quite easy to enable Gnumeric for Python in Ubuntu.
Next part is a short resume on how to set up scripting in Python for Gnumeric. All homepages known to me at this point of writing on this subject are erronous at some degree. This should a proper guide.
Finally after considering installation in Ubuntu and scripting setup this text runs though the Gnumeric API.
Apparently most linux users haven’t discovered that packages like ubuntu actually is a graphical user interface.
To test whether you now have Python functions by typing
=py_capwords(“python rocks”)
in a cell.
After you hit <Enter>, you should see “Python Rocks” in that cell.
Also, if You can find the function py_capwords in the “Python” category then everything seems to be correct installed.
Since there are few but essential standards there is a need for a script doing the standardisation.
The plugin.xml template based on the py-func folder:
<?xml version="1.0" encoding="UTF-8"?>
<plugin id="Gnumeric_PyFunc">
<information>
<name>ThisIsNotShownAnywere</name>
<description>ThisIsNotShownAnywere</description>
<require_explicit_enabling/>
</information>
<loader type="Gnumeric_PythonLoader:python">
<attribute value="MyPythonFunctionModule" name="module_name"/>
</loader>
<services>
<service type="function_group"
id="StartOfKeyDictionaryInPythonModule">
<category>CategoryNameInFunctionList</category>
<functions>
<function name="GnumericFunctionInModule"/>
</functions>
</service>
</services>
</plugin>
The MyPythonFunctionModule.py template based on the py-func folder:
# MyPythonFunctionModule.py
#::
import Gnumeric as g # Isn't used below right now ::
def PythonFunctionInModule(args):
'@FUNCTION=GnumericFunctionInModule\n'\
'@SYNTAX=GnumericFunctionInModule(args)\n'\
'@DESCRIPTION=Description.\n'\
'Look, the description can go onto other lines.\n\n'\
'@EXAMPLES='\
'@SEEALSO='
some python code
# Translate the func_add python function to a gnumeric
# function and register it
**StartOfKeyDictionaryInPythonModule**_functions = {
'GnumericFunctionInModule': PythonFunctionInModule
}
Regretably the function description isn’t transferred to gnumeric.
It is possible to specify the type of arguments by writing:
'GnumericFunctionInModule': ('type1type2', 'arg1Name, arg2Name',
PythonFunctionInModule)
where type is:
First of all: To do this you have to be running from within the Gnumeric Python console.
And then in order to get going Gnumeric has to be imported:
>>> import Gnumeric as g
If Gnumeric is running besides the console then there is an open workbook. To see existing workbooks use the Gnumeric function workbooks:
>>> wbs = g.workbooks()
>>> wbs
(<Workbook object at 0xb639f2f0>,)
>>> wb = wbs[0]
Otherwise create a new workbook by:
>>> wbNew = g.workbook_new()
>>> g.workbooks()
(<Workbook object at 0xb639f310>, <Workbook object at 0xb639f320>)
It is quite limited what can be done workbooks in Gnumeric:
>>> dir(wb)
['gui_add', 'sheet_add', 'sheets']
When a new workbook is created from the Gnumeric Python console it isn’t shown. To see wbNew inside Gnumeric one has to write:
>>> wbNew.gui_add()
As for workbooks you can add a sheet and see the list of sheets. Too see the the sheets in workbook wbNew:
>>> wbNew.sheets()
(<Sheet object at 0xb639f310>,)
So a workbook created from the code has 1 sheet from start whereas the one opened with Gnumeric has 3:
>>> len(wb.sheets())
3
A sheet can be added to eg wbNew by:
>>> sheet = wbNew.sheet_add()
>>> len(wbNew.sheets())
2
So what can be done with a sheet:
>>> dir(sheet)
['cell_fetch', 'get_extent', 'get_name_unquoted', 'rename', 'style_apply_range', 'style_get', 'style_set_pos', 'style_set_range']
At sheet level it is possible to get sheet name and rename a sheet:
>>> sheet.get_name_unquoted()
'Ark2'
>>> sheet.rename('nhb')
>>> sheet.get_name_unquoted()
'nhb'
Also you can refer to a specific cell by:
>>> c = sheet.cell_fetch(5,3)
>>> c
<Cell object at 0xb639f320>
And set the value to the string ‘hi’ (We will look at what can be done with cells just below the handling of sheets):
>>> c.set_text('hi')
Now we will be able to see the text ‘hi’ in cell F4, so rows and columns are numbered starting from 0 and positions are given as (column, row).
Now to get the minimal range on the sheet with cells in use:
>>> sheet.get_extent()
<Range object at 0xb636ec38>
And to see the range as a tuple:
>>> sheet.get_extent().get_tuple()
(5, 3, 5, 3)
Let’s see the get_extent in use one more time:
>>> sheet.cell_fetch(1,2).set_text('B3')
>>> sheet.get_extent().get_tuple()
(1, 2, 5, 3)
So the minimal range has upper left corner at ‘B3’ or position (1, 2) with value ‘B3’ and lower right corner at F4 or position (5, 3) with value ‘hi’.
A range can also be specified with the function Range:
>>> g.Range(1,2,5,3)
<Range object at 0xb636ec38>
>>> g.Range(1,2,5,3).get_tuple()
(1, 2, 5, 3)
Hints
To get a list of sheet names:
>>> [s.get_name_unquoted() for s in wb.sheets()]
['Ark1', 'Ark2', 'Ark3']
To get a sheet with name Ark1:
>>> s1 = [s for s in wb.sheets() if s.get_name_unquoted() == 'Ark1'][0]
>>> s1.get_name_unquoted()
'Ark1'
To see the methods for the cell object type:
>>> F1 = sheet.cell_fetch(5,0)
>>> F1
<Cell object at 0xb639f320>
>>> dir(F1)
['get_entered_text', 'get_rendered_text', 'get_style', 'get_value', 'get_value_as_string', 'set_text']
So let’s take a look at these methods. All that can be set is string/text.
>>> F2 = sheet.cell_fetch(5,1)
>>> F1.set_text('1')
>>> F2.set_text('3.2')
Now this is a trap. On my computer the decimal seperator is comma (,) and not the dot (.) so to write a float type into a cell I have to use comma:
>>> F2.set_text('3,2')
>>> F3 = sheet.cell_fetch(5,2)
>>> F3.set_text('=F1 + F2')
Now the workbook looks like:
Now let’s look at the methods.
Return entered text - Formulas are preserved:
>>> F1.get_entered_text()
'1'
>>> F2.get_entered_text()
'3,2'
>>> F3.get_entered_text()
'=F1+F2'
Return rendered text - Formulas are shown with their value as string:
>>> F1.get_rendered_text()
'1'
>>> F2.get_rendered_text()
'3,2'
>>> F3.get_rendered_text()
'4,2'
Return value - Formulas are shown as floats:
>>> F1.get_value()
1.0
>>> F2.get_value()
3.2000000000000002
>>> F3.get_value()
4.2000000000000002
Return value as string - Formulas are shown with their value as string:
>>> F1.get_value_as_string()
'1'
>>> F2.get_value_as_string()
'3,2'
>>> F3.get_value_as_string()
'4,2'
Another way of referring a cell object is to use CellPos:
>>> sheet.cell_fetch(1,1)
<Cell object at 0xb6735330>
>>> g.CellPos(1,1)
<CellPos object at 0xb6735340>
But CellPos only haves the method get_tuple.
Syntactic sugar
You can’t see the position in then usual spreadsheet notation using the str function on CellPos:
>>> cp = g.CellPos(1,2)
>>> str(cp)
'B3'
Instead of sheet.cell_fetch(column, row) You can write sheet[column, row]:
>>> c = sheet[1,2]
>>> dir(c)
['get_entered_text', 'get_rendered_text', 'get_style', 'get_value', 'get_value_as_string', 'set_text']
But You can’t see the position in then usual spreadsheet notation using the str function on a cell object:
>>> str(c)
'<Cell object at 0xb6481300>'
Gnumeric.functions is the dictionary of all the Gnumeric functions shown in the function browser. You can ONLY do lookups!! Ie you can retrieve a function but beforehand You must know what You want.
Some help can be found at Gnumeric function libary
How to set styles for a range
First define workbook, sheet, range and style (container):
>>> import Gnumeric as g
>>> wb=g.workbooks()[0]
>>> sheet=wb.sheets()[0]
>>> r = g.Range(0,0,1,1)
>>> style = g.GnmStyle()
Change text size from 10 to 14 in style:
>>> style.get_font_size()
10.0
>>> style.set_font_size(14)
>>> style.get_font_size()
14.0
Apply the new style to the range on the sheet:
>>> sheet.style_apply_range(r,style)
Shift to bold font in style:
>>> style.get_font_bold()
0
>>> style.set_font_bold(1)
>>> style.get_font_bold()
1
Apply the new style to the range on the sheet:
>>> sheet.style_apply_range(r,style)
style_get must refer to a CellPos:
>>> sheet.style_get(g.CellPos(0,0)).get_font_size()
14.0
What happens here?:
>>> sheet.style_set_pos(g.CellPos(2,2), style)
>>> sheet.style_set_range(r,style)
I don’t know. Arguments are ok! What is the difference to style_apply_range?