Microsoft Excel Rules The World

Steven NgDaily Debug BlogLeave a Comment

Having Excel skills is like winning the jackpot in the lottery. When other people find out what you’ve got, they come out of the woodwork and ask for your help. Ira Iosebasshvili’s article on Excel skills is a pretty funny take (to me at least) on the reality of people knowing that you have a skill they don’t have, whether it be Excel, or how to set up a wifi network.

If you’re one of those people who has built up a base of useful technical knowledge, that first paragraph of the article pretty much sums things up:

“People would come up to me and say, ‘Hey, I hear you’re the Excel guy,’ ” said the 37-year-old metrics consultant from Oak Brook, Ill. Mr. Kalelkar said he has become “a little more passive-aggressive,” warning help-seekers, “Don’t come to me, go to Google first.”

Which, of course, reminds me of one of my favorite XKCD comics of all time:

Now having said all of that, let me go on the record as saying that in my books, Excel is one of the most important and useful applications that has ever been made. I spent a year or so at a client writing Excel VBA scripts to make it do some absolutely crazy stuff, and it was a blast. With the right skills, you can do anything with Excel, including making beautiful art or even a game.

Even now, I see vanilla Excel (vanilla as in no add-ins, macros or scripts) as the ideal business intelligence analysis tool. And admittedly, that was part of my motivation for writing Knodeo Extrata.

I’ve seen a lot of people use Excel in all types of organizations, and there was generally one common thread. All the advanced Excel reporting and analysis was done with linked worksheets that were exported from some black box enterprise system. There’s no argument that it’s not the most efficient way of doing things, but there’s also no argument that it’s a very effective way of doing things, especially when you consider the fact that a lot of people know Excel and can usually find a way to coax Excel into doing what they want it to.

Just because there are better tools than Excel for doing something doesn’t mean people want to change their habits to accomplish that goal. Learning a new tool takes time (and money), and people are already busy with their everyday work. A lot of people just don’t want to get out of their comfort zone, and that’s just fine.

If I had my way, more companies would find ways of making solutions that line up with the existing skills and habits of their users. And when it comes to reporting and analysis, that means fitting in with how people use Microsoft Excel. As it relates to Knodeo Extrata, I just wanted people to be able to get their non-Excel data into an Excel file so they can create reports on them by simply linking the worksheets.

Have we accomplished that? I don’t know for sure, but how about you download the free demo, and let us know what you think?

My Big List of My Favorite Tools For Windows (mostly)

Steven NgDaily Debug BlogLeave a Comment

Overview

I was planning to write a post about how I work, but I realized in the end it was mostly going to be a list of tools, services, and hardware. As I started compiling my list, I realized that it’s a pretty large one.

Most of my list is Windows-centric, but there may be some nuggets for other people, including people who are switching from Mac. Keep in mind that your mileage will vary. Just because I like a tool doesn’t mean you’ll instantly like it too.

Standing On The Shoulders of Giants

Before I get into the big list, I do have to give credit where credit is due. I didn’t come up with this list in a vacuum.

When I switched from Mac to Windows (again) in 2014, Scott Hanselman’s Ultimate Developer and Power Users Tool List for Windows was absolutely indispensable to me. And while Hanselman’s list hasn’t been updated since 2014, it is still just as relevant and useful today.

Hanselman’s list served as the foundation of the suite of software and services that I use myself to stay productive. Be sure to check out Hanselman’s list, as you may find something very useful absent from my list.

Having said that, I use a lot of stuff not on Hanselman’s list, so I’ve decided to present my very own list here.

The List

Nobody paid for placement on my list, and the items I think are worth paying for are items I bought myself.

Instead of doing new posts for new tools, I’ll try to continuously update this list, so check it for updates every now and then.

Without further ado, here’s the list. By the way, the categories are in no particular order. I hope there’s a good discovery in there for you.

The First Thing I Do On Every New Machine

The absolute first thing that I do on every new machine is go to Ninite, pick the apps I want, and do a single bulk install. The great thing about Ninite is that you get a single installation file and it will install all your chosen software without any interruptions. It is such a huge time saver, and I think every Windows user should be using it when they get a new computer or reinstall the operating system.

Essential System Tweaks, or Stuff That I Wish Was Built Into Windows

EarTrumpet is a utility that lets you set audio volume by application. Not all apps play back audio at the same level (TuneIn Radio, I’m looking at you), so you can dial back the volume of some apps without missing the sounds from others.

Classic Explorer/Shell is most popular for letting you get the old Windows Start menu from yesteryear. I don’t really use the start menu (I got into the habit of using Spotlight on the Mac, so I prefer to hit the start button and type the first couple of letters of the app I want to launch), but I think the File Explorer in Windows XP is the best version ever. Classic Explorer lets you make the File Explorer in Windows 10 look and behave like the one in XP.

Speaking of Spotlight, I like to launch apps via the keyboard. The Windows key works for simple cases, but fails when you have multiple applications with similar names or misspell an item. Also, some of Windows’ guesses while it autocompletes can be incredibly inane. There are some alternatives you can consider like Launchy, Wox, and Listary. I’ve tried a few of them (and even bought Listary Pro). They are quite powerful, but I always go back to the Windows key in spite of its failings because it’s a dedicated key on the keyboard that I have muscle memory for.

