Monday, November 24, 2014

Is India ready for eGovernance?

Since opening of Indian economy to the world, and proliferation of Internet all over, we are seeing more and more services provided by the government is becoming web centric. It means a lot, at least to the technology savvy young public, as they are able to get information literally on their finger tips by accessing very useful information on their smart phones and laptops.

But, as a old time Information Technology (IT) professional, I notice that many a times, implementation by government departments leaves much to be desired. Most of government websites are designed and maintained by National Informatics Centre (NIC).

I'll give some example to illustrate, what I mean.

1. Scant regard for IT Security

I will be able to best illustrate this point by showing screen-shots of few prominent government websites.

a. Error shown below is for a page on NSDL site that take you to a page that allows you to make TDS payments under Form 280, 281 etc. What it means that this page does not have valid SSL certificate. Certificate installed on this page is for other two sites.

The landing page has a valid SSL certificate but in a round about manner using SAN value.

Possibly this page has become obsolete over a period of time but it is still there on the NSDL website and still gives error shown below.


This 'Mismatched Address' SSL error on Aadhaar/UIDAI site is really not expected. The error is due to improper usage of a wildcard SSL certificate.


b. Error shown below is seen while doing 'Upload' of TDS return after logging in to eTDS/TCS website with proper credentials. It basically means that Jar file (Java executable file/application) is not digitally signed. If it was digitally signed using Code Signer Certificates for Java, possibly this error would not have appeared.


How does a user - who would be a accountant or tax consultant would react to such an error message. He would simply learn to 'ignore' all such errors and get his work done, somehow. But this tendency of ignoring errors could land him in great trouble if he is redirected to a phising site - which would steal all his confidential data and possibly install Trojan or a malware that would make his PC a part of a botnet.

Also, using Java applications for websites is considered very dangerous because of numerous vulnerabilities discovered over so many years. Here is an informative article on - whether one should uninstall Java.

2. Poor Design and coding of Web Applications
One has to find out by trial and error as to how to make certain sites work.

a. Website doesn't accept Amount with decimal points: Check Challan Status link of NSDL site (see below) does not like if you enter amount with decimal points! See screen-shot below, where if we enter amount with decimal points it gives error saying 'Please enter valid amount'.

Is it easy for any one to guess that he is not supposed to type in decimal point and two zeros after that?


In fact, few years back there was a site which required decimal point and two zeros to be compulsorily added to a number to make it work. If decimal and zeros were missing then system would throw an error.

b. Many sites don't accept valid Email ids: I have noticed many instances where a website or Excel Form will not accept certain valid Email id. For example, Form 280 (TDS) payment website does not accept an Email id if it has dash "-" in the Email id. Same issue is with Excel Form by Maharashtra VAT Departments for VAT returns. For example if Email id is abc@pqr-india.com it will be rejected, though prq-india.com could be a real registered domain.

Some sites while giving an error - unnecessarily resets values of certain fields without any reason. Below given example of Form-281 for TDS payments is a good example of such poorly designed system.

c. Aadhaar side does not accept valid 12 digit Aadhaar numbers: This error on Aadhaar site is craziest of all.



d. File Attachments sent by CPC, Bangalore (Income tax) are without Date and Time Stamp: Whenever I get any Email with attachments from CPC, Bangalore the file attachments are not having any date and time stamp. So, one can never be sure when such files were created by them in first place. Also, another problem is that while backing up such undated files backup software does not like and throws warnings because it works on date & time stamp to find out which is the latest file.

It is left to anybody's guess as to while should an Income Tax Assessment order which is digitally signed by the assessment officer should be undated.

e. Maharashtra VAT department's website and systems do not allow change of registered Email id, no matter what you do: We had registered vsnl.com Email id with Sales Tax (now VAT) department many years back. But, over a period of time we switched to a mail id using our own company domain. We changed registered mail id at most places except with Maharashtra VAT department.

We gave feedback on website saying our Email id has changed but nothing happened. We wrote grievance form after logging in to their website - nothing happened. We called helpdesk they could not help. We wrote letter and hand-delivered to the assessment officer - nothing happened. Once I had a chance to meet VAT Commissioner and I told him about it. His reaction was as if I was taking up a silly issue! And he did not take note of it for correction. May be that RTI may work.

But, result is that we don't get any circulars/ notices / information mails from VAT department. Is 'Ignorance a bliss'? Perhaps 'yes' but ultimately No.

