In a recent effort to pull data into an Excel spreadsheet for further analysis, I needed more flexibility than a static report URL could provide. The back end could accept parameterized values (e.g. the desired date range) in the report URLs. I just needed to be able to set these values (initially or through user input) from within the spreadsheet, then request the needed data. Seems generally simple and useful enough, so I’ll share what I did.

Sample Report

Ignoring most of the particularities of the reporting back end, say the report in question consists of an SQL query:

The URL to access it is http://initech.com/reports/total_profits. Using the query above, this would return data from the beginning of 2000 to the end of 2010. To pass in values for the start and end date parameters you would use the URL http://initech.com/reports/total_profits?begin_month=2000-03-01&end_month=2000-06-31. This would limit the data returned to the second quarter of 2000.

Use in Excel

So what if you want to use dynamic parameters like these when pulling data into Excel? Let’s assume you have a spreadsheet already pulling data from the static report URL and displaying results. If you run into trouble, check out my earlier article on the subject. Once that’s working, right-click the resultant data table in Excel, select Edit Query, and add the parameter names and values in the format ["FieldName"]:

FieldName in this case can be whatever you want to call the variable for the user’s convenience. This format is recognized by Excel and you’ll be prompted to enter a value. You can enter it yourself of course, or you can select a cell from which to pull the value. You can also specify whether to use the cell for future data refreshes and whether to refresh automatically. These features are key for our intended use. You can set a parameter value in a single cell and have it effect any number of web queries, across various sheets!

Note: This last feature only partially works in Mac Office 2008. A cell’s value can be selected as a parameter value but after updating it you have to manually refresh all related web queries. In Windows Office 2007, updating the specified cell updates all queries that have this property set, which is great for easing administrative load caused by having to update reports!

Date format annoyance

Your backend reporting system might likely expect dates in proper ISO format (YYYY-MM-DD). Excel, in its infinite wisdom, allows “Date” typed fields to be one of a myriad of formats, not one of which is proper ISO. So you need to make your date fields Text type and enter the dates as ISO format yourself.

This article has additional information on the above approach as well as more screenshots of the various steps.

Upgrading

I ran into a memory limit issue for PHP when I first tried the upgrade automatically, due to the larger size of this new version. This was easily fixed by following this post’s advice. After that I just clicked Upgrade Automatically and what do you know WordPress updated itself properly in no time at all. I then went to the Dashboard and was greeted with the new hotness.

Themes and such

As you can see if you’ve been to this blog before, things look a bit different. This is mostly because it’s so easy and fast to customize many visual aspects in the new version that, well, it’s hard to resist tweaking!

I decided to go with the new theme included in WordPress 3.0: TwentyTen. With a variety of attractive headers, six widget areas, and great looking typography fresh out of the box, this is a much improved default theme. One of my favorite features: when editing in Visual Mode, your post content will appear just like it would in your actual blog! This means no more processes like: make some edits, click save, wait for the save, click preview, check out the preview in a new tab. Just keep working in the same editor and get the real view right away.

You can now also add and edit your blog’s menus without editing any files through the Appearance Menus. The positioning of this through the UI is limited by what your theme supports, but its much easier to customize and get what you actually want into a menu than before.

Plugins

Working with plugins is also made a bit easier in WordPress 3.0. You can update all plugins with updates available in a single step. And you get lots of handy info about the upgrade from a single screen. I mean just look at this:

Awesomesauce. And when you are done, it shows you the status all at once as well, so you know if that big batch of upgrade cookies came out alright or not:

In this and many other areas, the admin interface has been streamlined, making just about everything easier to get to. And the icing on the cake: everything is faster anyway! I didn’t make any other changes to my server, but since the upgrade interacting with everything in the admin interface has been much snappier. Add to that a whopping 1200 bug fixes and this is a great milestone release.

New Flexibility

Another big feature of 3.0 is Custom Post Types. This allows you to register new types aside from just the default Posts, Attachments, Pages, Revisions and Nav Menus. You can define the custom type’s attributes, how to display it, and more. This would be great for more business-oriented blogs, or even simply highly-customized personal sites.

WordPress 3.0 is an incredible update! I highly recommend upgrading to it.