While I never really used it on Mac, the one thing I hear Mac users groaning about when confronted with the idea of switching to Windows is their crack-like dependency on Quick Look. Well, help is here. QuickLook duplicates that functionality on Windows. I have to admit, it is handy for previewing PDF files in a pinch.

Whether you have a bunch of giant monitors or a tiny laptop monitor, the one display management app I cannot live without is DisplayFusion. It’s not free or necessarily cheap, but it does get heavily discounted at Steam periodically. Steam, by the way, is the best place to buy this software as it lets you install it on any machine you have (with Steam installed) and get free upgrades. Trust me, it’s money well spent.

AutoHotkey is a great tool for power users. At its simplest, it’s a great tool for text expansion, at it’s most powerful, it can be scripted to perform automated tasks on your system. There is one potentially big gotcha. AutoHotkey has been known to be detected as a false positive for malicious software by Chrome and Windows Defender. This is because it’s doing some low level stuff for its functionality. To be on the safe side, I recommend that you check the binary at VirusTotal to be sure that the version you downloaded is safe to install on your machine.

If you jump to a lot of different folders on your machine like I do, you’ll probably find DirectFolders to be indispensable. It’s freemium software, so you don’t have to pay unless you want the added features, but I found it very worthwhile to get the added features. The great thing about DirectFolders is that it works in the File Explorer and in File dialogs as well.

Sets are coming to Windows in the future, but at the time of writing, they’re still months away. Sets basically lets you group some related applications into a single tabbed window. How is this even useful? Let’s say you work on multiple projects on any given day, and each of those projects includes a text editor, browser window and terminal window. You can have a tabbed set for each of your projects so you don’t have to jump around and figure out which window to jump to. This is particularly useful if you’re working with limited screen real estate, such as on a laptop. I use an app called Groupy to get the Sets feature now. It’s not free (but it’s inexpensive), but you can download a trial to see if it works for you. I like this app a lot.

Ever wish you could force your screen(s) to sleep? Well, ScreenOff is a nifty app that lets you do that. To trigger it, I use a hotkey to launch the application. How this isn’t a built-in feature of Windows is beyond me. Speaking of hotkeys, it’s worth mentioning that I use a SteelSeries Apex gaming keyboard as my work keyboard. It adds 22 additional keys that you can customize to perform whatever actions you want. It is an incredible productivity booster.

Windows laptops can be funny. Depending on your laptop vendor, they may not always place the keys where you want them, and there may or may not be caps or num lock indicators. My Asus Zenbook only has an End key when the NumLock is off. That is just damned stupid. Sharpkeys is an indispensable tool for remapping keys. It can’t remap all keys, but it can do most of them. My Zenbook also has an LED for the Caps Lock, but not for the Num Lock (come on, Asus). For that, I use an app called Keyboard Indicator, which adds an icon to your taskbar.

Screen grabbing is an underrated task, and if the out-of-the-box Alt-PrtScn ain’t doing it for you, then you need something a little more powerful. I’ve been using Greenshot for quite some time. The thing I like about it is that it has its own editor, which is great for people who miss Skitch. If you don’t like Greenshot, I have heard a lot of good things about ShareX and Snipaste too, so you might want to check those out.

Screen recording should be a first class feature in any operating system. Camtasia used to be the app to get, but it’s pricey. There is a built in recorder in Windows 10 in the Xbox app, but I prefer OBS Studio. It’s got a dense user interface, but it’s powerful as heck. If you’re looking to record your screen and save as an animated GIF, ScreenToGif is worth checking out.

Do you shoot RAW with your camera? Unlike with MacOS, RAW support is not baked into the operating system. Unfortunately it’s not common knowledge, but Microsoft provides RAW support as an extension that you can get from the Microsoft App Store.

Stuff I Use Throughout My Working Day

Chrome is easily my most heavily used application on a computer. Yes, it’s a resource hog and it’s probably sending all my browsing info to Google, but I use it nonetheless.

Atom is probably the second most heavily used application on my computer, as I spend most of my time looking at or editing code. It wasn’t always Atom- my go to editor until very recently was Sublime Text, which I still use as my secondary text editor. If you’re wondering why I switched, you can read about it a previous blog post.

When I switched back to Windows, I had become very accustomed to using Terminal on Mac. I never had fond memories of the Windows Command (aka DOS) prompt. Thanks to Scott Hanselman’s list, I found Cmder.  For the most part, Cmder made my transition seamless. There are other alternatives, including ConEmu, which Cmder is built on, but I’ve been happy with Cmder and found no need to switch. Unlike the old days, there’s no need to install Putty or Cygwin.

If you care about your password security, you’ve got to have a password manager. I’ve been using 1Password since its first days on the Mac, and I’ve moved from the paid app to their paid subscription. It’s not perfect, but I rely on it heavily for managing my passwords and other private information. If you’re not willing or interested in pay for 1Password, there are free alternatives like Keepass.

For work based communications, we primarily use Slack. We outgrew the free edition and now have a paid subscription, but it is a great tool. We also continue to use Skype and Skype for Business, but we find the channels in Slack help reduce a lot of the noise. It has definitely reduced the amount of junk emails we send to each other throughout the day. Microsoft does have a Slack knockoff, but if you’re already using a tool, inertia often makes it hard to switch.

Office 365 is not cheap, but it’s cheaper than running your own Exchange server and buying the latest upgrades for the Office software. While I get that there are cheaper options like Google’s GSuite or LibreOffice, there is something to be said about interoperability. Every app that is supposedly compatible with Office never seems to render perfectly when the file is opened in Office. And while I hate the 365 web interface and that damned Ribbon, I still consider Word, Excel, PowerPoint and Access to be among the most powerful tools in my toolbox.

PDF Handling

PDFs are ubiquitous, but nobody wants to pay for Adobe Acrobat. For reading PDFs, I really like Sumatra PDF. For creating PDFs, I use PDFCreator (freemium). Finally, for manipulations like merging and splitting, I like PDFSAM (also freemium).

Note Taking

I used to be hard core Evernote user right back into its early days, but since they changed their license model, I stopped using it. These days, for work related stuff, I use OneNote from our Office 365 subscription, whose interface I have love/hate relationship with, and for personal stuff, I use a folder full of Markdown files in Dropbox, which seems to be working well. On Windows, I use Sublime Text to manage my notes files.

Editors

Text/Code

As mentioned earlier, Atom and Sublime Text are my go-to text editors. I would add that Notepad++ is also quite good. I used to be a heavy Notepad++ user in the Windows XP days, but I rarely use it today.

If you’ve got a huge text file you want to edit, not many editors are up to the task. The ones that I know of are: PilotEdit (which has a free Lite version), EmEditor and EditPad Pro. If you work on large text files regularly, those editors will pay themselves off pretty quickly in terms of the headaches and time they’ll save you.

Design

My preferred PhotoShop alternative is Affinity Photo. It is reasonably priced compared to PhotoShop and its user interface is top notch. I still occasionally use the free Paint.NET to edit raster images though.

My preferred Illustrator alternative is Affinity Designer. Like its sibling, it’s also got a great user interface and is reasonably priced. Affinity Designer was the first app on Mac or Windows that I found that didn’t mangle any Illustrator file of significant complexity. If you are looking for a free vector editor, Inkscape is a serviceable alternative.

My preferred Lightroom alternative is DxO Photolab. I also purchased DxO Viewpoint and DxO FilmPack. For me, the main appeal of PhotoLab are its lens corrections and its noise reduction algorithms. Their automatic adjustments are also pretty good.

If you write documentation or run a web site, one of the most annoying repetitive tasks to do is image resizing. Image Resizer for Windows lets you do it by right clicking an image in File Explorer.

If you need to edit or create a font, TypeTool is an okay tool for that. I bought the app, but I find the licensing model to be a little user hostile and stuck in the 1990s. BirdFont looks like a decent free alternative, but I have not had time to try it out yet. If you like creating handwriting fonts, you might want to check out Microsoft Font Maker.

If you need to create or edit icons, Greenfish Icon Editor is a good free option.

Audio and Video

When I want to listen to my music library while working, I use Musicbee.

For video playback, I like Pot Player.

If I need to recompress video, Handbrake does the job.

Need to grab a video off of Youtube or another site? 4K Video Downloader works pretty well for that.

For creating and editing audio files, Audacity is basically it for me.

For creating and editing video files, the answer is a little trickier. If you can still manage to find Windows Movie Maker, it’s great for quick and dirty edits. There is no good low cost equivalent to Apple’s iMovie on Windows. I’ve bought and tried several apps, including those from Cyberlink and Corel (Corel is where good apps go to die), and have had middling results. Premiere Elements is an option, but then you’ve got to deal with Adobe’s customer unfriendly activation schemes, which I am not willing to do. So unfortunately, I don’t have a fantastic video editing pick for you right now.

Communications Tools

If you don’t like to be distracted by your phone when receiving instant messages, YakYak (for Google Hangouts), Whatsapp for desktop and Android Messages for the Web are very handy.

For standalone mail clients, Easymail for Gmail and Postbox are pretty good.

Development

Most of the work I do is in Node, Ruby and SQL, so this list may be less useful for you depending on the type of development that you do. As mentioned earlier, Atom and Cmder are important parts of my development toolbox, but there are other important tools that I use.

Github (for public repos) and Bitbucket (for private repos) are our go-to tools for source control. If you want to self-host, you can also install Gitea or Gogs on a local or cloud server. If you prefer to use a gui for Git, SourceTree is a pretty good one.

Because Cmder satisfies my terminal requirements so well, I still haven’t had a chance to try the Windows Subsystem for Linux. I do think that it is a very appealing feature for Mac expats though.

If you’re setting up a web server with SSL, I think it’s a no-brainer these days to use Let’s Encrypt with Certbot.

Testing Stripe webhooks can be the bane of my existence. I really wish Stripe would offer better tools for local development. Having said that, ngrok is a wonderful tool for letting you receive webhooks on a developer machine behind a firewall. It’s a freemium service, and the prices are more than reasonable.

For testing REST APIs, Postman is very handy. Tooting our own horn, I also use Knodeo Extrata for testing APIs. Part of my rationale for building Extrata was to get API data from Stripe because I found their admin interface so frustrating to use when troubleshooting.

While Atom provides me with diff comparison tools within my text editor, Winmerge is a great standalone tool for diff analysis.

For virtualization, I use VMWare Workstation Player when I need to run a guest operating system that has a GUI. I used to recommend VirtualBox as well, but due to some changes to their licensing, I can no longer recommend it.