More on this later.

Tuesday, May 27, 2014

Extracting E-Mail addresses from a file or a web page using Microsoft Excel

Recently I needed to extract E-mail addresses from a range of cells in an Excel file. I looked around for a solution on Internet.

After going through number of solutions, I hit upon a very elegant solution that could be used easily for extracting Email addresses not only from Excel file but from any web page or a word file or anything that can be selected and copied and pasted in to a Excel sheet. The solution is described below.

It's essentially a short VBA (Visual Basic for Applications) code (also called a program) to create a custom function in Excel named FindEmailAddresses(Cell1:Cell2). Where range Cell1:Cell2 would contain the text from where we wish to extract Email addresses.

Assuming we have already created the function (explained later below) - here is a description of how to use it.
1. Assuming cells A1 to C5 contains the text from where we want to extract email ids
2. Insert a new column at A1 - so that original content of A1 to C5 will shift to B1 to D5.
3. Now, select Cells A1 to A4 - where the extracted Email ids would come. Here, we are selecting only 4 rows because we know that number of extracted Email ids will be less than 4. If they are more then select more number of rows.
4. While keeping the selection, in Cell A1 type the function =FindEmailAddresses(B1:D5), as shown below.
5. Now press Ctrl + Shift + Enter. Note that this step is crucial. By doing this, formula in A1 will get copied to all cells up to A4.



A
B
C
D
1
=FindEmailAddresses(B1:C5)
Hello world
Well just a empty cell
Nothing here
2
=FindEmailAddresses(B1:C5)
There is no email here
There is an email id:prakash@gmail.com
Here also nothing
3
=FindEmailAddresses(B1:C5)
Excel is very good tool
Practically this is useless

4
=FindEmailAddresses(B1:C5)
Her email is meena@yahoo.com but it changed
Nothing here
Well this Is good
5

Nothing

Theoretically this is empty

You will the extracted Email ids from B1:D5 in A1:A4 as shown below.
 
A
B
C
D
1
prakash@gmail.com
Hello world
Well just a empty cell
Nothing here
2
meena@yahoo.com
There is no email here
There is an email id:prakash@gmail.com
Here also nothing
3
#NA
Excel is very good tool
Practically this is useless

4
#NA
Her email is meena@yahoo.com but it changed
Nothing here
Well this Is good
5

Nothing

Theoretically this is empty

If all cells A1:A4 shows #VALUE! then save the file and upon opening the file you will see a warning saying 'Macros are disabled' Click on 'Enable Macro' and you will see the Emails ids in A1 to A4. If number of Email ids are less than 4 then you will see #NA in remaining cells. If they are more then select A1:A4 and press 'Del' key. Now, select more number of cells in step-3 above and continue. 
 
Note that if the range B1:D5 has only one Email id then all cells A1:A4 gets filled up with same Email id.

How do I extract Email addresses from my Gmail or Outlook application?
Well, this technique can be used only for extracting Email addresses from a single page. If you wish to extract Email addresses from your Email application - you will have to get programs that are developed specifically to do this work.

How to add user defined function FindEmailAddresses
Before you can use above function, you will have to add the user defined function using following procedure in Excel. It works with all versions of Excel from 2003 to 2013.

  1. Press Alt-F11 to open Visual Basic editor (while you are in the file containing the Email addresses)
  2. On top menu under Insert click on Module.
  3. Copy and paste the user defined function (given below) into new module window.
  4. Exit Visual Basic editor by clicking on 'x'
  5. Use the FindEmailAddresses function in Excel file, as explained above.

Visual Basic for Applications (VBA) Code:
Copy and Paste following code (program) in Visual Basic editor of Excel.
-----------------------------------------
Function FindEmailAddresses(rng As Range) As Variant()
Dim Temp As String, Cell As Range, EM() As Variant
ReDim EM(0)
For Each Cell In rng
Temp = Cell.Value
Do While InStr(Temp, "@")
   EM(UBound(EM)) = GetEmailAddress(Temp)
   Temp = Replace(Temp, "@", "", 1, 1)
   ReDim Preserve EM(UBound(EM) + 1)
   Loop
Next
ReDim Preserve EM(UBound(EM) - 1)
FindEmailAddresses = WorksheetFunction.Transpose(EM)
End Function

