Remote Worker Chicken Meatball Soup

People who work in creative fields like writing, programming, and design tend to be productive in fits and starts.

I know some folks who have the discipline to sit at a desk and just crank out good quality work, day in and day out. I don’t think I’m one of these people. I can do this type of work, but at least in my case the productive output feels pretty mechanical in process and final product. At least to me.

Counterintuitively, I find that my most productive and creative days involve little actual productivity, from a percent-of-the-day perspective. Good ideas take time to surface and coalesce, and, at least for me, that’s rarely a deliberate activity. 

I like to let those things come together via some unknown cognitive background process. (The thinking slow part of Thinking, Fast and Slow.) But it’s not like I can just sit and meditate my way to a research question or article idea; I have to be doing something active.

A lot of folks find mental clarity and flow states through exercise. I, for one, tend to hate every minute of exercising and can only really focus on just how badly the podcast episode I’m listening to is distracting me from whatever fresh hell I’m wreaking on my corporeal being today. (Except for when I’m cycling; then, I fly.)

I currently work from home, and that’s given me the opportunity to exercise a different set of skills. I really enjoy the process and rhythm of cooking, and I’ve managed to build food prep and cooking into my workweek as a way to have something productive and creative to do while the creative, productive work I do for money and health insurance metaphorically simmers on the back burner.

Is cooking downtime? Technically, yes. But it’s productive downtime that helps with managing stress, saving money, and eating healthier. Is it less efficient from a time perspective? Sure. It’s easier and much quicker to go get lunch at the yuppie food court around the corner, but I currently make the small-to-medium-sized dollars writing on the internet about large-dollar venture capital deals. If I made software or finance money, I’d consider eating fancy $20 salads most days, but I’d probably still sneak in a bunch of homemade lunches. 

Because, again, it’s not about the time or the money; it’s about the process. Years of practice and following instructions lets you start coming up with your own instructions and your own recipes. Whether that’s for code, for data visualizations, for phrasing, or for soup.

Here’s a recipe for a very tasty soup, with plenty of color commentary. Forgive the verbosity. It’s an occupational hazard.

Apologies for taking a bite out of one of the meatballs before taking the picture, which could have probably been taken from a better angle.

Ingredients

For the meatballs

  • 1 pound ground chicken
    • if you can find ground chicken thighs, even better
  • 1 Tablespoon of finely minced fresh ginger
    • Unless you have no other option, avoid the jarred stuff. Buy whole root and peel it with the edge of a spoon before processing
  • 1 Tablespoon finely minced garlic
    • You could also use a microplane to process the garlic. However, because the microplane creates so much surface area, you might want to use slightly less garlic if you’re sensitive to it
  • 2-ish Tablespoons of finely sliced scallions, white and pale green parts only
    • Finely slice the darker green parts too, but keep those separate for later
  • A daring amount of red pepper flakes, to taste
    • If you have fresh hot peppers, even better. Especially if they’re Thai chilis or habaneros, because of the fruity notes they give
  • A little kosher salt
  • A little olive oil

For The Soup

  • A 32oz container of good cloudy chicken broth
  • 3-4 leaves Lacinato kale
  • Rice vermicelli or other thin rice noodles.
  • Salt
  • (Optional) Just a wee bit of the special salt (MSG)
  • (Optional) Dried shiitake or maitake mushrooms

For The Toppings

  • The reserved sliced dark green parts of the green onion
  • Cilantro
  • Fermented hot sauce
  • (Optional) a dash or two of soy sauce