Osmos is a wonderfully fun and good-looking game. I was so impressed by the demo (a game that starts off with Newton in Latin deserves my attention) that I purchased the full game. I might add this is only $10 and allows you access to DRM-free versions on Windows, Mac, and Linux, as well as a download of an ambient album used in the game! Quite a deal for a game that can provide many hours of enjoyment. It’s like a simpler version of the first stage of Spore, with more interesting novel elements and music reminiscent of Blade Runner. Full size screenshot here.

Go check it out, it’s a lot of fun. And if you are on Linux and want to see more great games for a great platform, consider buying the game!

If you use Vim only to edit text files when you happen to be logged into a CLI: mosey on along, this isn’t the book for you. If you currently use Vim regularly and want to improve your knowledge and effectiveness with it: your time reading this book will be well-spent. If you’ve somehow never heard of Vim but are enraptured by the idea of a text editor that can be an effective IDE in addition to allowing you to play Tetris and Rubik’s Cube: you might like this book too.

At first this book seemed rather short for such a potentially expansive topic. In the end I found this to be mostly a virtue rather than a problem, as the amount of information presented is, in fact, voluminous. A more comprehensive approach likely would have been too hefty and less effective. It doesn’t pretend to be a manual for Vim. Instead it illustrates the best approaches for various issues and situations, suggests improvements on more common operations, and provides lots of references to external resources (primarily Vim internal help and vim.org) to learn more. Examples and illustrations provide insight into some of Vim’s lesser known abilities, while commonly used ones are explained and presented in new and useful ways. Groups of features resolve into solutions.

Chapter by Chapter

Chapter 1, Getting Started with Vim

Primarily a history of the Vim editor. Useful to expand the knowledge of Vim fans, but not really pertinent to hacking your beloved editor. Would probably have been better as an appendix or excised entirely.

Chapter 2, Personalizing Vim

Editing your Vim config, customizing fonts, colors, menu buttons and other UI elements. Lots of great material for tuning the visual appearance. More about vim.org and other resources for colorschemes (such as this gem) would have been helpful. The ability to add tooltip balloons was new to me and quite exciting. Schulz provides a number of great examples for their use. The discussion of abbreviations was a bit confusing, especially considering the recommendation of the more intuitive snippets approach in Chapter 4.

Chapter 3, Better Navigation

By now you might already be feeling overwhelmed by the number of key commands to remember. I recommend starting a cheat sheet of those you find especially interesting. Speaking of that, perhaps it’s just my predilections and lack of a perfect memory, but the vast number of code navigation keys was just a bit too much. Sure, “extra keys pressed equals extra time wasted”, but what about extra time sorting through key commands in your thoughts? Still, plenty of tips should be mind-blowing if new to you. Jumping to files named in the text of the current file, for example, is really handy. The information about search improvements, both in user files and in the help system, was quite good. I’ve yet to really use signs or marks, but found their explanation here illuminating.

Chapter 4, Production (Productivity?) Boosters

Templates! Templates are awesome, and this was a welcome introduction to a topic I’ve barely scratched in my Vim journey. I’ve also not used placeholders in templates before, which look to be quite useful. Tag lists and essential plugins for their use are explained, providing an effective presentation of this feature. I was getting a little worried around pg. 86, as the number of Ctrl + x, Ctrl + SOME_KEY commands started to get cumbersome. Then came the explanation of triggering all completion types using a single key, which was quite refreshing. Complete using dictionaries, words in the current document, language files, and more, all using a single key. The SuperCleverTab function will be a very welcome addition to my configuration. Until seeing the example about macros, I never really felt how useful they might be. This definitely convinced me.

But this meaty chapter isn’t over yet! Sessions felt rather opaque to me before reading this section, and I think they will now definitely serve a role for me. No more ritual of opening the right files, folding them just how I want, etc every morning. Undo branching… I still find this rather too complicated and marginal of a feature to care about. But the examples were at least explanatory. Folding is a great eye-saver, and the coverage was adequate. I’m still not really a fan of vimdiff, as there are other diff utilities out there that make the process faster and more intuitive to me. One exception is finding the diff between the current buffer and the saved file, which is very handy and not something available in most editors for sure! I hadn’t opened remote files in Vim directly before, so the section on this at the end of this chapter was quite useful.