For bug tracking, I like Manuscript (formerly known as Fogbugz). They used to have a free startup account for up to two users, but I’m not sure if they still do.

Documentation

If you need to document a lot of screens quickly, the Steps Recorder in Windows (built in to all recent versions) is a great time saver. It’s not without a catch though, it saves the recording in an MHT file in a ZIP file. To get your screenshots out of the MHT file, I suggest you use extractMHT, which is free.

Databases

If you have to query any relational databases, DBVisualizer is pretty good. You can use it free, although there are limitations on some features. I use an add-on in Atom these days, so my dependency on DBVisualizer isn’t what it used to be. Some other querying tools worth checking out are Query Express and Linqpad.

If you need to store data easily without a server, Microsoft Access is still great for that. If you don’t have an Office 365 subscription that offers it, LibreOffice has a similar tool, although the user interface is mediocre at best.

In terms of servers, you can’t go wrong with Postgresql, MySQL or SQL Server Express. My first choice for a long time has been Postgresql.

Need to pull or move data? Pentaho, Scriptella and SQLines are pretty good for that. Shameless promo- when I need to export relational data to Excel, I also use Knodeo Extrata for that too.

Backup and Storage

I use Dropbox, Google Drive and Office 365 to store files in the cloud. If I’m being honest, however, I can’t stand Office 365’s OneDrive for business. I only use it because I have to.

If you’re looking to host your own Dropbox type of service, OwnCloud is pretty good. A lot of NASes that can run apps support it. I use it to sync photos across my various computers.

If you’re looking to store backups in the cloud, Backblaze is a pretty good option. If you’re looking for flexibility in how you store your backups, Backblaze B2 might be an even better option.

For backup software itself, I rely heavily on Windows File History, which is kinda like Time Machine on the Mac, and Duplicati. Duplicati, by the way, also supports writing to Backblaze B2 as well.

For drive imaging, I use Acronis. It’s not free or cheap, but you don’t appreciate the value of it until you really need it. The one downside is that the Windows software likes to upsell you newer versions, which is annoying for something you paid for. Another good imaging tool is Macrium Reflect, which has a pretty useful free version.

In terms of offline storage, I use a BluRay burner and burn with Cyberlink Power2Go. I’m not a huge fan of Power2Go, but it came with my burner and it works.

Antivirus and Security

For me, Windows Defender is the only active antivirus I have on my machine. When I’m feeling very paranoid, I check my files at VirusTotal, which will analyze them with several antivirus applications.

If I’m using public wifi and want some protection, I use a VPN. For that, I like Private Internet Access.

Diagnostic Utilities

When I’m troubleshooting more complicated problems, my two go to applications are Process Explorer and Process Monitor.

Network Utilities

If you’re trying to map out wifi signal strength at your home or office, Netspot is pretty good for that. Another one worth considering is WiFi Survey, which is available from the Windows store. I found WiFi Survey to be a little easier to use when mapping the signal strength of my access points at my house, but the app is not free.

If you are wondering who’s lurking on your network, Nmap for Windows is a good utility for that.

If you need to test your Internet speed, fast.com is a nice clean way to do that. If you want to know information about your IP and location, Private Internet Access’s What’s My IP Address page is a good tool.

Need to test some DNS entries? Are checking if a DNS text value has propagated so you can get your Let’s Encrypt certificate working? MXToolbox is a great tool for that.

Disk Utilities

If your disk is cramped for space and you want to know what’s eating it all up, Windirstat is a great way to visualize it. While Windirstat is one of the OGs for space analysis on Windows, here are some newer alternatives like Space Sniffer and Wiztree that are worth checking out too.

Need to fix your partitions and Windows Disk Management isn’t being too helpful? MiniTool Partition Wizard has a free version that will work in a pinch.

Want to replace spaces with underscores in a thousand files? Want to add a prefix to them too? Advanced Renamer is a great batch renaming utility.

Newer versions of Windows don’t let you format in FAT32 any more. FAT32 Formatter for Windows lets you format FAT32 disks when exFAT is going to give you compatibility problems.

Need to write a raw disk image (like a Raspberry Pi distribution) to an SD card? Win32DiskImager is probably the tool you need.

Need to make a bootable USB key from a Linux ISO? Rufus is great for that.

File locked? Ok this problem is a little complex, and not always solvable, but FileAssassin can help some of the times. If that doesn’t work, you might have to chase the problem down with ProcessExplorer or reboot.

Other Utilities

Got a Windows Home machine that you want to remote control? Windows Home doesn’t offer Remote Desktop out of the box, so TightVNC is a great substitute for that.

For compression, 7zip is my favorite tool. Not much more to say than that.

If you’ve got multiple computers (regardless of operating system) on your desk and you want to use one keyboard and mouse to control them all, Synergy is great for that.

While I don’t use a GUI for file transfers very often, Cyberduck is the tool when I need to.

If you need to get a Linux distribution via BitTorrent, qBittorrent is a good client for that.

Want to use a gamepad or joystick as an input device? I know this seems counterintuitive, but a gamepad can be a great device for bulk operations that would otherwise require a lot of repetitive keystrokes. For example, I’ve used a gamepad to to rate thousands of photos. Joy2Key and AntiMicro are great tools that can map your gamepad inputs into keystrokes.

Browsers, Extensions and Web Sites

Chrome is my go-to browser, but sometimes you still need to use Firefox, Edge or Internet Explorer if you’ve got older devices that have Java user interfaces, etc.

I like Chrome for a number of reasons, but one of the big ones is the extension ecosystem. A few that I like are:

  • UMatrix, which lets you limit what a site can run in your browser (also available on Firefox)
  • Copy As Markdown, which lets you copy a link in one or more tabs as Markdown
  • Screencastify (freemium), which lets you record activity in a web site. The one thing I like about Screencastify over a general screen recorder is that it records the browser viewport without any of the browser chrome and you don’t have to worry about resetting a recording area if you move the window.
  • Incognito Filter, which forces specific sites to open up in a new incognito window.
  • Pinterest, which I use for clipping images I might want to refer to later

Some sites and services I use all the time:

  • Bing, Google and DuckDuckGo for searches. Google used to be my only search destination, but these days, there’s so much garbage that trickles up to the top of search results that I need to use more than one
  • Assign It To Me (self promotion alert!) for managing projects
  • Hacker News is how I keep up to date with technology
  • Passmark for CPU and GPU benchmarks.
  • ImgFlip for sending sassy self-generated memes
  • Feedly for aggregating all my RSS feeds
  • Reddit for specialized technical subject area discussions… and cute dog photos

Odds and Ends

Here are a few things that I use that aren’t so easily categorized.

If you are trying to create a complex hierarchical document, Treesheets is pretty fantastic.

Are you a Canadian corporation and do your own taxes? Futuretax is pretty good and one of the lowest cost options.

For cloud VPS server hosting, we like to use OVH because they have a data center in Canada and their prices are pretty good.

If you are setting up non-proprietary IP security cams and want a decent and free NVR, Zoneminder is pretty good. The UI is a little dense, but the motion detection zones are better than many of the other options. It only runs on Linux though.

 

Giving Atom Another Go

Steven NgCodingLeave a Comment

A little bit of history