Preparation Of The Work

  1. Because anxiety, wake up at approximately 6:15 AM, about an hour before the scheduled alarm. Roll over and reach down for your vintage iPad Pro. Read the internet for awhile, until about 7 AM.
  2. Perform your regular morning ablutions. Shower, shave, teeth brushing, etc. Get dressed.
  3. Realize you have some ground chicken in the fridge that you should probably do something with before you go on vacation.
  4. Look in your fridge’s produce drawer and assess the state of your provisions. Note the ever-so-slightly desiccated exterior of the ginger you bought last week for smoothies but instead left to languish in the produce drawer, slowly drying up.
  5. Remember bits and bobs from that one meatball soup recipe in that Nigel Slater cookbook, Tender.
  6. Come to terms with your lack of kale and various herbs and aromatics.
  7. At 7:50 AM, decide that you have enough time for a very quick trip to the grocery store.
  8. Walk several blocks to the grocery store. Obtain ingredients. Go home, and refrigerate your perishables.

Preparation Of The Ingredients

Mise

Unless you’re exceptionally slow with a chef’s knife, this shouldn’t take much more than 15 minutes between Zoom calls. This is also a process that can be accomplished during a call if you’re not on video, though you may want to mute yourself when dealing with clanky metal bowls.

  1. Grab yourself a big mixing bowl and a stack of small prep bowls.
  2. Peel ginger using the edge of a spoon. Place garlic on a cutting board, place the side of your chef’s knife on the garlic and, with a closed fist and swift hands, hit the side of the knife with the bottom part of your hand sufficiently hard to crush the garlic slightly and loosen the skin, but not hard enough to send garlic shrapnel flying about the kitchen, the smell of which would linger for days.
  3. Mince garlic and peeled ginger, together or separately. Both will end up in the same small prep bowl.
  4. Slice your scallions and place them in the bowl with the garlic and ginger. Add your pinch of pepper flakes or finely-minced fresh hot peppers to the prep bowl. (Please note: if you’re using fresh peppers it’s advisable to wear a glove and use a separate small cutting board for the prep.)
  5. Wash your kale under cold water. For each leaf, run the tip of your knife along each side of the fibrous stem to cut it out. Stack and roll your de-stemmed kale leaves like a cigar, and chiffonade the leaves widthwise. You should be left with a lot of very thin ribbons of kale. Place in the remaining (empty) prep bowl.
If not using immediately, place both prep bowls in the fridge. Both should be fine there for a couple hours without degrading their quality.

Make The Meatballs

This is another step that can be accomplished in the interstitial time between calls or downtime between large-ish tasks. This process should take about 20-25 minutes from beginning to end.

  1. Get a sufficiently large frying pan onto the stove and begin pre-heating it over medium-low heat. The next couple steps should only take a few minutes, enough time for your pan to come up to temp.
  2. Get your large mixing bowl.
  3. Put in your ground chicken, your aromatics from the small prep bowl, salt (I recommend going very easy on the salt because you can always add additional salt to the broth later), and a little olive oil (maybe a couple of teaspoons) to help a bit with emulsifying all the ingredients.
  4. Using very, very clean hands (or, if you’re squeamish about chicken, gloved hands—or a spatula if you must) thoroughly mix the ingredients until they’re homogeneously blended. Then wash your hands.
  5. Add a little bit of oil to the pre-heated pan. Even if it’s nonstick, oil helps with conducting heat.
  6. Again, using very, very clean hands (or gloved hands), roll the meat mixture into spheres just a bit smaller than golfballs. Make your meatballs and place them directly into the pre-heated pan. Do not overcrowd the pan. I repeat: do not overcrowd the pan. Do this in a couple batches if you need to.
  7. Lightly brown the meatballs on all sides. Keep in mind that, at this point, it’s likely that they aren’t cooked all the way through, and should be treated as if they were still raw.
  8. If not using immediately, remove from heat to a ceramic plate or glass storage container. Refrigerate if you’re not going to make your soup within about 20-30 minutes after browning.

Build Your Soup

