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

Risk and hope

What science tells us is not that you are guaranteed to die because of smoking. It tells us about an increased risk to get a disease or die because of smoking.
When you deal with risk you also deal with hope. What is true for the average person hasn’t have to be true for a single individual like you.

Why do scientists who know about the risk of smoking smoke ? Especially scientists are probably good in dealing with risks and probabilities.

If you are an optimistic person anyway you always would say: yes, I see the risk, but I will be on the lucky side.

Is that the reason we don’t do risk management in our projects ? Because we prefer to hope than to plan accordingly ? Hoping definitely is cheaper.
It’s kind of gambling. When it comes to smoking, it is kind of gambling  with your life.
I personally wouldn’t do this. I always try to minimize risks, when I can. Well … not always. Otherwise I wouldn’t have done some glacier crossing and climbing on rocky mountains during my last vacation. Well, at least we hired a mountain guide to do so, so we took some risk, but then worked on minimizing it.

That picture doesn’t show me, but our mountain guide. Anyway, I had to go the same way.
You actually always take risks. Your entire life is risky, more or less. Even if you stay at home. Most people die there.
But it is easy for me when it comes to smoking: I never figured out what people like about this and never had any interest in smoking. Except may be a water pipe once or twice a year after a diner in my garden.

IPython and lxml

I have been playing a bit with ipython and lxml these days.

IPython is a powerful and interactive shell for Python. It supports browser based notebooks with support for code, text ( actually html markup ), mathematical expressions, inline plots and other rich media. Nice intro here:

Another nice demo what you can do with ipython actually is the pandas demo video here.

Several additional packages need to be installed first to really be able to use all these features, like pandas, mathplotlib or numpy. A good idea it is to install the entire scipy stack, as described here.

I did the installation first on my windows thinkpad and later on on a Mint Linux box.

This is some work to get thru, like bumping into missing dependencies and installing those first, or try several installation methods in case of problems. Sometimes it is better to take a compiled binary, sometimes using pip install, sometimes fetching a source code package and going from there.

I finally succeeded on both my machines. Next step was to figure out how to run an ipython notebook server, because using ipython notebooks in a browser is the most efficient and fun way to work with ipython. For Windows there are useful instructions here, on my Linux Mint machine it worked very differently, working instructions I finally found here.

After that I developed my first notebook using lxml, called GetTableFromWikipedia, which basically goes out on a wikipedia page ( im my case the one about Chemical Elements ) and fetch a table from there ( in my case table # 10 with a list of chemical elements ), retrieves that table using lxml and xpath code and converts it to csv.

The nice thing about ipython is that you can write code into cells and then just re-run those cells to see results immediately in the browser. This makes it very efficient and convenient to develop code by simply trying, or to do a lot “prototyping” — which sounds more professional.

Having an ipython notebook server running locally on your machine is certainly a must for developing a notebook. But how to share notebooks with others ? I found http://nbviewer.ipython.org allowing to share notebooks with the public. You have to store your notebook somewhere in the cloud and pass the URL to the nbviewer. I uploaded my notebook to one of my dropbox folder and here we go: have a look ! Unfortunately it is not possible to actually run the notebook with nbviewer ( nbviewer basically converts a notebook to html  ).

My notebook of course works with other tables too, like the List of rivers longer than 1000 km, published in this wikipedia article as table # 5.

Since Firefox 30 using unsafeWindow is really not recommended anymore

I had written a little Greasemonkey script allowing to generate some html code from a flickr photo page to use in a blog posting.

Gut gelaunter Baum
"Gut gelaunter Baum" by Axel Magard.

See picture on the right as an example.

That script went to userscripts.org but unfortunately userscripts.org is not available anymore, so you now can get that script from here (OpenUserJS).

That script used code like described here to dynamically load jQuery so that jQuery can be used in that Greasemonkey script. Because of this change in Firefox this code stopped working and I always ran into a Javascript error saying: “Permission denied to access property …”

Luckily this problem has been discussed here on stackoverflow.

The solution: a different way to use jQuery in a Greasemonkey script, bascially thru the @require directive, nicely explained in Taw’s blog here. ( You can check out the source code of my script right away here on OpenUserJS.

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/

Kanban in project management

Yesterday I consumed another of the 1-hour-replays of a project management education session I have to go through in order to gain my PMI re-certification in 2016 ( 60 hours of education are required to get re-certified ).

"" by Jim Downing.

This was about the use of Kanban in project management and I found this quiet remarkable.
Kanban – originally invented for manufacturing processes and based on the "pull" principle – can also nicely be used in project management and I guess works best for agile projects. "Pull" principle here means that team members ( analogy to manufacturing operations ) pull work into their working queue from the predecessor rather than getting work pushed into it. That way, and by implementing some rules like Work In Process limits, the project team ( analogy to manufacturing line ) can be better balanced. The "bottleneck" controls how much work can be done overall while avoiding unnecessary Work In Progress queue’s created somewhere else which are usually causing costly non-value-add overhead and the risk of producing too many defective or out-dated work products.
The wikipedia article ( link posted above ) about Kanban in project management has a nice example of a dashboard visualizing user stories and where they are in the project flow. With such a dashboard ( can be a physical white board for a collocated team, or a piece of software supporting collaboration of remote teams, like Apollo Agile PM or KanbanFlow ) it is easy to keep track of the project and see where resources ( aka people ) are missing and what the status of particular user stories is. Swim lanes can be used to keep track of user stories by feature, or to introduce high priority of fire lanes. Also besides user stories defects can be tracked as well – may be using a different color like red of the Kanban cards.

The Servant Leader

Yesterday I consumed one of the 1-hour-replays of a project management education session I have to go through in order to gain my PMI re-certification in 2016 ( 60 hours of education are required to get re-certified ).
It was about the concept of the "Servant Leader" and I found that concept quiet astonishing. Many think of a leader as the one who has people working for him in order to accomplish a mission. According to the nice quote by Dwight Eisenhower:

"Leadership is the art of getting someone else to do something you want done because he wants to do it."

The concept of the "Servant Leader" actually changes the point of view: leadership as a service, so to speak. The leader servers the team to accomplish something. She enables and empowers the team to get the job done. He identifies barriers and moves those away. He coaches and educates and ensures things are moving into the right direction.
Brilliant concept, in my opinion. My current team lead is actually doing a quiet good job with this.
How about you ? Have you ever met a Servant Leader ?

Follow

Get every new post delivered to your Inbox.