Chapter 5, Advanced Formatting

I felt mostly ambivalent about the beginning of this chapter. To me, if you want text aligned a certain way, or want to create proper lists and use other more advanced document formatting features, you should use a word processor. Adding whitespace to my text files for alignment’s sake doesn’t strike me as a good idea.

The second part covering formatting pertinent to coding was much more useful. I’ve used things like autoindent and formatting of pasted code for a while now, and they are quite essential to sanity. Great coverage for those new to these features. Integration of external formatting tools such as Tidy is also described.

Chapter 6, Basic Vim Scripting

“Now we get to it” was my feeling starting this chapter. There’s some very helpful stuff in here, but it’s strangely organized and should have been moved or at least mentioned earlier in the book to provide the most benefit. Some critically handy items: explanation of Vim configuration file directories, how to properly override layers of configuration, Vimball installation, syntax highlighting and colorscheme creation. Then… comes an introductory language tutorial. Honestly, it’s pretty dull and mostly elementary. I would have rather this been moved to an appendix mentioned early in the book. It does provide broad coverage of the basics one would need to start writing Vim scripts.

Chapter 7, Extended Vim Scripting

Everything you need to know about actually writing Vim scripts: suggested code format, function structure, adding key mappings, detecting OS and Vim version, debugging and distributing scripts, documentation, even integrating programming languages like Python and Ruby! This is certainly a great reference to get started with Vim scripting. Schulz also gives an example script wrapping up a number of these points, providing something like a Vim scripter’s “Hello World”.

Appendix A, Vim Can Do Everything

Examples of the many and varied things you can do with Vim, such as Tweet and play Tetris. Provides amusement, but not a lot of value. The section on Vim as an IDE should really have been expanded to an entire chapter or appendix considering the likely audience for this book.

Appendix B, Vim Configuration Alternatives

A number of tips on how to keep your Vim configuration organized. Spends time explaining a system of scripts created by Jos van RisWick that allows the user to make changes to their configuration from GUI menus and make them permanent, instead of editing config files. I had never heard of this and found it quite interesting.

The last few pages deal with storing your Vim configuration online. While I whole-heartedly agree with the intent, the particular manifestation presented is rather terrible. Using this setup, the user’s .vimrc would be downloaded every time Vim is opened. Then it still has to be parsed, which for heavily customized setups can already take a moment. Not the best when you’re looking for a lean editor. And what about the legion of scripts and other supporting files outside .vimrc? I think not. My approach to the problem is to keep all of my Vim configuration files inside an SCM system, checking it out on each system the first time I want to use Vim.

Issues

• Confused structure: Chapters 6 and 7 felt rather strangely divided in themselves, and I felt most of the content should have been provided earlier. Having a better knowledge of the syntax and nature of ex commands/Vim scripting would have made the content throughout the book more understandable. I felt I was mostly taking the syntax in the code provided for granted, without feeling comfortable enough about all the components that I could have written something of my own design.
• Typos: The text would have greatly benefited from another round of copy-editing. Most of the errors weren’t very distracting. There were, however, a number of typos in the code provided that were more problematic, especially for a work on an editor that can be rather picky about commands. For a first edition, these aren’t overly numerous.
• Lack of color diagrams: I’m all for using black and white images when they will suffice, but when providing illustrations of *color* highlighting files, images without color are downright useless! I was rather surprised at this oversight, first appearing in chapter 2, then in multiple places in chapter 6. They could have at least linked to color images online.

Conclusion

All in all this was a very useful treasure trove of tips. Some I’ve already added to my repertoire, and more I’m trying to understand better. In fact, I learned so much from it that I decided I will likely need to drop most of my current Vim config and remake it cleanly. I know I’m not alone in collecting bits and pieces of configuration for this wonderful editor, in a fashion occasionally reminiscent of a deranged hermit crab. Now that I have a more solid understanding I can rebuild it quickly and effectively.

In summary, I agree with Brian Carper:

The best you can hope for in a book is a broad outline to point the way toward features that you didn’t know much about. Hacking Vim 7.2 achieves this goal.

Enter Readability:

