Monday, November 29, 2010

Personal expense sheet

With the formula I presented in my previous blogpost on Excel look-ups I created an Excel sheet to monitor my personal expenses.
It is based on the data I extract from my different online banking systems (currently for the Belgian banks Argenta, Landbouwkrediet and KBC).

In the 'Categories' sheet, different keywords linked to the category name need to be filled in, this could be the name of a company or the account number. The keyword will be looked up in the account and comment of each transaction. And this will make it possible to automatically categorize each bank transaction and automate some analysis/summary overview on the transactions.

Every now and then, I copy the data from the online banking system into the 'data' Excel sheets. All expenses are categorized automatically with my formula in the column 'Auto Type', but if some exceptional category needs to be assigned, a 'Manual Type' column can be set to override the 'Auto Type'. Based on these categories, I created some sheets with an summary overview for each month and some averages per month and per year. This way, I can get a clean overview on our expenses and incomes and keep a copy of our banking data offline as well (since many banks only keep last 2 years online).

Based on my personal sheet I created this empty (dutch) template sheet, but it will still need some custom changes to be usable for someone else. But it could be a nice starting point.

Thursday, November 25, 2010

GIT usage (for an SVN user)

I put together some stuff about git I found interesting.


To get started with git on Windows, you'll probably want to download TortoiseGIT (and this requires msysgit)


To use git, a username and email need to be configured. This can be done in of TortoiseSVN -> Settings -> Git -> Config.

Migration from SVN

A very good manual to migrate your existing SVN repository to GIT can be found in this blog post of Jon Maddox.

Good to know

Git tracks content not files
Many revision control systems provide an 'add' command that tells the system to start tracking changes to a new file. Git's 'add' command does something simpler and more powerful: git add is used both for new and newly modified files, and in both cases it takes a snapshot of the given files and stages that content in the index, ready for inclusion in the next commit.

Most SCM systems use Delta Storage systems - they store the differences between one commit and the next. Git does not do this - it stores a snapshot of what all the files in your project look like in this tree structure each time you commit. This is a very important concept to understand when using Git.

There is only one Git Directory per project (as opposed to one per subdirectory like with SVN or CVS), and that directory is (by default, though not necessarily) '.git' in the root of your project.

Git is much faster than SVN.

In SVN, each file & folder can come from a different revision or branch. At first, it sounds nice to have this freedom. But what this actually means is that there is a million different ways for your local checkout to be completely screwed up.

You have to tell SVN whenever you move or delete something. Git will just figure it out.

Branches are cheap and easy to merge, so this is a good way to try something out.

A single git repository can maintain multiple branches of development. The 'master' branch is a default branch that was created for you automatically. The 'git checkout branchname' command will switch between branches. The command 'git merge branchname' will merge changes from another branch in the current active branch.

The 'pull' command performs two operations: it fetches changes from a remote branch, then merges them into the current branch.

'git commit' commits locally, whereas 'git push origin master' pushes the master branch to the remote named 'origin').

