How to keep track of changes in multiple columns in a MS Excel spreadsheet

In a question to my blog article “How to keep track of changes in a MS Excel spreadsheet” I have been asked whether the code I showed there could be changed to monitor multiple columns in a spreadsheet.

Of course it can. The new code I am showing below handles an arbitrary number of columns, with the following assumptions / pre-requisites:

  • Column label in row 1
  • Row identifier (id) in column 1 of each row

I have changed my example from the previous article and added an additional column to my list of products: ‘Vendor’, here is an example:

Let’s change Price of Product C to $ 1301,00 and Vendor to ‘Company B’. Here is how the new Change History looks like:

Column B now shows which columns was changed and column C the new value, as usually together with a time stamp. Column A identifies for what product ( my ‘id’ column in this example ) the change occurred.

And here is the new code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim AuditRecord As Range
 ' This is our change history ...
 Set AuditRecord = Worksheets("ChangeHistory").Range("A1:B65000")
 r = 0
 ' Now find the end of the Change History to start appending to ...
 Do
    r = r + 1
 Loop Until IsEmpty(AuditRecord.Cells(r, 1))
 ' For each cell modified ...
 For Each c In Target
   Row = c.Row
   Col = c.Column
   Value = c.Value
   ' ... update Change History with value and time stamp of modification
   AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(1, 1) & " " & Worksheets("Products").Cells(Row, 1)
   AuditRecord.Cells(r, 2) = Worksheets("Products").Cells(1, Col)
   AuditRecord.Cells(r, 3) = Value
   AuditRecord.Cells(r, 4).NumberFormat = "dd mm yyyy hh:mm:ss"
   AuditRecord.Cells(r, 4).Value = Now
   r = r + 1
Next End Sub

jQuery – the best Javascript library ever !

jQuery is my favorite javascript library and luckily it is the most popular at all on the market according to these "Usage of JavaScript libraries for websites".
What I especially like in jQuery is its powerful and consistent way to select DOM elements. Combined with a professional way to use CSS styles and classes it becomes quiet easy to come up with a dynamic full functional web page with a consistent look-and-feel and behaviour.
I have created a jsFiddle here to demonstrate what I am stating here. In this example I have two divs with three paragraphs in each, and thru some buttons I demonstrate how to highlight particular divs or paragraphs and how to easily reset everything to default settings by simply adding or removing a particular class "Highlight" I have created for this. This class could be more fancy than just changing background color, it could come with larger or different fonts, nice borders or whatever you can think of CSS-wise.
jQuery selector like "div#div2" ( choose the div with id "div2" ) or "div p:first-child" ( to select the first paragraph in all divs ) make it really easy to deal with particular DOM elements on the web page.
HTML code:

Code:

<div id="div1">
    <p id="p1">Item 1</p>
    <p id="p2">Item 2</p>
    <p id="p3">Item 3</p>
</div>
<div id="div2">
    <p id="p1">Item 1</p>
    <p id="p2">Item 2</p>
    <p id="p3">Item 3</p>
</div>
<div id="buttons">
    <button type="button" id="b1">Highlight div 1</button>
    <button type="button" id="b2">Highlight div 2</button>   
    <button type="button" id="b3">Highlight all first items</button>   
    <button type="button" id="b4">Highlight 2nd item in 2nd div</button>   
    <button type="button" id="b">Factory settings</button>
</div>

CSS Code:

Code:

div {
    background: lightcyan;
}
.highlight {
    background: yellow;
}

Javascript ( and jQuery ) code:

Code:

$("button#b1").click( function () {
    $("div#div1").addClass("highlight");
  });
$("button#b2").click( function () {
    $("div#div2").addClass("highlight");
  });
$("button#b3").click( function () {
    $("div p:first-child()").addClass("highlight");
  });
$("button#b4").click( function () {
    $("div#div2 p:nth-child(2)").addClass("highlight");
  });
$("button#b").click( function () {
    $("div").removeClass("highlight");
    $("p").removeClass("highlight");
  });

 

I have enhanced my example with a fancy way to highlight the first div: using a blue border and animations.
http://jsfiddle.net/amagard/pB8nx/7/

Python Expression Evaluator Version 2

I have extended my Python Expression Evaluator (alternate link) )a bit, adding the main feature to support regular expressions . Regular expressions need to be typed in between slashes and need some input text to be applied to, that’s why now a second input field has been added to the user interface.

Regular expressions are very well supported in Python ( like in Perl ), thus not much additional code is needed to support those:

 1: ...
 2:     if expr[0] == "/":
 3:         m = re.match("\/(.*)\/",expr)
 4:         if m:
 5:             expr2 = m.group(1)
 6:             m = re.match(expr2,input)
 7:             if m:
 8:                 for i in range(len(input)):
 9:                     if i in range(m.start(0),m.end(0)):
 10:                         char = "<strong>%s</strong>" % input[i]
 11:                     else: char = "%s" % input[i]
 12:                     for j in range(1,len(m.groups())+1):
 13:                         if i in range(m.start(j),m.end(j)):
 14:                             char = "<span class='highlighted'>%s</span>" % char
 15:                     output += char
 16:
 17:     else:
 18: ...