Readability is a simple tool that makes reading on the Web more enjoyable by removing the clutter around what you’re reading.

Huzzah! Simply go to the Readability site and try out the different options for how you want rendered pages to look. You can change things like font size and style, as well as margin. You can even make the colors inverted if you prefer a dark background. Then drag the indicated button to your bookmark toolbar and click it whenever you are on a page you want to be more readable. After discovering Readability, I’ve been using it every day.

I was interested to see that the new version of Safari released today (version 5) added a feature called Safari Reader. This serves the same purpose as Readability, just built into the browser’s address bar. It doesn’t appear that Safari Reader allows you to set more advanced options like the appearance of the font, but it does have the added benefit of remembering your zoom level.

After some consulting with good friend Google, I gathered that this same behavior was witnessed by a number of people (blog, forums). Older MacBooks upgrading to Snow Leopard sometimes apparently fail to engage the backlight.

Thankfully, there was a lo-tech and amusing workaround: a flashlight!

With the assistance of this external lighting I could see enough of the install screens to complete the process. It’s easiest to see holding the light not too close to the screen and at a steep angle to avoid glare. Not that anything really helps, this is an annoying and slow process. Finding the mouse cursor by having to scan the whole screen bit by bit, not so fun.

Joyfully, after the final reboot, the backlight returned from its slumber:

Fail indeed. Here’s the fix:

This increases a particular kernel parameter to allow postgres enough memory to start. Yay for poor defaults…

Yeah, that’s the actual URL that works. Pretty sweet.

Anyway, next you make it executable, run it, enter creds:

Then the goodies appear:

Oh, that query_cache_limit is tiny! At this point, no changes have been made to your setup. But at a glance you get helpful stats, validate that changes you’ve decided to make are in place, and get alerted to potential improvements.

If I knew Perl now and refreshed my MySQL tuning knowledge, I’d love to help as a maintainer of this lovely script. Maybe sometime soon…

This loops through all the .jpg images in the directory and converts them to a PDF file of the same name. Once that is done they are all combined into FINAL_NAME.pdf by pdftk, a handy PDF utility. The temp dir business is there to make the temp PDF file removal easier.

You have a file already tracked in your git repository, but you don’t want future modifications to it to be tracked.

A perfect example of this is the DB config file for Rails projects (config/database.yml). You’ll probably want to track this to keep the production and staging configuration stored and consistent. But it’s quite likely that individual development configurations will be different. Having a commit for each developer adding their own local configuration and thereby polluting it for others when they push is just silly.

Solution

The fun details

I had initially thought that git-ignore was the thing to use for this. That was wrong: git-ignore’s purpose is to allow ignoring of untracked files. In the case of the Rails DB conf example above, the file is already tracked. It has the instance information that needs to be shared and now we want to add local development details. Since it’s tracked, any modifications are going to be picked up by git-status. When you do a commit including changes to tracked files listed in .gitignore, the changes do indeed get pushed, which isn’t what we want.

When looking around for solutions to this, I saw “git rm –cached FILENAME” suggested as a way to stop tracking currently tracked files. And it does this, but it also deletes the file in the commit, which isn’t what we want either.

git update-index allows you to alter your staging area more manually than usual. With it you can perform a wide range of operations not otherwise possible through standard commands. Continuing with the Rails DB conf example, here’s what the workflow would look like:

1. config/database.yml with permanent instance info is added, committed, and pushed
2. Application code is pulled down by new developer
3. Local modifications made to database.yml for new developer’s local DB setup
4. git update-index –assume-unchanged config/database.yml

Thereafter, the changes to conf/database.yml won’t appear in status or get committed. You can continue making and committing other changes, going about business as usual. But any changes to config/database.yml won’t ever appear.

In case you decide you do want to start tracking changes on a file previously ignored, just run:

Thereafter changes to FILENAME will appear in git-status.

Gotcha

The annoying part about this solution is that it has to be run against every new checkout of the repo with the files whose modifications should not be tracked. The command sets a particular bit per checkout, and it’s not passed on to new checkouts.

Just as I was finishing up some fact checking for this write-up, I came across Jesper Rønn-Jensen’s post on this exact topic. If only it had come up in my initial searches! Here’s hoping the next searcher has better luck.