This can be initiated in parallel with the browning step of the meatballs.

  1. Bring your chicken broth or stock to a simmer in a large enough saucepan to accommodate your meatballs and cooking liquid. (If you’re using dried mushrooms, add them to the pot alongside the stock so they can come up to temp together. This will give the mushrooms time to rehydrate.)
  2. Carefully add your meatballs to the broth, ensuring that they’re covered by at least an inch of liquid. If not, add more broth (if you have it) or water.
  3. Bring back to a simmer and let cook for about 5 minutes, long enough to cook the meatballs through to the center.
  4. Taste for salt and add more if desired.
  5. Add your kale ribbons to the pot, stir to combine, and cover. Simmer for 2-3 minutes until the kale is slightly softened but still has a little texture to it.
  6. Take a tangle of thin rice noodles and add it to the pot, ensuring they’re submerged in the cooking liquid. Let simmer for 1-2 minutes, until al dente.

Plating (Bowling?)

  1. In your favorite soup bowl, ladle in a satisfying number of meatballs, some rice noodles, and some broth.
  2. In order: Top with a good fermented hot sauce, ideally made from green chili peppers. Then add your scallions. Then add your cilantro, lightly torn.
  3. Eat immediately.
  4. Refrigerate leftovers for lunch tomorrow and/or the following day.

The Net Fishing Guy

At a beach south of Hilo I spent about 20 minutes watching this guy watch the water.

Bunched under his arm was a fishing net. He was standing on some rocks, staring into a pool. Leaning forward, ready to strike. Like a leopard.

Every minute or so he would stand up, loosening his stance. Readjust, stepping one or two feet to one side or another, angling.

I imagine that this was one of his usual spots. It’s hard to tell what exactly he was looking for. As he stood there, you could see in his stance that he was intent on accomplishing a single mission.

It went on like this. Standing at the ready, adjusting stance slightly, fiddling a little with the net, to make sure the weighted beads around its frill were hanging down and flat.

The tide was coming in.

I don’t know what his trigger was, but he saw his target through the chop and foam. Like a starburst his net, easily six feet across, cast wide for an instant before sinking to the bottom.

I watched him haul in the net, scooping nylon in voluminous bunches. A tangled snapper came in with one of the final pulls.

He hugged the net close as he walked across the jagged lava rocks to an orange five-gallon bucket he had perched on another rock, higher up away from the surf.

He fished his quarry out of the net, placed it in the bucket, put on the lid, bunched up his net, and tossed it over his shoulder. He waded through the rising tide, crossed through the beach. He put the bucket and his net in the back of his mini van, parked beneath some trees. And he drove off.

One cast, one fish. More anecdotal evidence that providence favors the patient and practiced.

How To Send A Haunted VC/Startup Newsletter

As you may or may not know, I have a newsletter I’ve been sending out since May 2016 on a roughly weekly basis. If you want to keep up with what I write at the day job and the most interesting links I find while procrastinating at my day job, consider subscribing: Rowley.Report.

Considering it’s Halloween and all, I figured I should share some of the tips and tricks I use to produce that content.

You have to understand that to produce something like that every week, an elaborate conjuring ritual is involved. Trick or treat?

  1. Start by arraying five beeswax candles in the shape of an upside down star on the floor. Light the candles.
  2. Fashion a miniature boat out of cocktail bar coasters and glue, and use one of the heavy metal pens they give you to sign your bill at the Rosewood as a mast.
  3. Print out the most recent leaked term sheet and fashion it into a sail, affixing it to the makeshift boat.
  4. Anoint the small boat with samples of billionaire founders’ blood, sweat, and tears.
  5. Place the boat in the middle of the inverted star on the floor.
  6. Burn and scatter the ashes of one of the last remaining hard copies of Georges Doriot’s Manufacturing class notes into and about the small boat.
  7. For safety, blow out the candles in your house and take your boat to the nearest body of water, ideally while riding a Bird or Lime scooter.
  8. Short-circuit one JUUL e-cigarette battery such that it begins to heat up, and place it in the boat.
  9. Also place a USB stick containing the weekly output of charts and all items, read and unread, from my RSS feedreader.
  10. Push the boat into the open water. If timed correctly, the short-circuiting JUUL battery will light the whole thing on fire, resulting in a kind of viking funeral effect. Like this: 🔥⛵
  11. Summon the Owl by hooting vigorously toward the blackness of the woods. Speak of phenomenal risk-adjusted returns with it, in tongues, until sunrise.
  12. Go home, and sleep.
  13. Kind of all in one fell swoop: Merge accumulated notes in iA Writer & Drafts 5 into a new document; edit markdown in iA Writer; in iA Writer, left-swipe with two fingers to reveal well-rendered and semantic HTML; copy; paste into new Mailchimp email campaign.
  14. Press send.
  15. Wait.