The Python code above first checks whether input contains a regular expression ( starting with a forward slash ). The first regular expression evaluation (re.match(…; re is the name of the Python module for regular expression supported to be imported at the beginning of the program ) is to get the regular expression itself in between the two slashes, the second regular expression evaluation actually evaluates that regular expression. m is the name of the object returned by the evaluation, having some useful attributes:

  • m.group(0) contains the matching part of the input
  • m.start(0) contains the starting position of the matching part of the input
  • m.endt(0) contains the ending position of the matching part of the input
  • m.groups() is a list of groups defined in the regular expression to extract part of the input; defined in form of round brackets within the regular expression
  • m.group(n) with n > 0 is content of group n
  • m.start(n) with n > 0 is the starting position of group n
  • m.end(n) with n > 0 is the ending position of group n

Those useful object attributes help to do what I attempt to do with that code: highlight those characters in my input string being in the overall match or in any of the groups: the first case is indicated by a bold font, being contained in a group by yellow background color. Thus my for loop

 1: for i in range(len(input)):

iterates over the input provided character by character.Then I analyze whether that character is contained in the overall match to put some HTML “strong” tags around it. Then I analyze for each group returned …

 1: for j in range(1,len(m.groups())+1):

… whether the character is contained in a group and give it a yellow background in that case. To do this I use a class called “highlighted” defined in my CSS file for this little application:

 1: .highlighted { background: yellow; }

And here it is (or here) : version 2 of my Python Expression Evaluator supporting regular expressions

A Python Expression Evaluator

To start going with my first little Python based web application here I came up with Python Expression Evaluator. What is does ? The name says it all: it evaluates Python expressions, which the user can enter into a form and send to the server where this little 25-liner does its work and returns the result plus all the HTML code to render it nicely on the user’s screen:

It allows the user to type in any type of Python expression, like e.g.

  • 80 / 4, or any other type of basic calculation, thus we can use it as a calculator
  • len("Hellow World!") – we can use it to compute the length of a given string
  • "Hello World".count("o") to find out how often a particular character shows up in a given string
  • … and many more ( ideas ? )

Here is the code:

   1: #!/usr/bin/python
   2:  
   3: import cgitb; cgitb.enable()
   4:  
   5: import cgi
   6: form = cgi.FieldStorage()
   7:  
   8: expr = form.getvalue('expr', None)
   9:  
  10:  
  11:  
  12: if expr != None:
  13:     try: output = expr + " => " + str(eval(expr))
  14:     except Exception,e: output = "<font color=\"red\">%s => %s</font>" % (expr,e)
  15: else: output = ""
  16:  
  17: print '''Content-type: text/html
  18:  
  19: <html>
  20:   <head>
  21:     <title>Python Expression Evaluator</title>
  22:   </head>
  23:   <body>
  24:     <h1>Python Expression Evaluator</h1>
  25:     <div>%s</div>
  26:     <br>
  27:     <form action='py_eval.py'>
  28:     Expression <input type='text' name='expr' />
  29:     <input type='submit' />
  30:     </form>
  31:   </body>
  32: </html>
  33: ''' % output 

Let’s decipher what it does:

  1. Let the script know we are using Python code
  2. Import cgitb module and enable CGI Tracebacks to nicely show error messages on the screen rather than in the web server log file. Not really needed here since my little script basically catches all sorts of exceptions, as we will see in a minute. Thanks to Magnus Lie Hetland and his great book "Beginning Python: From Novice to Professional, Second Edition" for this tip !
  3. Import cgi module, mainly used to retrieve values sent to the server
  4. Implement the cgi FieldStorage to retrieve values sent to the server
  5. Evaluate the expression sent to the server. With the help of exception handling all possible exceptions are handled and translated to a message ( variable output ) sent back to the user
  6. Generate the HTML for the user frontend and insert the output message; either the output from the eval() or the exception message.


Try the expression ("10/0") to see how Python’s exception handling catches that error. If I would remove my own exception handling from the code and change it from

   1: if expr != None:
   2:     try: output = expr + " => " + str(eval(expr))
   3:     except Exception,e: output = "<font color=\"red\">%s => %s</font>" % (expr,e)
   4: else: output = "" 

to just

   1: output = expr + " => " + str(eval(expr)) 

then the cgitb module would kick in and transform the unhandled exception into a message shown in the browser, like here for example:

Nice, so far.

What enhancements can we think of to enhance this little tool ? Here are my ideas, any more to come ?

  1. Support multi-line Python code
  2. Support regular expressions
  3. Support expression storage & retrieval ( partially works thru your browser; try to hit the Down while entry field has focus )
  4. Support a more dynamic user interface
  5. 5. … ?

Running a sub process from a Python script

Python is such a powerful language that “Python scripts” actually deserve to be called “Python programs” or “Python applications”, but nevertheless, let’s go with this title for todays’s blog posting: “Running a sub process from a Python script”.

Currently I am writing test scripts for one of our storage products and thus apparently I have the need to invoke commands from my Python script, capture the output and look at the return code.

There are various ways in Python how to do this. One way is popen which allows to run an external command in a sub process and return its output in form of a file like stream, so that it can be read and analyzed any further ( see this easy example ). Nevertheless, there are many popens available for Python, from the standard popen provided by the os module to several variations in other modules like popen2 and subprocess, called popen, popen2, popen3, popen4.  A bit confusing for a Python newbie, especially since I had come up with some specific requirements:

  1. I wanted to capture stderr as well – the file stream for error messages – and actually treat it like the standard output stream so that in case of an error the error messages are displayed where usually the output is displayed. And depending on my future test scenarios I probbaly have the need to analyze the error message stream as well.
  2. I need to capture the return code from the command executed in the sub process.

After working on this for 2 to 3 hours and exploring all the possibilities I got to the point where I thought I either can have 1 or 2, but not both. Until I figured out this solution based on the popen2 module:

   1: import popen2
   2: # ...
   3: cmd = "ping bla"            # No, I am not using foo here 😉
   4: f = popen2.Popen4(cmd)
   5: while True:
   6:     try: line = f.fromchild.readline()
   7:     except IOError: break
   8:     if not line: break
   9:     # ...
  10: rc = f.poll()
  11: f.fromchild.close()

Popen4 from the popen2 module by default combines stderr and stdout into one stream, thus no need to handle both streams. This implementation actually seemed to work nicely, except … I got a “depreciation” warning about the popen2 module when importing it saying basically that this module will go away in the future and will be replaced by the subprocess module.

Thus I had to continue my research and find a working solution based on the subprocess module. Here it is:

   1: import subprocess
   2: # ...
   3: cmd = "ping blabla"                 # Still not using foo, but never mind 😉
   4: f = subprocess.Popen(cmd, shell=True, stderr=subprocess.PIPE, stdout=subprocess.PIPE)
   5: while True:
   6:     line = f.stderr.readline()
   7:     if not line: break
   8:     m = re.match("shell-init",line)        # Ignore shell-init errors
   9:     if not m:
  10:         # ...
  11: while True:
  12:    line = f.stdout.readline()
  13:    if not line: break
  14:    # ...
  15: rc = f.poll()
  16: f.stderr.close()
  17: f.stdout.close() 

Here I get two message streams, one for stdout and one for stderr to be handled independently. One hurdle is that I get strange “shell-init” errors always in my stderr stream, wheter or not the command itself finishs successfully. The other observation to make: the return code obtained by using this implementation is different than the one I get from the first implementation. If the command is okay – in my case I ping an existing network address, I get 0 in both cases. If I ping some non-existing network address ( like “foo”, “bla” or “blabla” ) I get a return code 512 with the first code snippet and a return code of 2 with the second alternative.

Even I have a solution now working quiet well I guess there is still more to explore for me.

File permissions may be fouled up on web server …

Sometimes it happens to me that after I have changed a CGI script and FTPed it over to my web server the script won’t run because it has lost its original permission settings, especially it has lost its “executable for all” file permission. The problem is: I can’t define “sometimes” more precisely. Sometimes I have to change the file permission after FTP has finished transferring the file, sometimes not. I can’t spot a pattern nor discover a fix for this. Somehow I got used to this problem and fixing file permissions became a default activity after I have transferred a file over to my web server. I even stopped wondering whether I am the only one having that problem and possibly overlooked some basic thing to avoid this, or whether this is a more common problem.

Today I have been reading this in chapter 15 of the book “Beginning Python: From Novice to Professional, Second Edition” by  Magnus Lie Hetland:

Tip: Sometimes, if you edit a script in Windows and it’s stored on a UNIX disk server (you may be accessing it through Samba or FTP, for example), the file permissions may be fouled up after you’ve made a change to your script. So if your script won’t run, make sure that the permissions are still correct.

It always feels good if you discover that you are not alone with a weird problem you have. Apparently this really seems to be a more common hiccup happening somewhere between Windows and Linux systems. Good to know.

Python IndentationError

Python is the first programming language I encounter where code indentation is more than a cosmetic issue or a means to make code more readable. In Python code indentation  actually replaces the “DO-END”s or curly brackets used in other languages to define code blocks, for example after an if-statement.

What looks like an elegant way to type less code introduces a new type of error I never have seen in any other programming language: the IndentationError, like this one: IndentationError: unexpected indent

Python requires one level of indentation to be four space characters ! Usually when using a text editor I ( and probably most programmers ) perform indentation conveniently by using the TAB key. Nevertheless, a tabulate character is not necessarily the same than four space characters, as a fact it is not.

When writing my first little Python program using my favorite code editor Notepad++ I was safe as long as I did not use more than one line of code in a code block. As soon as I started to use larger code blocks I suddenly bumped into this error and had to learn that lesson: a tab character is not equivalent to four space characters. Unless you tell your editor to convert a tab character to four space character, like it can be done in Notepad++ for the Python language, under Preferences –> Language Menu/Tab Settings:

After checking on “Replace by space” I seem to have that problem under control. I just wonder why this hasn’t been a default setting.