Counting People FAQ

Video Turnstile
People Counting Systems from Biodata Ltd

Using Excel to Generate People Counting Reports

The Video Turnstile system lets you use any software you wish to analyse your data and generate reports. This page gives advice on using Excel to do so.

With just a couple of mouse clicks Excel can load your people counts, analyse the data and present the results in a report ready for printing. To set this up you need to create a macro to automatically produce the report. We've created an example of how to do this, which you can modify to suit your own analysis requirements. Click here to download the Excel example: Excelreport.zip. Unzip this into your data folder (by default c:\videoturnstile\data\).

To run the Report Generator you need the file "mscomct2.ocx" installed in Windows. You may already have this on your computer, but if not you can click here to download it mscomct2.zip

To install this component:

  1. Copy the file mscomct2.ocx to the c:\windows\system32 folder
  2. In Windows, select Run from the Start menu
  3. Enter: regsvr32 "c:\windows\system32\mscomct2.ocx"

You should get back a message to say that the component has been registered successfully.

If you are running a windows NT based system you may have to change c:\windows to c:\winnt.

Using the Report Generator

  1. Open reportWL.xls. You are asked to select a report.
  2. Choose the date from which you want to the report to run.
  3. Choose whether you want to create a daily, weekly or monthly report.
  4. Select the "Make Report" button.

The daily report gives the mean, maximum and minimum values for each channel on the chosen day. The weekly report gives the mean values for each channel for each day of the week. The monthly report gives the mean values for each channel for each day of the month. You can modify the macro to provide the information that you want.

The macro assumes that you have used VT Collect to collect data and have chosen to do once a day. It also assumes that the folder holding your data is c:\videoturnstile\data. You might need to edit the macro to suite your setup.

How the Report Generator Works

The macro is contained in the reportWL.xls file. When you open this a form is shown asking you to choose your report type and date. The Report Generator then opens the appropriate Excel template file: dailyWL.xls, weeklyWL.xls or monthlyWL.xls. These contain a title, such as "Windmill Logger - Daily Summary" and some headings. To change these close the Report Generator (reportWL.xls) and open and edit the template files.

Editing the Macro

To edit the macro first hide the Report Selector form. Open the Visual Basic Editor and edit the code attached to the ButtonForm.

The macro assumes that the Excel and Logger data files are in the "c:\videoturnstile\data" folder. If this is not the case then change the first line: Const DEFAULT_ROOTDIRECTORY = "c:\videoturnstile\data"

The macro automatically finds the correct data files. To do this it assumes that you have used VT Collect to collect data once a day and that your site prefix is "mySite". To change the site prefix, just edit the macro's second line: Const WLFILE_PREFIX = "mySite"

If you want to analyse data collected by Windmill Logger rather than VT Collect, edit the UserForm_Initialize() procedure and changing the line
flFileType = FILETYPE_VTUPLOAD
to
flFileType = FILETYPE_LOGGER
The macro will then find data files generated each day by Logger.

The appropriate type of report - daily, weekly or monthly - is created by the showDaily, showWeekly or showMonthly procedures. These have a number of things in common. They all:

They then perform the appropriate calculations and enter the results. The Daily Report Generator, for example, calculates the mean, maximum and minimum values for each channel.

Taking the mean value as an example. The Daily Report calls the analyse_SumColumn function which returns the sum of a column of numbers, and stores this in the SumChan variable. SumChan is divided by the number of rows of data (sumCnt) to calculate the mean, and this is entered into the appropriate cell of the report as follows
reportWkb.Worksheets(1).Cells(START_ROW, iChan + 1).Value = SumChan / sumCnt
You can replace the "SumChan / sumCnt" with the results of your own calculations.

Printing and Saving the Report

To print the report simply select the print button. To save the report hide the Report Selector form then select Save As from the File menu.

Your Comments

Do you have a question or a comment on generating your reports in Excel? Let us know. 

Name:          
E-mail:        
Organisation:  
Excel Comment: