Using timecode in Excel

In the mad technological rat race for high-definition, video compression and desktop compositing with infinite layers and undo levels, it’s not very often that you stumble upon a 20-kilobyte file from 1996 that can change the way you work. In the process of editing documentary films, I spent a while looking for the right way to enter timecode values in an Excel spreadsheet. This can be extremely valuable when working with historical archive footage. Most of the time the editor will be working with files which have “hard” timecode (numbers burned-in on top of the image to prevent illegal broadcast). When the film is done, it is usually up to the editor to dress up a list of all the shots he or she has actually used, so that the final footage – a “clean” frame, free of timecode – can be ordered from the appropriate footage agency.

If you’re the organized type, dressing such a list in Excel can be a pain. For each timecode value entered, you have to type three separate colons so as to obtain, e.g. 10:00:00:00 (asking for a clip that starts at 10000000 will usually get you nowhere). Moreover, if you want to do a few basic calculations (e.g. finding the total duration of all footage coming from a particular agency), you have to go through them with a separate timecode calculator and then waste half a day copying and pasting. I’ve tried to create a formula which would handle all of that but I got lost, quickly, in Excel’s cryptic syntax. And I didn’t feel like learning how to program Excel.

Luckily, as often happens on the third paragraph, an easy and effortless solution exists. A Swiss company, Belle Nuit Montage, has posted on its website an invaluable little macro which goes by the positively baroque name of TC.XLA 1.1. (with Drop-Frame). This little marvel was written more than a decade ago by Matthias Bürcher and will take all timecode formatting and calculating tasks in Excel off your hands. There are versions for Macintosh Excel 4 & 5 – and even DOS! – however the version called “Macintosh Excel 5, downloadable for OSX” is working perfectly well in a more up-to-date environment (in my case, Excel 2004 running on OSX Leopard).

Unfortunately, the English version of the webpage comes with very few instructions (the French version is much more explicit). The idea is very simple: you have to launch the XLA file (either by opening it or by adding it to your default macros). This adds two new styles (which can be found using Format > Styles…), called Time Code and Time Code DF (for Drop-Frame). All you have to do once you’ve applied these styles to your cells is to type the timecode without the colons – much like you would do in Final Cut Pro or Avid – i.e. typing 10000000 will result in 10:00:00:00 being displayed. This isn’t half the story, though.

TC.XLA running in Excel 2004

TC.XLA running in Excel 2004

TC.XLA adds a dozen of new and handy functions that are very simple to use. You can now easily add and subtract timecodes, convert between frame rates and even find the value of a timecode in feet for 16mm or 35mm film. The macro seems to support all standard drop and non-drop frame rates, although I’ve only worked with it in 25fps (which is the rate the code defaults to if you don’t specify another one).

The syntax for the functions is very simple and is described on the Belle Nuit Montage website. As an example, entering “TCminus(E4;D4)” into a cell will give you the timecode difference between the values in E4 and D4. Presuming the two cells represent IN and OUT points, the result is obviously the duration of the shot. You can then easily add up a list of those durations with TCsum. This last function doesn’t seem to refresh properly when changing values upstream, but forcing a recalculation (Command-=) solves the problem. This is a minor niggle, though, compared to the ease of use and practical value of this tiny piece of free software.

Update: The above works fine for Excel 2008 as well, however you must press Command-Option-T after creating your workbook in order to enable the macro. The status line should respond with “TC-XLA 1.1″ followed by a URL to confirm this.

Tagged , , , , | 17 Comments

Install Python 2.6.1 without trashing Ubuntu

After several trials and tribulations, I finally managed to get an independent Python 2.6.1 version running smoothly on Ubuntu 8.10 (“Intrepid”), without interfering with the system’s reliance on Python 2.5 and without breaking the entire package dependency system. The procedure described here has been successfully carried out on the Easy Peasy distribution of Ubuntu (ex-”Ubuntu-eee”), specifically tailored for the Asus EEE-PC. It should work, though, with any other 8.10 version of Ubuntu. It has also been tested successfully on Ubuntu 8.04 (“Hardy”).
Read More »

Tagged , , | 9 Comments

Python and Leopard

A messed-up Python installation on my Mac OSX Leopard system has caused me a fair amount of googling with no solution to be found. For no apparent reason, Python’s environment variable $PYTHONPATH got set to ‘/Applications/Dropbox.app/Contents/Resources’, causing havoc in Terminal and preventing IDLE from launching.

Several posts to the Python forum as well as the Apple Developer 101 discussion board got me no further than “Thank Dropbox’s authors for destroying your system and pray you have a backup.” This was evidently not the issue since Dropbox was working fine alongside Python and IDLE on my MacBook (although the fact that Dropbox.app contains a ‘Python.framework’ folder didn’t make figuring it out any easier). Googling about it came up with a fair amount of forum posts by people asking how to “reset” $PYTHONPATH or the sys.path variable to a default state. Replies were not very helpful, especially for UNIX neophytes like myself. Resintalling XCode, Dropbox, Python 2.6 or MacPython didn’t help either.

Luckily, I found a very simple solution that I thought I’d add to Google’s search engine: simply download and install the Mac OSX Combo Update for your version of Leopard (such as 10.5.5). This seems to fix the references to the default 2.5.1 framework that comes with Leopard, make importing modules functional again and, as a positive side effect, lets IDLE run.

Tagged , | 4 Comments

Bobigny, mémoires d’une cité

Directed by Marie-Pierre Jaury

TV documentary (2007) 50′

Produced by Au fil de l’eau / France 5

  • Original music score
Leave a comment

Quand l’art prend le pouvoir

Directed by François Lévy-Kuentz

TV documentary (2008) 2×26′

Produced by Les poissons volants / Arte

  • Camera
  • After Effects Animator
Leave a comment

Plugin-based photography section

I finally got to designing a unified photo gallery which exists independently of the posts. Some photographs are taken from the posts, while others can only be found in the gallery.

In order to make the gallery as flexible and automated as possible, I got to writing a custom PHP function which finally developed into a Wordpress plugin called Simple Lightbox Slideshow or SLS. Like most of the images on this site, it relies heavily on Lightbox 2. The plugin automates slideshow creation by simply scanning for files inside a specified folder. It was written for use on this site but it is flexible enough to be included in any Wordpress installation. You can try it out on the plugin page.

Tagged , | Leave a comment

Mourir pour la voiture

Directed by Paul Moreira

TV documentary (2007) 52′

Produced by AMIP / Premières Lignes

  • Camera
  • AVID Editor
  • After Effects Animator

Watch the trailer

Leave a comment

Realistic HDR tonemapping

Lately I’ve spent some time experimenting with HDR tonemapping using Photomatix Pro and Photoshop. I find that most examples you bump into on the web demonstrate the ability of tonemapping to create very striking and surreal images. But actual benefit of this technique, in my opinion, is that it allows a photographer to capture a scene exactly the way it looks in real life. Granted, it is very gratifying to use tonemapping’s dynamic range compression to manipulate light in a creative manner, with results like the photograph above. What seems like an infrared shot of a snow-covered park is actually a photograph taken on a sunny day which presented lots of variations between lights and darks. Different exposures were assembled into an HDR image which was subsequently tonemapped. The resulting 16-bit image then went through some funky monochrome channel mixing in Photoshop.

However, it is interesting to see what happens when you use tonemapping in a more discreet fashion. With everyday subjects and settings, it is possible to obtain images which otherwise could not have been captured but which do not present that “in your face” effect. For a low-resolution medium (such as a web page), you can get by with bracketing the exposure using fast continuous-shooting mode and keep the camera handheld. If people don’t move too much, the artifacts associated with HDR misalignment are reduced to a minimum. This results in ordinary-looking photographs which correspond a lot more to the scene as it was originally framed in the viewfinder. The following images illustrate the point.

Tagged , , | Leave a comment

Châteaux de Lastours

The following photographs are some basic experiments I did with HDR tonemapping vs traditional masking techniques in Photoshop. The view from atop the Châteaux de Lastours, brightly lit, had obviously a very high dynamic range in terms of light energy. I have tried capturing it using two methods. The first one simply involved shooting two exposures, one for the highlights and another one for the shadows. They were then layered in Photoshop and masked with a simple linear gradient (similiar to what a gradient-density filter would have done had I had one in my bag). The second method consisted of shooting seven auto-bracketed exposures, 1 EV apart, combining them into an HDR image and then tone-mapping that in Photomatix. Since the composition is basically horizontal, the results were very similar. All images were shot RAW on a Nikon D200 and developed in Lightroom.

