- Why the Future of Data Storage is (Still) Magnetic Tape (IEEE Spectrum)
- All Things Sales! 16 Mini-Lessons for Startup Founders (Andreessen Horowitz)
- The Long Goodbye (To Facebook) (Om.co)
- How to Fall Asleep in 120 Seconds (Sharon Ackman on Medium)
- Mastodon and the challenges of abuse in a federated system (Read The Tea Leaves)
- Teachers Are Moonlighting As Instagram Influencers To Make Ends Meet (BuzzFeed News)
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.
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.
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 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, 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 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.
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.
Ethan Kurzweil, “An inside Look at Bessemer Venture Partners’ Investment Process for Twilio,” TechCrunch, April 11, 2017, http://social.techcrunch.com/2017/04/11/an-inside-look-at-bessemer-venture-partners-investment-process-for-twilio/.
Jeff Bezos, “Exhibit 99.1,” Shareholder Letter, (April 13, 2017), https://www.sec.gov/Archives/edgar/data/1018724/000119312517120198/d373368dex991.htm.
Yinmeng Zhang, “How to Measure User Retention and Track Improvement,” Apptimize, July 28, 2016, https://apptimize.com/blog/2016/07/measure-user-retention-track-improvement/.
josephg, “Josephg’s Comment on ‘How We Built r/Place,’” Comment, Hacker News, (April 14, 2017), https://news.ycombinator.com/item?id=14112085.
Bruce Booth, “Of Abundance And Scarcity In Venture Capital,” LifeSciVC, March 13, 2017, https://lifescivc.com/2017/03/abundance-scarcity-venture-capital/.
Ty Magnin, “The 4 Customer Retention Metrics You Need to Measure Now,” Appcues Blog, accessed April 15, 2017, https://www.appcues.com/blog/the-4-customer-retention-metrics-you-need-to-measure-now/.
Colin Dickey, “The Elements of Bureaucratic Style,” Longreads, April 12, 2017, https://longreads.com/2017/04/12/the-elements-of-bureaucratic-style/.
Eric Newcomer, “Uber, Lifting Financial Veil, Says Sales Growth Outpaces Losses,” Financial News, Bloomberg.com, (April 14, 2017), https://www.bloomberg.com/news/articles/2017-04-14/embattled-uber-reports-strong-sales-growth-as-losses-continue.
Alex Wilhelm and Katie Roof, “Uber Shares Growing Financials to Distract from Negative Publicity,” TechCrunch, April 14, 2017, http://social.techcrunch.com/2017/04/14/uber-shares-growing-financials-to-distract-from-negative-publicity/.
American Bar Association, ed., Fund Director’s Guidebook, 4th edition (Chicago, Illinois: ABA, Business Law Section, 2015).
Kurt J. Berney, Mitchell S. Presser, and David H. Wasserman, eds., Private Equity & Venture Capital Investing: Legal, Financial & Strategic Techniques for Successful Investing, Corporate Law and Practice Course Handbook Series, no. B-1276 (New York, N.Y: Practising Law Institute, 2001).
Danah Boyd, “Toward Accountability: Data, Fairness, Algorithms, Consequences,” Data & Society: Points, April 12, 2017, https://points.datasociety.net/toward-accountability-6096e38878f0.
Justin J. Camp, Venture Capital Due Diligence: A Guide to Making Smart Investment Choices and Increasing Your Portfolio Returns, Wiley Finance Series (New York: Wiley, 2002).
John Henry, “Mastodon Is Dead in the Water,” Hacker Noon, April 5, 2017, https://hackernoon.com/mastodon-is-dead-in-the-water-888c10e8abb1.
Ilya Pestov, “How PCs Were Advertised in the 1990s,” freeCodeCamp, February 11, 2017, https://medium.freecodecamp.com/how-pcs-were-advertised-in-the-1990s-cdaee59f2555.
M. G. Siegler, “The Squid,” 500ish Words, March 27, 2017, https://500ish.com/the-squid-218be9939cdf.
Andy Sparks, “All the Venture Capital & Fundraising Bloggers You Should Be Following,” Medium, January 25, 2017, https://medium.com/startup-grind/all-the-venture-capital-fundraising-bloggers-you-should-be-following-6ea9817039c4.
Rick Turoczy, “An Open Source Guide for Building the Startup Accelerator of Your Dreams,” Medium, April 4, 2017, https://medium.com/portland-incubator-experiment/an-open-source-guide-for-building-the-startup-accelerator-of-your-dreams-8ff931d8c3a.
Alex Wilmerding, Term Sheets & Valuations: An inside Look at the Intricacies of Term Sheets & Valuations (Bedford, MA: Aspatore Books, 2001).
“How We Built r/Place,” Upvoted, April 13, 2017, https://redditblog.com/2017/04/13/how-we-built-rplace/.
Stef Aupers, “‘Trust No One’: Modernization, Paranoia and Conspiracy Culture,” European Journal of Communication 27, no. 1 (March 1, 2012): 22–34, doi:10.1177/0267323111433566.
Josh Constine, “Facebook Messenger Hits 1.2 Billion Monthly Users, up from 1B in July,” TechCrunch, accessed April 12, 2017, http://social.techcrunch.com/2017/04/12/messenger/.
Dan Motzenbecker, “Fast Drawing for Everyone,” Google, April 11, 2017, https://www.blog.google/topics/machine-learning/fast-drawing-everyone/.
Mike Preuss, “Financial Modeling for Startups,” OpenView Labs, April 11, 2017, https://visible.vc/blog/financial-modeling-for-startups/.
Robert Safian and Robert Safian, “Mark Zuckerberg On Fake News, Free Speech, And What Drives Facebook,” Fast Company, April 11, 2017, https://www.fastcompany.com/40397297/mark-zuckerberg-on-fake-news-free-speech-and-what-drives-facebook.
Strange Parts, How I Made My Own iPhone – in China, accessed April 12, 2017, https://www.youtube.com/watch?v=leFuF-zoVzA&feature=youtu.be.
Daniel Tomlinson, “It’s Not the Gig Economy, Stupid.,” openDemocracy, April 11, 2017, https://www.opendemocracy.net/beyondslavery/daniel-tomlinson/it-s-not-gig-economy-stupid.
Benjamin Wallace-Wells, “The Despair of Learning That Experience No Longer Matters,” The New Yorker, April 10, 2017, http://www.newyorker.com/news/benjamin-wallace-wells/the-despair-of-learning-that-experience-no-longer-matters.
edit: apparently, this article has gotten a fair bit of attention from Hacker News. There’s a link here.
edit 2: the HN link was flagged and removed.
edit 3: like adults, Kevin and I talked out our differences. I still think he came off as exceedingly callous, and we still fundamentally disagree on several core issues here. But we both have a fair bit of common ground. While I still feel justified in initially writing this little post, know that I feel comfortable discussing these issues with him and anyone else on reasonably civil terms.
Unless you’ve been living under a rock, you’ve probably heard that the American Health Care Act, known by many as Trumpcare, died a somewhat ignominious death on the floor of the US House last week.
I say good riddance to the bill. And, moreover, I tweeted out “Next stop: Single Payer” because I’m a fan of the idea of single payer healthcare. (I’m no policy expert in much of anything, much less healthcare, so I openly admit that my affinity for single payer is purely based on feelings.)
So anyways, who parades into the @-replies like the little kid in the Robert Kelly BBC video but the CTO of the company I used to research and write for… Continue reading “An Awkward Twitter Exchange With A Former Employer”
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.
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…
(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.)
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.
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.
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,
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:
Using the official, three-character currency codes, we’d convert Canadian Dollars to USD using this function:
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.
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.
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.
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:
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.
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()…
So, now we can bring it all together…
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.”
And there you have it…
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, 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.