Function GetEmailAddress(ByVal S As String) As String
Dim x As Long, AtSign As Long
Dim Locale As String, DomainPart As String
Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
Domain = "[A-Za-z0-9._-]"
AtSign = InStr(S, "@")
For x = AtSign To 1 Step -1
    If Not Mid(" " & S, x, 1) Like Locale Then
       S = Mid(S, x)
    If Left(S, 1) = "." Then S = Mid(S, 2)
       Exit For
    End If
Next x
AtSign = InStr(S, "@")
For x = AtSign + 1 To Len(S) + 1
    If Not Mid(S & " ", x, 1) Like Domain Then
       S = Left(S, x - 1)
    If Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
       GetEmailAddress = S
       Exit For
    End If
Next x
End Function
----------------------------------------
Explanatory Notes
This notes are for those who wants to know more about technique used in this solution and further improvements.
1.  VBA code uses simple string comparison to find out presence of @ within the content of a cell. If it found one it found out word before and after it and extracted it as a Email address. This is done in GetEmailAddress function
2. It uses a clever Array Formula technique to store the extracted Email addresses one each in each cell. This is done by Ctrl+Shift+Enter key combination.
3. With some modifications this technique can be used to sort the extracted Email address domain wise (@yourcompany.com).

If you find it useful or have any suggestions - do write back.

Saturday, April 05, 2014

Would you ditch a PC running Windows XP because Microsoft is stopping support from April 8, 2014?

For last 6 months or so there is an intense debate and advisory from various sections of media, some of them acting on behalf of certain IT vendors also, that it would owe fully dangerous to continue to use Windows XP PC after the D-day.

Here is my insight in to the situation:

Windows XP has been in existence for last 12 years. This period is perhaps longer than any other Windows version so far. And also, it is one of the most admired and widely use PC operating system on earth. Even as on today, more than 30% of PCs run Windows XP.

So, should people scrap the PC or donate it to a charity or do something else?

There have been various answers from various people. Some of the most popular are listed below:

1.Upgrade it to Windows 8.1: Well, this may not be possible for most of the PCs because of old hardware or cost of upgrade. In fact, cost of upgrade is perhaps more than double of the current value of the PC.

2. Upgrade to Windows 7: If we decide to ignore the cost of Upgrade then this may be possible to do. But, key question is why should one put in money to keep running a dying hardware? It could be that application running on the PC is not upgraded for Windows 8 and it's mission critical to keep them running, as long as it is possible.

Well, this is easier said than done because there is no 'in-place' upgrade possible from XP to Windows 7. So, one has to use various tools provided by Microsoft to chart out the upgrade plan. This is a long and time consuming path; but there is hardly any option. There are some vendors who claim that they are able to make it easier if you agree to 'buy' their solution. Well, if you have enough money one can try it. But, in practice, no one has that much money.

Once done, one can run the mission critical application on Windows XP mode of Windows 7. So, this is doable but very costly in terms of money, time and effort. And no one is sure how long the PC will last. It may die sooner than you thought was possible; or a part can fail for which no replacement is available - coming back to 'almost dead' situation.

3. Run Windows XP on a Linux Virtual machine: Well this approach is not for the people with weak heart. Robolinux claims to provide one click install of Windows XP on top of Robolinux. This is possible to do provided you have a good hardware i.e., it should have enough memory and a processor capable to run a virtual machine.

So, this approach is ruled out for people who are running Windows XP on a old processor or with say 256 or 512 MB memory.

Assuming this is not a problem, but tell me what's benefit of doing this? Well, it is claimed that if Windows XP is running on top of a Linux OS then one would be saved from the antivirus and antimalware because it is running on top of a Linux - which is by default very secure. I doubt this claim unless one wants to run Windows XP in isolation i.e., it won't connect to Internet at all. If it is then one would require all security gears to protect itself from all kinds of attacks. It's like a baby dying within a iron cage.

Also, making this work requires major work in terms of first formatting the existing hard disk or getting a new one. First install Robolinux on it. Then donate the developers to get One Click virtual machine install for Windows XP. Then install Windows XP, install the required applications, update the XP with all patches and so on. Too much of a work for the promise of no viruses - a claim which itself seems doubtful if one is going to connect to Internet.

4. Switch to Linux: Well this topic requires lot of work on part of the user. In most cases small and medium business owner is not able to do this himself. It requires hiring of a Linux consultant, at least for couple of months to start with; and subsequently on a call basis for migration, user training and technical support. It's not easy or cheap to do this. Also, there is no Linux distribution worth its name who is as even close to Microsoft Windows as a Desktop Operating System. Of course, many are trying for a long time - and I've tried many of them and have given up for various reasons.