The following image is the result of layering two exposures in Photoshop:

This is the image resulting from tone-mapping a seven-exposure HDR file:

The main difference between the two is obviously the horizon, which has much more detail in the tonemapped image. Blowing out the horizon highlights in the layered version was a necessary compromise in order to avoid darkening the top of the tower too much. But apart from that both methods achieved similar outputs. Tonemapping would win hands-down, though, in a more complex composition.

On a final note, the same subject from a different viewpoint later in the day, using with a single exposure and no Photoshop editing. Just to point out that sometimes simplicity can also be a valid solution.

Tagged , , | Leave a comment

u3 and OS X

I never thought that getting a brand-new USB key to work could turn into a configuration nightmare, requiring trips to four separate utilities and two operating systems. This is what happened last week with the SanDisk Cruzer Micro 2GB USB Flash Drive. This USB key uses a SanDisk-developed technology called U3 (obnoxiously marketed with the slogan “It’s what’s next. It’s what’s smart.”) What this piece of code does is allow Windows XP users to launch applications directly from the USB key, therefore allowing them to "leave no personal data behind". Unfortunately, not only is this a Windows-enhanced technology, it is actually incompatible with Mac OS X. And getting this thing to work on Apple’s OS is an exercise in frustration. The whole idea of a USB key was to replace portable rewritable media like floppy disks, which were universal in their hardware implementation (regardless of OS-specific formatting and file-system). This was true for USB keys as well, at least until U3 arrived.

When you plug a U3-enabled USB key into your Mac, what you get is the (expected) disk image, accompanied by a second icon, a CD image, called "U3". If you’re like me, you buy this thing to quickly get a couple of portable gigabytes of storage, and the last thing you want to care about are "enhancements". It’s like buying a hard drive (or, if memory serves, a floppy disk): you just want to connect it and format it for the file system you’ll be using. But what happens after a quick formatting trip to the OS X Disk Utility is that you’re left with an unusable piece of plastic. No matter what you do, you’re stuck with a drive that won’t mount and, more annoyingly, can neither be erased nor partitioned. The USB key is now read-only. Plugging it into a Windows machine, downloading "uninstallers" from the SanDisk website, cursing the innovation-obsessed IT market, whatever you do, the key is dead. Even the Launchpad Remover, distributed on the U3 website, won’t be able to do anything with it. It’s important to note that this is not a SanDisk-only problem; it happens on several (maybe all) models of U3-enabled keys.

The outrageous part of all this is that while the packaging clearly claims Mac OS X compatibility, as you can see on the left, an angry trip to the SanDisk support website is all that’s needed in order to find out that the very same manufacturer actually states the opposite. This is an ancient way of making quick money: sell something which you acknowledge doesn’t do what you say it does. I wrote an email to SanDisk customer support about this glaring inconsistency between their own two statements. Their reply, which arrived surprisingly fast, suggests that they might rethink either the technology, the packaging or their marketing information in the future, which is a good thing:

The U3 part of your Cruzers are not supported on MACs, but the removable drive part will work and is supported on MAC computers. We would like to thank you for pointing this out to our attention. We have already forwarded this information to the proper personnel for review.

As of now, we cannot give you a definitive answer concerning the matters you have presented but rest assured that the personnel for this matter will greatly consider it for our product improvement.

And since I’d rather not wait for the SanDisk product improvement team to fix this, I searched the internet again, and again, and finally found a post on the Canadian ehMac forum. The author, Mark Rushton, had the exact same problem with the GXT Mobile Disk USB 2.0, and posted a quick and (almost) hassle-free solution. All you need is a Windows machine (I found one which had Parallels Desktop installed and it worked fine) and the Drive Key Boot Utility from HP (45mb download, no registration required). Just plug your dead USB key into a Windows XP machine (real or virtual), run the app, click through all the recommended options and let the software pulverize the drive’s U3 enhancements into oblivion. Don’t worry if it takes several minutes (with no progress bar) or if the program stops responding; just wait until it’s finished. Go back to the Mac OS Disk Utility and do whatever you want with your new, fully functional USB flash drive (which is what you wanted in the first place). Thanks Mark.

UPDATE: Apparently it’s possible to accomplish the above using Terminal without downloading anything, as suggested by Damien in a comment to this post. I haven’t tested it myself but it does seem to be a very simple solution. Thanks Damien.

Tagged , | 30 Comments