I keep wanting to file the income tax return online. However, I insist on using OpenOffice and I have been failing. A request on the income tax office's site for a OpenOffice version did not get any response. A discussion on the ilug-delhi list pointed to a version of ITR1 utility at http://freedom-matters.in/. This Excel utility was for the previous year. Making changes from VBA to OpenOffice's basic to the Excel sheets is time consuming. It is also hard to keep up with the updated releases and various versions of the Excel utilities for various forms – ITR1 through ITR4 at present.

VBA was originally included in the http://go-oo.org/ version but was not complete. The VBA interoperability project is now a joint Novel and Sun(Oracle) project and active - http://wiki.services.openoffice.org/wiki/VBA. Hence, it is worth exploring the state of this project as far as the utilities for various ITR forms are concerned. Our objective is to experiment and see whether we could use the Excel utilities next year and the skill level that may be needed.

Hence, I started with the development snapshot from http://download.openoffice.org/next/ on Fedora. Arch Linux includes the beta and the development versions in its repositories, making it very easy to work with all three versions (stable, beta and development) of OpenOffice concurrently. VBA module is included in the current releases of OpenOffice; however, the security options for macro's have to be 'Medium'. In addition, in the Load/Save options for VBA, “Executable code” has to be enabled for Excel sheets.

The development version I used was ooo300_m86 (build 9518). There is considerable progress. Validation is triggered by a call to Worksheet_Change. OpenOffice introduces an additional macro Worksheet_Change_OnChange_Proxy in the code which calls the Worksheet_Change macro. Currently, the implementation was incomplete so the validation code failed. However, most of the validations are now carried out by hidden functions in the spreadsheet and they get executed. Hence, to progress, I commented the calls to the Worksheet_Change macro in the proxy. So, while the validation was incomplete, the worksheet could be filled.

Validations for two fields, PIN code and telephone number, still failed. The solution was to set the type for these two cells as text.

Execution of various macros still failed. The issue was that the cell names in sheet1 (which was actually the second sheet) had an additional suffix '_2', that is sheet1.PAN_2 while the macro was trying to use sheet1.PAN. I filed a bug report but meanwhile a small Python macro helped change the names to what was expected. (It turned out I needed to add additional names for the same cells as some hidden formulae were affected. More details are at http://sethanil.blogspot.com/2010/08/python-macro.html .) However, the need for this macro was short lived.

I checked the beta version OOO330m5 (build 9521). On this version, the name conversion was proper. Hence, the rest of the experimentation was done on this version. The Worksheet_Change function is not called in this release but as discussed above, its implementation was still incomplete in the development version.

There are a number of hidden sheets. In order to debug the code and get a better idea of what is happening, it was useful to make all the hidden sheets visible. It is quite simple as the following Python macro illustrates:

Once we enter the data and compute the tax, the result is incorrect. Since the Calculator sheet is now visible, we notice that the problem is that the tax is not shown properly. The problem is that the Calculator sheet is protected but with no password. It is possible that the translation of the function from Excel to OO is in error. The code expects the password to be the value in cell “BE1” on Sheet6 (Home sheet) appended with “(21)*”. The contents of the cell is a null string. Hence, the password expected was “(21)*”. Manually, changing the blank password to the expected password resulted in the taxes being computed correctly.

One problem I encountered was that Not var = "" was interpreted as (Not var) = "" instead of Not (var = "") as was expected. It is difficult to argue which is the better or more appropriate interpretation. In this context, compatibility with Excel is the issue. An ideal solution could be that the compatibility module inserts parentheses as needed when loading VBA code and removes ambiguity. Finally, after I added parentheses as needed, there was success and a proper xml file was generated.

Making the utility for ITR1 form run on OpenOffice and get an XML output was a challenge but feasible even for a person who knew no basic much less VBA!

VBA compatibility in OpenOffice is improving. In all likelihood, by the time the we need to file the tax return next year, we will be able to use OpenOffice with minimal additional effort and generate the XML file for online filing of the return. So, next year, I expect that I will save Government of India data entry costs.

September 2010