My Grandfather’s Ghost In The Watch

Earlier today, Apple announced a raft of new phones, which no doubt will excite a lot of people. Apple also announced a new version of its Apple Watch today—Series 4.

One of the watch’s new features is very near and dear to me: the conductive pads on the back which help the watch capture electrical activity and display it as an electrocardiograph.

Image credit: Apple

Back in the late 1950s, my grandfather, Donald Rowley, co-invented the gel electrode alongside fellow pathologist Seymour Glagov. From his 2013 obituary published by the University of Chicago’s news office:

At the time, there were no tools to monitor heartbeats over long periods; so they invented one. They worked with an undergraduate student at UChicago and a watch repairman to convert a spring-wound pocket watch into a portable pulse counter and built tiny electrical sensors that could be glued reliably to the chest—the first gel electrodes. The gadget later would be scaled up with help from the Elgin National Watch Co. and Illinois Bell Telephone.

This simple device, no bigger than a deck of cards, accurately could record the electrical activity of the heart for more than 24 hours. It heralded the birth of ambulatory cardiology. Rowley and colleagues described their counter in a report to Science in 1959. They performed additional studies on 100 volunteers, showing enormous variation in daily heart rates.

They didn’t patent their invention because, as my grandfather put it, it was “simply not part of the academic culture.”

No longer a wind-up device made of watch parts, a descendent of my grandfather’s ticker-tracker lives on in watch form, which I think is pretty wild.

News’s Future Formats Favor Fortunate Incumbents

As a somewhat accidental member of the industry, I spend a fair bit of time thinking about media.

It’s for this reason I was so happy to come upon a two-part series from Tristan Ferne of the BBC’s research and development group.

Part 1 of the “Beyond 800 words” series, published back in September 2017, opens with the following:

The 800-word article is still the dominant form of online news from most publishers. This largely seems to be a legacy from printed newspapers and to a lesser extent this is true for online news video online too, with much of it still produced in traditional made-for-TV formats albeit shorter.

And in Part 1 Ferne identifies and briefly discusses a number of new formats like listicles, live blogs, and structured news.

Part 2, published last week, covers recent research work at the BBC around how Gen Z (18-26 year olds) wants to consume the news. The article discusses some of their methodology and research findings, and it presents examples of prototype news formats and interaction models that appealed to Gen Z audiences.

The thing about those formats – which include scrollable video, swipe to view reactions/polls, a section-by-section “choose your own format” model, and others – is that they are really nice, but also incredibly resource-intensive to produce at scale. For that modular format one, imagine having to:

  • Write a long version of the article
  • Write a short version of the article
  • Break each version into modular sections
  • Produce video for each section
  • Edit, view, and test the multiple formats to ensure they form a cohesive narrative
  • Ship the damned thing

That’s difficult for a small or medium-sized newsroom to do more than once a month. A dedicated team – bare minimum: a researcher, a writer, a video and sound editor, and an editor/production lead – could probably push something out once a week.

It’s all to say that if there’s a format race, it’s likely to be between bigger, well-resourced newsrooms. There’s definitely room for small newsrooms which are built around a novel format. Ferne references Circa as an example. But that’s a high stakes bet, because you’re wagering on both your ability to find and report on important and interesting stories and, moreover, that the fancy new format stays relevant among a fickle and increasingly fast-moving audience.

That’s going to be a tall order.

You can subscribe to my newsletter, the Rowley Report, at Rowley.Report.

Three Markdown Tools I’ve Known And Loved

There have been many programs that build upon Markdown’s principle of readability in pure-text formatting, and I wanted to share a couple of them here.

Since issue #2 of my newsletter, I’ve written almost everything in Markdown, the text-to-HTML conversion software originally written by John Gruber, the Daring Fireball himself.

It’s shockingly easy to use when you get the hang of its syntax. Fortunately, there are a number of “cheat sheets” available to help you get started if you’re not familiar with it. I personally prefer writing in Markdown (specifically MultiMarkdown) on a plaintext editor to writing in a WYSIWYG environment these days because it’s a little closer to the metal and gives me a lot of options for how I want to share my work.

Since the original release of Markdown, a lot of developers have built Markdown editors into their software to excellent effect. Since Markdown was originally built by and for writers, it’s no surprise that it’s used as the markup language of choice for Github pages, blog posts on Ghost and other blogging platforms, and other places where text is written and published on the web.

What I find most interesting about Markdown is the design philosophy. First and foremost, as Gruber says in the original spec:

“The overriding design goal for Markdown’s formatting syntax is to make it as readable as possible. The idea is that a Markdown-formatted document should be publishable as-is, as plain text, without looking like it’s been marked up with tags or formatting instructions.”

There have been many other programs that build upon Markdown’s principle of readability in pure-text formatting, and I wanted to share a couple of them here.

Dillinger.io

Dillinger.io is a simple, web-based Markdown editor with a pane that renders your Markdown as formatted HTML in really-close-to-real-time. It’s a great site for playing around with Markdown for the first time.

(It’s kind of like Codepen for text.)

iA Writer

iA Writer, is a minimalist and un-opinionated Markdown editor for MacOS and iOS, and it’s been my Markdown editor of choice basically since it launched. I first used it on my first generation iPad with that amazing keyboard dock, and continued to use it as iA built the Mac app and built out the feature set.

One of my favorite features of iA Writer is its syntax highlighting. Unlike with a code editor, iA Writer lets users highlights English words based on the part of speech they represent. So, if I’m working on a sentence that’s lexically dense, I’m able to visualize its bits and pieces to ensure that I’m not veering too deeply into run-on territory.

It also features easy integration with Dropbox and iCloud Drive, full-screen editing, metadata support by way of MultiMarkdown, a range of export modes, easy image/file embedding, and custom document templating for PDF exports, a feature near and dear to my heart.

There are other Markdown-driven editors out there, including Ulysses and Scrivener, which are geared toward toward users who want more software-based assistance with project management and organizational structure. I’ve tried Ulysses and quite liked it, but I still prefer iA Writer’s somewhat hands-off approach.

At time of writing, iA Writer is still my tool of choice for writing my weekly-ish newsletter. It’s one of the best pieces of software I use and can’t recommend it highly enough.

Marp

Marp is an open source, cross-platform editor for making presentations using Markdown. It utilizes Github-flavored Markdown’s syntax and a simple text editor with more traditional syntax editing and so-called “directives” for how Marp’s rendering engine treats pagination and aspect ratios. To that end, using Marp, you can set aspect ratios for standard paper sizes in A and B 0-8, as well as other popular aspect ratios.

For now, the software is still in a pre-release beta, and it lacks some of the features of more fully-developed Markdown presentation apps like Deckset or the Remark.js library, but it’ll be interesting to see where the project goes.

Closing thoughts

I am a big, big fan of Markdown, and since it’s become the default plain-text input syntax for extremely popular websites like Github, reddit, Hacker News, StackExchange and others, it seems to have cemented itself as the default standard on the web.

However, like anything that isn’t purely WYSIWYG, Markdown still feels like a thing for programmers, power-users and professionals. This reputation, to me, feels somewhat undeserved because, again, it’s really freaking easy to pick up.

So, if you’ve never tried writing in plain text like this, give a Markdown editor a spin. There are literally hundreds of great options out there.

Autogenerating Currency Conversion Formulas In Google Sheets Using GOOGLEFINANCE(CONCATENATE())

I forgot where I heard this, but on some or another podcast it was declared that “Microsoft Excel is the gateway drug to programming.” And I’m coming to believe this more and more.

I’m someone with more than a passing interest in programming who simultaneously lacks any serious proficiency with writing code. Irony of ironies, right? Also, I’m now in this weird liminal space between being really good at manipulating data in a spreadsheet-type environment and seeing the possibility of what I can do with a real programming language while still sorely lacking in the practical know-how to get a lot of things done.

And that’s why I’m still tied to using Excel and Google Sheets for a lot of my data analysis work. Both are fine tools, albeit with their own quirks and limitations. Although Excel is good for the kind of work I do, which typically involves big CSVs that Google Sheets kind of chokes on, I strongly prefer Google Sheets for its more expressive functions and easy connectivity to outside data stores, including Google’s own services.

It’s for this reason that I’m going to be covering the subject of creating abstract, auto-generating formulas in Google Sheets only. Also, I’m like 90% sure one can’t use CONCATENATE() in Excel for the use case I’m presenting here, but I’d love to be proven wrong.

The challenge

Part of my job is to analyze venture capital data, and oftentimes the scope of my analysis expands beyond US borders. Accordingly, I often have to deal multiple currencies, which can be a pain.

So imagine a column containing data for an arbitrarily large number of VC deals priced in an arbitrary number of different international currencies. (For the sake of this example, let’s assume these are all fairly recent deals, so historically accurate currency conversion figures aren’t important.)

Remembering that our dataset can be arbitrarily large, what’s the easiest way to convert those foreign currencies into USD so we can make comparisons on equal terms?

An example dataset

Below, to avoid using proprietary data, I fabricated an array of 20 sample deals using a random number generator and multiplying its output by different scalars depending on the round type. The size of deals do not necessarily correspond to any real-world averages and are here for demonstration purposes only.

Also, I picked 20 rows because it’s small enough to fit in a screenshot. For a sample size of 20 rows, it’s still easy to do all the conversions by hand sorting, but the ideal solution would scale to sheets with hundreds or thousands of rows and a huge combination of currency conversions. This is why we’re going to emphasize abstraction here.

Here’s what we’ve got to work with…

Screenshot 2017-03-23 14.07.14

(Yes, the numbers are hideous. Deal with it.)

Here, we have sample deals from five different countries: the USA, Canada, the UK, France, and Germany. (I intentionally picked two countries that use the same currency for reasons that will become apparent later)

Converting foreign currencies to USD, some methods of varying efficiency

We have several choices for how we want to make the conversions of foreign currencies to USD. Here, I’ll share three ways, with each successive option being more abstract and scalable than the last. A list of top books will be helpful for you to become a consistently profitable trader.

To remind ourselves of what we want to convert our currencies into here, we’ll add a “Target Currency” column and set all values in that column to USD. And we’ll also add a “Conversion Rate” column where we’ll set our conversion ratio. (This column isn’t absolutely necessary, strictly speaking, but it keeps things organized and clear from a visual perspective.)

Screenshot 2017-03-23 14.22.16

Brute force

Let’s say you’re a masochist with a fetish for tedium and frustration. This is the method for you.

Remember, I said “masochist” and not “primitive cave dweller” here, so I’m going to run on the assumption that, being an enlightened user of spreadsheeting tools, you understand how to sort columns.

So we start by sorting the “Base Currency” column to make things at least somewhat easier to deal with… Note how all the base currencies are now grouped.

Screenshot 2017-03-23 15.09.54

Now for the brute force part. We search Google for each conversion pair (“CAD USD,” “EUR USD,” etc.), copy and paste the ratio into our spreadsheet, and manually fill down.