Since switching back to Windows, my go-to text editor has been Sublime Text (https://www.sublimetext.com/). While I purchased the license back when I was a Mac user, Panic’s Coda (https://www.panic.com/coda/) was still my go-to editor.

Don’t get me wrong, Sublime is easily one of the best text editors on any platform, and it’s inexpensive for something I basically use 8 hours or more a day. It’s extensible and fast.

I tried Atom (https://atom.io/) a few years ago, and it’s similar to Sublime in many ways (and free too!). But it was slow to load, and it just wasn’t “there” for me yet. It is developed by Github, yet at the time, there was no good built-in Git or Github support.

I saw a headline announcing a recent release of Atom, and I was curious, so I went to the site to check it out. I know that over the years, Atom has gotten a lot faster and more powerful, but inertia is a powerful thing. When something works well for you, you’re simply not motivated to make a change.

But this time, I decided to give Atom another go, and it was eye-opening.

Clearing some air first

I get it. Atom isn’t for everyone, and that’s okay. This post is just about finding a tool to fit how I work. So obviously, your mileage will vary.

I’m also aware that there are plenty of other great text editors like Visual Studio Code (which I tried in its earliest days but never looked at again), Notepad++ (which used to be my go to Windows editor many years ago), VIM, EMACs or whatever else you can come up with. While I’m going to explain why I’m personally switching to Atom, I’m also not here to convince you to change to Atom.

How I Work

I have both a desktop and a laptop. I do most of my work on a desktop, because I have four monitors (2 x 4K, 2 x 21:9 1080p in case you’re wondering) that provide me plenty of real estate for coding and testing.

My laptop is mainly for working on the go, but given that it’s one small 15.6″ screen, it’s not the best thing, especially when you have to juggle windows constantly. I’m not one of those guys who can work happily with a 13″ screen. When I’m multitasking on a laptop, I quickly feel my blood pressure rising from the lack of screen real estate. I often use a USB powered external monitor to alleviate some of that stress.

I am, unabashedly, a window and tab hoarder. A really bad one at that. I don’t close windows/tabs until it’s absolutely necessary, so window/tab proliferation is a problem for me. I don’t expect this behavior to change, so this behavior absolutely shapes how I choose software and tools.

So depending on what I’m working on, I generally need access to the following windows/applications:

  1. Code Editor (multi-pane)
  2. Database Querying Tool
  3. Multiple terminal windows (for logging, builds, asset compilation)
  4. Source Control GUI like Sourcetree (I know this one’s going to be controversial with hard core developers, but I’m not judging you, so please don’t judge me)
  5. Browser Window with Developer Tools Open (for testing)

That’s at least five windows (some with tabs), not counting other applications I have running, like e-mail and messaging.

Discounting the browser window, I have four applications open with a minimum of one window open when I’m coding on a project. Factoring the window proliferation habit I mentioned above, it can be a real pain jumping around when I’m doing stuff. It’s even more painful when I’m doing it on a laptop.

There are also some additional tasks that I have to jump out of my text editor and perform in a browser.  My primary coding languages are Coffeescript, Jade (aka Pug), Haml and SASS, so I’m often converting Javascript to Coffeescript (http://js2.coffee/), HTML to Jade (http://www.html2jade.org/) and Haml (http://html2haml.herokuapp.com/), and less frequently, CSS to SASS (http://css2sass.herokuapp.com/). Because I’m jumping around so much, I often leave garbage browser tabs open for these conversions.

Revisiting Atom

In taking a look at Atom, I realized quickly that installing a few packages would allow me to merge my four applications into one. Out of the box, Atom supports Git/Github, and it pretty much does everything that I use Sourcetree for.

For querying relational databases, the data-atom package (https://atom.io/packages/data-atom) can replace a database querying tool like DBVisualizer.

And to replace my terminal (I use Cmder (http://cmder.net/)), the PlatformIO IDE Terminal (https://atom.io/packages/platformio-ide-terminal) lets me have multiple consoles running within Atom. And the best part? The package lets me use Cmder as my terminal within Atom. Another bonus is that the terminal opens up to the folder location of the project I’m working on.

In the case of Git, data-atom and PlatformIO IDE Terminal, they’re all hideable panes in the Atom project window. You basically have all the functionality you need in one place without bouncing around between screens or windows.

With the exception of CSS to SASS conversion1 , there are packages for Atom that will do my code conversions without having to open a browser window:

  • Javascript to Coffeescript: js2coffee (https://atom.io/packages/js2coffee)
  • HTML to Haml: Html2Haml (https://atom.io/packages/html2haml)
  • HTML to Jade: html2jade-plus (https://atom.io/packages/html2jade-plus)

All of this makes Atom a compelling alternative to what I was doing before. And because it’s all done in the same window, it makes working on a laptop a lot less frustrating too.

Making The Switch

For the most part, because Atom is so similar to Sublime Text, making the switch is fast and painless. Most of the key bindings are the same, so there’s really not much new to learn.

Does this mean I’m ditching Subime Text? Far from it. Sublime Text is still my default text editor for many types of files, and it’s awesome for that. For development projects, however, I find myself much more productive in Atom than I am in Sublime, mainly because of the packages that I use.

Does this mean you should switch to Atom too? Nope. But if you’re not happy with how your tools fit in with your development habits and process, it’s definitely worthwhile to see if Atom (or another editor) and its package ecosystem can help in that regard. It did for me, and maybe it can for you too.

Footnotes

1. At the time of writing

How to Save Data Locally in Electron

Steven NgCoding, Daily Debug BlogLeave a Comment

So you’ve hitched yourself on the Electron bandwagon, and you’ve started coding, and now you realize you want to save data locally in Electron. Yes, you know how to code and you can do whatever you want, but you obviously want to do the right thing for your application.

While I’ve been admiring Electron from afar for quite some time, I’ve finally dipped my toe into the Electron waters and encountered the same dilemma. After plenty of Binging and Googling, I found a lot of Stack Overflow pages and blog entries that pointed out a lot of popular libraries and NPMs to use, but less so on how to choose the best approach for my use case.

Save Data Locally in Electron

Save data locally in Electron

This article is about how to save data locally in Electron. So I’m not going to talk about solutions for client-server scenarios where you want to save data to a database server or to the cloud. This article is also about application data as opposed to content. By application data, I’m talking about the consolidated data that you expect only your application to read and write from, even though that may include user generated content. I’m not talking about self-contained “content” files like Word, Excel or JPEG files.

Also, let me get this out of the way now, I’m not going to prescribe any particular library or technique in this entry. My goal here is to help you figure out what your requirements are and move on from there.

There is no panacea with respect to how you save Electron data. Just do what is best for your particular application and your programming style and capabilities. What works for someone else’s application might not work for you.

In the end, you need to make a choice that’s effective for your application now, and ideally one that can grow with your application.

First Things First

What are you trying to accomplish? You’re probably inclined to say “I’m trying to save data locally in Electron, you fool!”

Ok, fair enough. But let’s figure some things out first. Let’s ask ourselves some pointed questions:

  • How much data do you think your application will generate in the short term? What about the long term?
  • How complicated is your data? Do you have a lot of nested relationships?
  • Do you want the datafile to be easily manipulated by users outside of the application?
  • Does the data need to be human readable?
  • Will the data need to be portable?
  • Does the data need to be readable by other applications while your Electron application is running? 1

I’m not going to answer each question directly, but you should always keep these questions in the back of your mind when evaluating the appropriateness of a save solution for your Electron application.

Two Key Decisions

In my mind, there are two decision points that you need to consider before choosing a final approach.

Electron’s Built In Storage or File Based Storage

Electron’s basically a browser on steroids that can be packaged up as an application. Because Electron has Chromium (Chrome) at its heart, you have most of the common HTML5 storage options at your disposal. That includes cookies, LocalStorage and IndexedDB. The catch, however, is that this data is stored in the user data folder and it’s not easily viewed or manipulated with your code editor. Because of that the data is also not portable in the same way a normal data file is. Depending on your application’s use case, this may be perfectly fine.

There are also some implications related to backing up the data. Time Machine or Windows File History can back the Electron data up, but it will seem like a black box to your users, and depending on who your users are, this can be a potential red flag.

If Electron’s built in storage isn’t suitable for your use case, however, then you want to look at file based options, such as a flat file like JSON, or an embedded database like SQLite.

Asynchronous or Synchronous

If you’re a dyed-in-the-wool Javascript fiend, this is an easy choice. Unless you have a specific reason not to, you’re going to go with an asynchronous solution.

If you’re coming from another language like Ruby, however, you may want the same degree of I/O predictability provided by the language you’re already used to. While the benefit of asynchronous data stores is that they’re non-blocking, sometimes blocking is a good thing, as you may not want to be forced to use callbacks or promises to avoid out of order read/write operations. If you’re not accustomed to using callbacks and promises, asynchronous I/O has the potential to destroy your sanity.

While most saving APIs for Electron are asynchronous, there are some options for those who prefer synchronous APIs.

Electron’s Built-In Storage

The methods available out of the box with Electron are tried and true, and a known quantity. Here’s what you have at your disposal:

  • Cookies
  • Session Storage
  • Local Storage
  • Web SQL
  • Indexed DB

Cookies, Session Storage and Local Storage

Cookes, session storage and local storage aren’t very useful for saving large amounts of data, but they are great for storing settings and application state. They are, however, not great for handling complex application data. If your application settings don’t need to be portable, then you will probably want to forgo these options.

Web SQL

WebSQL is basically SQLite. Not horrible in itself, but it’s been dead-ended as an HTML5 standard since 2010. You never know when this feature will be dropped from Chromium/Electron, so you probably don’t want to use this to save data locally in Electron. If you like SQLite, then the answer is, to simply use SQLite.

IndexedDB

IndexedDB is similar to NoSQL databases like Mongo or CouchDb. The structure revolves around collections of JSON objects. IndexedDB is a solid choice for saving your application data, but be aware that to access that data outside of Electron, you’ll have to provide some export functionality to your users.

File Based Storage – Text Files

Text files are very flexible. If your application isn’t going to produce a ton of data, then a text file might be a good way to go.

In most cases, text files are in plain text and are human readable. This means they are not appropriate for storing secrets, and they’re subject to manipulation by outside applications.

While easy to work with, text files are not without their share of tradeoffs. Text files can also be inefficient to load, as you may encounter heavier CPU and memory workloads as the file size increases.

The bottom line– if you know your application is going to have a lot of data, go with an embedded database instead.

JSON

If you’re using Electron, you know Javascript and you know JSON. Node works with JSON files natively, so JSON is a no-brainer right? Nope, JSON is a brainer.

Yes, Node handles JSON files quite nicely… provided they’re not too large. If your application has a lot of data (i.e., tens to hundreds of megabytes), you’ll want to switch to an embedded database.

JSON is human readable, and easily modified outside of your application – that is both good and bad. You obviously won’t want to store secrets like passwords in a plain text file like JSON.

JSON is also super easy to read and write. I already use a library called fs-extra, and it extends the Node fs library with sync and async commands for reading and writing JSON files without having to do any stringifying or parsing.

So in a nutshell, if the data volume of your app is modest, JSON is a very easy and efficient way to save your data.

Everything Else

You can save your data in flat text files in any format you want, whether it be XML, CSON, YAML or something else.

Unlike JSON, however, you’re going to have to convert the data to and from Javascript Objects every time you load or save. Depending on the complexity of the parsing, it can be CPU intensive and slow things down noticeably. Unless you have a specific goal of interoperability with other applications, you might want to forego this option.

File Based Storage – Embedded Databases

SQLite

If you are comfortable with relational databases, SQLite is a solid option. You get a file based API that gives you plenty of room to grow. You can use other tools to read SQLite files, such as a database query tool. There are a bunch of libraries that let you use SQLite, but you might want to give better-sqlite3 (https://github.com/JoshuaWise/better-sqlite3) a spin, as it advertises itself to be more performant than the other SQLite libraries.

Worth mentioning is that SQLite can handle a JSON data type, which does make life easier in terms of handling the data.

Since relational databases have schemas, you need to think about interoperability with older versions of your data files as you update your application and database schema. You will need to use a migration library to upgrade older database schemas to new ones. While this can be handled without too much headache by using with a preexisting library, it is something to think about. You may be able to forego the migrations if you use an ORM (object relational mapper) or a NoSQL wrapper that can write to SQLite.

NeDB

NeDB (https://github.com/louischatriot/nedb) is an embedded database that has an API similar to MongoDB. NeDB allows you to use a local file in the same fashion as SQLite.

It’s not that hard to use, and while not as fast as a real MongoDB database, it’s fast enough to be used with Electron. NeDB can be used to write to a file, or to one of Electron’s built in data stores. To be sure that it writes to a file, you do need to make sure you initialize it with a file path.

I’m not going to go into any further detail about NeDB for one simple reason– I haven’t had the need or opportunity to use it yet.

A Note on…

Storing Secrets

If your application is storing any secret data like passwords and API keys, you may not want to store those using any of the methods explained above. There is a library called Keytar (https://github.com/atom/node-keytar), which works well for Windows and Mac users. It can read and write data to the Windows Credential Manager or Mac Keychain, respectively. It will work on Linux, but it’s not as seamless as it is for Windows and Mac.

You can use Keytar in combination with any of the storage methods discussed in this blog by simply storing the ID of the secret in your file (as opposed to the secret itself).

API Wrappers

There are some wrappers that can use SQLite or IndexedDB as a back end while providing a different API. I have no real opinions on them, but if they happen to have an API that fits how you like to program, they can be a huge time saver.

For one app I wrote, I used Dexie.js (http://dexie.org/) to handle storing data to Electron’s IndexedDB. Some other options include Keyv (https://github.com/lukechilds/keyv) and PouchDB (https://pouchdb.com/).

If you’re using SQLite, you might want to use an ORM (object relational mapper) like Sequelize (https://sequelize.readthedocs.io/en/v3/) or bookshelf.js (http://bookshelfjs.org) to avoid having to write SQL. Whether you should use an ORM really depends on how you like to do things. ORMs do add weight to your application though, and frankly speaking, SQL is not a hard language to learn.

If there’s an API that mimics the way you’ve done thing in the past, go with that. If you don’t mind learning the direct API, then do that. Do you enjoy rolling your own wrapper?…do that instead. The best and only advice I can give is to take the approach that gets you forward progress the fastest.

Ok, Let’s Recap

The best options for saving local data, in my humble opinion, are: IndexedDB, JSON files, SQLite and NeDB.

Electron Built-in Storage: IndexedDB

The Good Parts

  • Storing reasonably large amounts of data
  • Storing complex data
  • Many wrappers are available to give you a choice in API

Hit Or Miss

  • Asynchronous API
  • Data can’t be read by other processes while Electron is running

The Not So Good Parts

  • Data portability
  • Easy access by end users outside of Electron

File-Based Storage: Text Files – JSON

The Good Parts

  • Storing modest amounts of data
  • Storing complex data
  • Data Portability
  • Human Readable
  • Super easy to implement
  • Easily Backed Up
  • Easy – no API wrapper required
  • Because you’re just saving a Javascript Object, you can organize it however you want

Hit Or Miss

  • Data can be manipulated by anyone with a text editor
  • Data can be manipulated by other applications while Electron is running
  • Can be done synchronously 2

The Not So Good Parts

  • Storing large amounts of data
  • Plaintext storage — there is no obfuscation out of the box

File-Based Storage: Embedded Database – SQLite

The Good Parts

  • Storing reasonably large amounts of data
  • Storing complex data
  • Data Portability
  • Not plain text
  • Easily Backed Up
  • Not hard to implement
  • People who are familiar to RDBMS
  • ACID compliant

Hit Or Miss

  • Synchronous API available 3
  • Object Relational Mappers (ORMs) can make coding easier
  • Because SQLite is a standard, files can be read by other applications
  • You should plan your schema in advance

The Not So Good Parts

  • When not using an ORM, requires knowledge of SQL, which can scare some people away
  • Requires knowledge of how to create a proper relational database
  • Schema changes over time need to be managed and handled automatically 4

File-Based Storage: Embedded Database – NeDB

Good Parts

  • Schemaless data
  • Storing large amounts of data
  • Storing complex data
  • Data portablity
  • Not plain text
  • MongoDB-like API

Hit Or Miss

  • API is Asynchronous
  • NeDB files aren’t as easily read by other applications as SQLite
  • Database cannot be accessed by other applications while open by Electron 5
  • NeDB will save to Electron’s IndexedDB if you don’t provide a filename

Not So Good Parts

  • API might feel foreign to people who are more familiar with RDBMS
  • Might not provide ACID transactions 6

Some Final Words

First thing – don’t take my word for anything. I am literally just another guy messing with Electron. Look around, do your own research. The more informed you are, the better decision you’ll make for your application.

The biggest decision you will need to make is whether to use Electron’s built-in options or a file-based option. There are pros and cons to either approach. You can always change your mind in the future, but depending on the volume and complexity of your application data, it can be a nuisance to migrate later.

Having said that, don’t overthink or overengineer how you save your application data. Put your application’s requirements first. If you’re well funded and you are writing an application with a clear long term roadmap, then yeah, you can afford to overthink things. If you’re a bootstrapped startup and throwing a spaghetti app out there hoping that it will stick, go with the fastest and easiest method for you to ship. You don’t know that the app will be successful, and it can’t be successful if it’s not out there. Balance your resources (i.e., time and money) with your release schedule in making your decision.

Now get out there, build your baby and ship it.

Footnotes

  1. This is a non-issue for most people, but there are some use cases where it might be a dealbreaker. For example, if your Electron app is creating job definitions for a scheduled task that is being handled by a separate executable.
  2. I use fs-extra (https://github.com/jprichardson/node-fs-extra) to read/write the file
  3. Provided you use better-sqlite3 (https://github.com/JoshuaWise/better-sqlite3) Because better-sqlite3 is synchronous, it probably won’t work with many ORMs.
  4. The easiest way to do this is with a database migrations library. Some ORM libraries include this feature.
  5. https://github.com/louischatriot/nedb/pull/535
  6. https://github.com/louischatriot/nedb/issues/398

Extrata - Save data locally in Electron

 

Assign It To Us uses Electron to develop Extrata.info

 

Quick Hit: Everyone Wants To Go Home During Extra Innings — Maybe Even The Umps

Steven NgAnalyticsLeave a Comment

FiveThirtyEight has a fascinating article on the tendencies of umpires in extra innings when it comes to calling strikes and balls:

Altogether, teams that are in a position to win get up to a 27 percentage point increase in the rate of called balls, while teams that look like they’re about to lose see increased strike rates of up to 33 percentage points.

I can’t imagine the umpires or the umpire’s union is going to be very pleased with this report.

Last month, FiveThirtyEight had another interesting article about which NBA teams are wronged by the refs the most, and the NBA referee’s union apparently called it “fake news”, in spite of the fact that the NBA provides the last two minute reports used by the article’s analysis for everyone to see.