Notable among them are Ubuntu, Mint with Cinnamon and there are many of them out there. None of them come closer to Windows for following reasons:

a. Usability: Some basis short cuts that Windows user use on a regular basis do not work in Linux viz., Alt+Arrow keys to Select text, Ctrl+Alt+Del to reboot a hung PC (even Linux hangs, believe me) and so on. Also, Libre Office is a disappointment compared to Microsoft Office on a ongoing basis. I have tried using Libre Office and its earlier avatar a number of times but each time I've given up for some reason or other. It simply does not measure up on various counts e.g., speed of execution, office file compatibility, formatting, absence of key features etc. etc. etc.

b. Poor Design of Linux applications: Say one is using Lotus Notes Mail client in FireFox - it seems to work properly. But, moment you try to attach a file that is not on the local PC you are out of luck. It just can't attach a file that is located on Windows or Linux (Samba) server. File Manager does not remember Samba shares. It is not able to display directory structure in a way similar to Windows File Manager. Fonts and color schemes look poor - challenging not only aesthetic sense but also wasting precious space on the screen. Wine is able to run Windows applications but if that Window application wants to access files on Windows Server or Linux (Samba) server - you are out of luck.

Of course, if I don't mention this I will be doing injustice to Linux community. Linux has evolved by leaps and bounds in last 5 to 7 years. So many features have been added - making it almost as good as Windows, feature wise or hardware support wise. But, it simply lacks finesse or ease of doing work. There is an immediate drop of productivity (even after a good Linux training). 

One notable Linux distribution from India which seems to offer alternative to Windows XP users is named Tiguin - Tiger and Penguin together in one body. Well, I must say that it's a good effort, particularly coming from a small town of Gujarat. I gave it a decent try and it is still installed on my Windows XP machine as a dual boot. Installation is almost flow-less - I would give more than 90% mark for installation. It's a distribution targeted to small and medium businesses of India. It offers all frequently used application under Windows on a Linux  For those application which are available only on Windows like Tally (accounting software) and Microsoft Excel (required for Tally to do export reports in Excel format) it offer them either out of the box or easy installation without end user to learn complex Linux commands. In summary it makes Linux as close to Windows XP as is possible. It works on subscription model for updates and technical support. I tried taking their help for few technical problems I faced but it was average i.e., nothing great.



In few areas in spite of its best foot forward it is not able to come closer to Windows because of inherent limitations of Linux mentioned above. Also, one shortcoming I noticed is that out of the box it uses old version of FireFox (22 against current 27), Libre Office and so on. Upon inquiry Enjay (developer of Tiguin) said they have chose 'stable' versions over 'current' versions. Well, I don't agree with this.

5. Switch to a Windows XP clone: This is a little known fact but indeed there is an open source project that has been trying to write an almost exact clone of Windows XP. It's called React Operating System. Many people are working on it. It's specs are quite close to Windows XP. Few alpha versions are available for trial but sadly it is advancing very slowly for want of funds, I suppose. I've downloaded version 0.3.1.6. Its live CD did not work on few PCs I tried. Version 0.4 (community edition) is likely come out very soon. How soon? No body knows. But, there is some hope that after say one year something credible may come out. Keep hoping.

Is there any way out for Windows XP users?
Yes, and it is very simple. Keep using Windows XP as long as hardware keeps working.

Why am I saying this?

First, Windows XP has hardened itself in last twelve years. It's very stable, usable and very secure. And there is no reason (at least today) to ditch it in favour of something else in a hurry.

My guess is that we can make it work at least till end of PC's life. By no stretch of imagination this period would go beyond 3 years - on average of course.

Microsoft will keep supplying updates to its own antivirus Microsoft Security Essentials till July 2015. Avast has announced that they will provide support to Windows XP at least for three years. See official text below from Avast.
--------
Are you aware that AVAST will be supporting Windows XP for at least the next three years, by creating protection modules and detections specifically designed to cover Windows XP vulnerabilities and other security problems? (We will be doing this!)
--------

So, why worry or hurry now? There is no need to open wallet and make payment to buy new PC or purchase Windows 7 or 8.1 and of course, new Office 2013 and so on.

What do you say?