Brute Force Conversion Table Making

For this small sample set, it took me almost exactly a minute to build a conversion column using brute force. But this is only for a small handful of currency pairs. If I was dealing with dozens of pairs, this would have been a much bigger task.

Enter The Joys of GOOGLEFINANCE()

Like I may have mentioned earlier, one of the nice parts about Google Sheets is that it gives users direct access to some of Google’s services directly through a series of bespoke functions.

For example, the GOOGLETRANSLATE() function lets users translate strings in spreadsheets from one language to another using the Google Translate engine. In conjunction with DETECTLANGUAGE(), one can generate some interesting formulas.

But here we’re going to talk about the joys of the GOOGLEFINANCE() function.

There’s standard syntax for pulling current and historical stock market prices:

GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

So, if I wanted to find the closing price of Apple on March 7, 2014, I’d write the formula like this,

=GOOGLEFINANCE("AAPL","price","3/7/2014")

And Google automatically returns that information and builds a table to display it.

It turns out that the GOOGLEFINANCE() function also lets you find the current conversion rate between two currencies. (Unfortunately, at time of writing, it doesn’t let you find historical conversion rates.)

Here’s that syntax:

=GOOGLEFINANCE("CURRENCY:[BASECURRENCY][TARGETCURRENCY]")

Using the official, three-character currency codes, we’d convert Canadian Dollars to USD using this function:

=GOOGLEFINANCE("CURRENCY:CADUSD")

Now, for our table of VC deals we want to convert into USD, we could hard-code the currencies we want to convert into our formulas by group.

Here are the other hard-coded formulae:

=GOOGLEFINANCE("CURRENCY:EURUSD")
=GOOGLEFINANCE("CURRENCY:GBPUSD")
=GOOGLEFINANCE("CURRENCY:USDUSD")

Google Finance will return the current conversion rate and embed it as a number in the spreadsheet.

Below is a screenshot of our Google Sheet with an escaped version of the formula next to the conversion rate. (Note what’s in the formula bar for the selected USD -> CAD conversion.

Screenshot 2017-03-23 16.57.13

And, once we’ve hardcoded all the formulas, all one would have to do is drag the corner of the cells down to fill in the rest of the table with the appropriate values.

Just like the original “brute force method” this is all well and good if the number of currency pairs is small, there are few rows, and any re-sorting of the rows will be on a whole-sheet basis.

Now, on to the fun part…

Dynamically generating currency conversion formulae using GOOGLEFINANCE(CONCATENATE())

Alright, so we start with the general form of a currency conversion rate query in Google Sheets, the same as what was previously listed.

=GOOGLEFINANCE("CURRENCY:[BASECURRENCY][TARGETCURRENCY]")

Now, let’s look at our table and its columns.

  • Column C: “Base Currency”
  • Column D: “Target Currency”

Hmmm… seems familiar… If only there was a way to join those elements into a formula.

Enter Google Sheets’s CONCATENATE() function, which is similar to its cousin CONCAT(), except for the important fact that it lets you concatenate an arbitrary number of elements.

Importantly, in Google Sheets, the output of the CONCATENATE() function is not simply treated as a string. Its output is usable inside another function.

So, to start, let’s concatenate cells D2 and E2 using the following formula:

=CONCATENATE(D2,E2)

And as we can see, the result is “CADUSD” which is is the required pairing of source and target currencies we need in our formula.

Screenshot 2017-03-23 17.17.35

Now that we’ve proven we can build our one part of our formula using CONCATENATE(), let’s see if we can build the rest of it.

Typing in the following formula produces outputs what we need to give to GOOGLEFINANCE()…

=CONCATENATE("CURRENCY:",D2,E2)

So, now we can bring it all together…

=GOOGLEFINANCE(CONCATENATE("CURRENCY:",D2,E2)

Before showing the nice gif of how it all “just works,” give me one second to explain how one formula will be able to generate a conversion ratio for all of the currencies in our set.

The D2 and E2 cell references are relative, which means that as I drag the equation down the sheet it will still pull from the cells in the two adjacent columns, but will take the value from each row. So that same equation, if applied to, say, row 3, would read from D3 and E3. From row 4, it would pull from D4 and E4, and so on down the sheet.

So, here’s the moment you’ve been waiting for…

At this point, it’s just a matter of multiplying the “Deal Amount” column by the “Conversion Ratio” column to generate the “Converted Amount.”

Screenshot 2017-03-23 17.54.39

And there you have it…

Next Steps

One of the features of this kind of query with GOOGLEFINANCE() is that it dynamically updates as new currency exchange rate data becomes available. This can be viewed as either a bug or a feature, depending on your particular needs.

In the event you don’t want these numbers to change, simply copy the contents of the columns that update dynamically and paste those cells in “as values”. (In Google Sheets, goto Edit -> Paste Special -> Paste Values Only [⌘ + Shift + V].) As this suggests, it just pastes in the alphanumeric values of the cells, and strips out the formula data.

In the event that you still want to edit the formulas, which in variably you will, my suggestion is to duplicate the tab so you have one dynamic, editable version, and the static version you can work off of in later analysis.

That’s All, Folks!

 

This has been a somewhat protracted way of saying that a little bit of abstraction makes for a time-saving and viscerally satisfying (at least from my perspective) data analysis experience.

CONCATENATE() can be used outside or inside other functions in Google Sheets to build flexible, extensible auto-generating formulas in your spreadsheets, and I’m very much looking forward to a exploring how else it can be applied in the work that I do.

This is also my first time offering up one of these tutorials, so if you liked it and want me to make more of these, please let me know! Future topics I want to cover include pivot tables, demonstrating a superior alternative to VLOOKUP() and INDEX(MATCH()), and a couple of others.

One Year Ago, I Graduated From College

One year ago, almost to the date, I finally managed to graduate from UChicago with general honors.

I did “the scenic route” through college, taking a long break after three years to work on a startup, only to come back and finish on relatively short notice.

It was worth it to go back and finish, and I’m happy I took the time off in the middle. Even though taking that time off involved a lot of struggling, and ultimately resulted in failure of the project, I maintain it was the best thing for me to do at the time. I’d do like 95% of it all over again.

In general, the last year has been good. I’ve built a platform for myself through my weekly(-ish) newsletter, and gained credibility by writing for Mattermark and now Crunchbase.

The most rewarding thing, though, has been my continued work with the Python Software Foundation. It’s an incredible organization consisting of phenomenally smart and kind people, and I have the privilege of working with some great people, including one of my oldest friends, in making Startup Row happen.

I’ve also found the thing I want to do with the next couple decades of my life, which is to find and invest in interesting startups. And anything I can do to accelerate myself toward that end will be pursued.

In general, it’s been a pretty good 365-ish days.

I’ve learned a lot in the past year, and I’m still figuring out how I want to articulate it. Stay tuned for more.

Also, as petty as it sounds, it still feels good to stick it to my high school guidance counsellor who thought applying to so-called tier-three schools was “a bit of a reach” for me.

Opportunity in Two Flavors

One night this week I met up with one of my best friends. I don’t get to see him that often, but that’s alright. It’s one of those friendships that picks up right where it left off, even if it’s been weeks or months.

He’s had a lot of opportunities, and so have I. This friend of mine is a bit older than me, and, accordingly, has moved farther on in his career. He’s one of these people with a solid job and, like, three side projects, all of which are doing phenomenally well. If he wasn’t so down to earth, it’d be all too easy to resent the guy’s success.

Somehow, we got to talking about opportunities in the meta sense, and I realized his definition of opportunity and my definition of opportunity are quite different. And, now that I’ve had a cumulative fourteen hours on airplanes without internet this week to reconcile these two differing definitions, I think I’ve been won over by his. Continue reading “Opportunity in Two Flavors”