Git adds complexity. Two modes of creating repositories, checkout vs. clone, commit vs. push... You have to know which commands work locally and which work with "the server" (I'm assuming most people still like a central "master-repository").

Git is MUCH better suited if some developers are not always connected to the master repository.

Even if you don't have commit rights for a project, you can still have your own repository online, and publish 'push requests' for your patches. Everybody who likes your patches can pull them into their project, including the official maintainers.

Drawbacks of Git:

  • it's much harder to learn, because Git has more concepts and more commands.
    • many Git commands are cryptic, and error messages are very user-unfriendly
  • revisions don't have version numbers like in subversion
  • you have to have a full copy of the repository, you can't work on partials

Git branching model

A very good post on git branching model
Pdf model overview
  • Central repo with 'origin/master' and 'origin/development' branches (infinte lifetime)
    • 'origin/master' branch reflects the production release
  • Supporting branches
    • feature branch: branches from development and merges into development, branch name different from master, develop, release-* or hotfix-*, exist in developers repos only, not in central origin
    • release branch: branches from development and merges into development and master, branch name release-*, all feature branches must be merged in development before release branch is branched off
    • hotfix branch: branches from development and merges into development, master and release, branch name hotfix-*

Central Repo

If you like to setup a git central repository on Windows (for example to replace your VisualSVN environment), you can follow this manual.

Update 23/04/2015: A good tutorial and Git related info can be found on this Atlassian site.

Wednesday, November 24, 2010

MoWes Portable + OS Commerce 2.2 / 3.0a

I wanted to play with OS Commerce. Since this is written in PHP I needed some portable PHP, Apache and MySQL package. MoWes Portable does exactly what I was looking for and they offer some very nice online MoWes Mixer so anyone can easily create it’s own portable package (choice in version, packages, etc.). I created my own package with OSCommerce v2.2, v3.0a and phpMyAdmin. I added extra languages and applied some standard configuration/tweaks to make it immediately usable in Belgium.

Full package download containing MoWes Portable 2, Apache2_SE, MySQL5_SE, PHP52, MediaWiki, OpenDB, OS_Commerce 2.2, OS_Commerce 3.0a, phpBB3 and PHPMyAdmin.

VirtueMartVirtueMart is a similar open source project, but build within Joomla!. For me they seem very similar in the provided functionality. Joomla and thus VirtueMart can be integrated within MoWes as well using the MoWes mixer.

InstantRails + Redmine

I just discovered Redmine, and it looks quite promising to me as a project development tracking system.


Since I’m a big fan of portable applications, I tried to play with Redmine using InstantRails, but had some trouble getting it to run, since InstantRails uses some old versions of some required gems. So after upgrading gems to 1.3.7 and rack to 1.0.1 and finding some old version of the MySQL lib libmysql.dll v5.0 I got it all working nicely locally and portable. If anyone would like to play with it: download InstantRails 2.0 + Redmine 1.1.1 (42MB). I removed some parts of the gems documentation files to make it smaller.

The InstantRails package contains MySQL, Apache, Ruby and Rails. To use it, just run ‘InstantRails.exe’, the Apache and MySQL servers will automatically be launched.


During startup, InstantRails may request to regenerate the configuration because of a folder location change. Just press ‘Yes’. If Apache doesn’t start automatically, make sure the ‘apache\logs’ folder exists.

Next click on the ‘i’ button, in the menu chose ‘Rails Applications’ –> ‘Manage Rails Applications’, select ‘Redmine’ push the button ‘Start with Mongrel’.



A command window will open up and when it’s loaded you can navigate to http://localhost:3003 and start using your own personal portable Redmine. (default login: admin, password: admin). To manage the database, phpMyAdmin is included as well, just navigate to http://localhost/mysql.

If you don’t care to have it portable, you could as well install Ruby on Rails and Redmine using this 1 minute guide.


Redmine 2 is included, but I added the following plugins I found interesting as well:

  • Codereview: code review comments can be added in repository within Redmine. It has a project tab with an overview of the code review comments as well
  • Hudson: project tab for Hudson administration
  • Issue due date: automatically set issue due date to the version due date
  • Timesheet: create a timesheet with overview of tracked timings
  • Time Tracker: easily track timings while working on issues
  • Wiki notes: extra wiki options, example created
  • Wiki tabs: possibility to add a project tab linked to wiki page, not configured yet
  • Tab: custom project tab configuration, configured to get the Sonar project information page


Within Redmine I configured roles, issues states, permissions and a workflow. I set up different users, projects and issues to be able to play with different configuration settings. Each user is configured to use the same password as the username: admin, teamleader, developer, etc. (see administration console). It is very easy to configure which role can perform what tasks and see specific content within Redmine.


Creation and management of issues is straightforward. It has a good integration of time tracking, at every update of an issue, the user can immediately update the time tracks.


With the time tracker plugin, it is even easier to track your timings. With an issues selected, you can easily let Redmine keep track of the time you spend on that issue by using the start/stop links at the top.



An important part of an issue tracking system is the reporting capabilities.

A summary with the number of issue in open or closed state can be seen:


In the list of issues, filters can quickly be added and columns can be chosen:


In the roadmap overview, all versions / planned version of the project are listed with an overview of their related issues:


The activity overview shows the activities performed within a project.


The spent time within a project can be viewed in an overview:


But custom reports can be created on the fly as well:



With the Timesheet plugin, an overview of all projects, all users, etc. of the spent time can be generated easily on the fly:



Specific plugins exist to integrate the Redmine issue lists with TortoiseSVN and TortoiseGIT: Tortoise Redmine Plugin.

For full integration, follow this manual for the BugTraq properties configuration.


Points of improvement

One thing I noticed during my tests: if an issues is updated and a user changes the state of the issue, he still can select any user to assign it to. In my opinion, only users able to make changes on the selected target state of the issue, should appear in the list of users to assign the issue to. An issue tracking system should enforce all constraints of the workflow to make sure an issue can’t come in an invalid workflow state (eg. assigned user can not change the status). But this problem occurs on most tracking systems I checked (Track+, Jira, etc.). See this feature request, I checked the plugin development, but so far I couldn’t make it work myself yet.

Update 31/01/2011: InstantRedmine package updated to Redmin 1.1.1

Tuesday, November 2, 2010

Excel lookups in formulas

Different solutions exist to work with looked up data in Excel formulas: LOOKUP, VLOOKUP, HLOOKUP.

  • LOOKUP(value, lookup_range, result_range): searches for value in the lookup_range and returns the value in the result_range that is in the same position
  • VLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.
  • HLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the top row of table_array and returns the value in the same column based on the index_number.

But I needed yet some other lookup functionality, something like SEARCHHLOOKUP(search_in_text, lookup_range, result_range) where values from the lookup_range are searched in the text of search_in_text and if found the value of the result_range with the same column as were it was found is returned.

I wanted to be able to define some categories, with keywords linked. If a keyword occurs in a sentence, I wanted the category name as result. and I wanted to easily add new keywords for each category, without changing the formula. For example sheet CATEGORIES:

Fruit Food
1 2
1 apple chocolate
2 banana milk

Next I have a cell with value: "Apple belongs to category" In another cell I want a formula (no VBS) that would result in the category name: "Fruit". For example sheet EXAMPLE:

1 Apple belongs to category Fruit
2 Chocolate belongs to category Food

(Example SearchLookup.xlsm)

Since I need to search for the keyword in a sentence, I use the SEARCH(search_text, search_in_text, start_position ) function. If the result is bigger than 0, the keyword was found (not case sensitive). But if the keyword was not found, an error value #VALUE will be returned. To catch this error value, the function IFERROR(value, value_if_error) can be used. So I get this function: {=IFERROR(IF(SEARCH(CATEGORIES!$A$2:$A$5;EXAMPLE!$A1)>0;CATEGORIES!A$1);"")}

But this will only lookup keywords in my first column of Categories, while I want many more. I solved this by attaching a weight to each category and using the formula CHOOSE(position, value1, value2, ... value_n ) to select the category name corresponding it's weight. To ignore the error values when the keyword is not found, I return 0 if an error occurs and the category weight when the keyword was found, so a MAX on that array will result in the matching category weight. To make sure that 0 is returned, only when the keyword is not found, I add any single character (µ in this case) in front of the text to search in, else 0 could be returned if the text to search in starts with the text we are searching. Now the search has to be bigger than 1 when the keyword is found.

The result is this formula: {=CHOOSE(MAX(IFERROR(IF(SEARCH(Categories!$A$3:$A$6;"µ"&Example!$A1)>1;Categories!$A$2;0);0);IFERROR(IF(SEARCH(Categories!$B$3:$B$6;"µ"&Example!$A1)>1;Categories!$B$2;0);0))+1;"";Categories!$A$1;Categories!$B$1)}

Since we apply the SEARCH function on an array, the complete formula has to be an array formula, so don't forget to press CTRL+SHIFT+ENTER to save as an array function and get the "{=...}" signs around the formula.

So using a combination of CHOOSE, MAX, IFERROR, IF and SEARCH functions I can lookup category names base on keywords and the keywords can be added dynamically. The only "problem" left is that I need to change my formulas when a new category is added, but at least not when adding keywords in a category.

To solve this last problem, I ended up creating a 'User defined function' SEARCHHLOOKUP(search_in_text, lookup_range, result_range, (result_range_index)):

Function SearchHLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)
'Written by myT -
'Values from the lookup_range are searched in the text of search_in_text
'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned
'A B
'1 2
'3 4
'if 2 or 4 is found in Search_in_text, B will be returned
'if 1 or 3 is found in Search_in_text, A will be returned
'if none is found, empty string will be returned
Dim iRow, startRow As Integer
Dim iColumn, startColumn As Integer
If Result_range Is Nothing Then
startRow = 2
startRow = 1
End If
startColumn = 1
For iColumn = startColumn To Lookup_range.Columns.Count
For iRow = startRow To Lookup_range.Rows.Count
If Not (Lookup_range(iRow, iColumn) = "") Then
If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then
If Result_range Is Nothing Then
SearchHLookup = Lookup_range(1, iColumn)
ElseIf Not (Result_range_index = 0) Then
SearchHLookup = Result_range(Result_range_index, iColumn)
SearchHLookup = Result_range(1, iColumn)
End If

Exit Function
End If
End If
Next iRow
Next iColumn
SearchHLookup = ""
End Function

So in my example, the function I use now has been simplified to:


Of course, a SearchVLookup could be made easily as well:

Function SearchVLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)
'Written by myT -
'Values from the lookup_range are searched in the text of search_in_text
'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned
'A 1 2
'B 3 4
'if 1 or 2 is found in Search_in_text, A will be returned
'if 3 or 4 is found in Search_in_text, B will be returned
'if none is found, empty string will be returned
Dim iRow, startRow As Integer
Dim iColumn, startColumn As Integer
If Result_range Is Nothing Then
startColumn = 2
startColumn = 1
End If
startRow = 1
For iRow = startRow To Lookup_range.Rows.Count
For iColumn = startColumn To Lookup_range.Columns.Count
If Not (Lookup_range(iRow, iColumn) = "") Then
If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then
If Result_range Is Nothing Then
SearchVLookup = Lookup_range(iRow, 1)
ElseIf Not (Result_range_index = 0) Then
SearchVLookup = Result_range(iRow, Result_range_index)
SearchVLookup = Result_range(iRow, 1)
End If

Exit Function
End If
End If
Next iColumn
Next iRow
SearchVLookup = ""
End Function

The user defined function needs to be defined in a module. Example: SearchLookup.xlsm)