Minimalist API for stock prices in Google Sheets

For a few years now, I've been using Google Sheets to manage my investment portfolio. It shows the current state of the investments as well as the historical data on charts, allowing me to see how the internet-informed decisions impacted it over time.

To get the prices of various instruments, I use the built-in GOOGLEFINANCE() function. However, this is not without its cons. First, it does not have all the tickers I need, and is known for causing problems with frequent #N/A errors. This issue is especially annoying when many prices depend on each other. One invalid cell is enough to disrupt the entire calculation or chart. More than once I opened my spreadsheet only to find many of the numbers replaced with errors.

Looking for an alternative

I started looking for an alternative which would cover all my needs:

  • support for ETFs, commodities, forex, and crypto,
  • the ability to get historical data of each instrument.

Ideally, the replacement would also be as simple as the GOOGLEFINANCE() — one function you pass an argument to and get the value in return. No explicit casting to number, no plucking for nested JSON values.

After some research, I found Crypto Prices. It checks the box for simplicity, but is limited to cryptocurrencies and only current prices. Though not ideal, I really liked the idea of using IMPORTDATA() with API endpoints returning text with the price. Inspired by this simplicity, I decided to build the solution myself.

Custom solution

This solution is Stonkista. A minimalist API service inspired by Crypto Prices, but designed for broad markets and providing access to historical data. The name is kinda dorky, but I like it!

Getting the prices is very easy and endpoints are pretty self-explanatory:

In Google Sheets, it's just a matter of wrapping the URL with IMPORTDATA():

=IMPORTDATA("https://stonkista.com/AAPL")

Stonkista has no usage limits, no sign ups, no API keys. It's open source and available on GitHub.

Implementation

First, I needed to find data sources. I wanted free sources with current and historical data, and ideally accessible without any API keys. I ended up choosing:

  • Coin Gecko API for cryptocurrency prices (also used by Crypto Prices). Can be used either with or without an API key.
  • Frankfurter for currency rates. It provides the official data published by the European Central Bank and includes ~30 commonly traded currencies.
  • Unofficial Yahoo Finance API for all the other instruments. Though unofficial, it has worked reliably for a long time. There are even Python/JS wrappers for it. I decided to use the API directly.

I used my usual go-to stack: Bun, Hono, Drizzle, BullMQ, Redis and Postgres, stitched together with a Docker Compose. Created background jobs to call the APIs periodically and store current prices. I also wrote a script to backfill the database with historical data.

API design

I wanted the URLs to be obvious at a glance. Ticker first, then optional modifiers like currency or date:

/AAPL              → current price of Apple
/AAPL/EUR          → converted to EUR
/AAPL/2024-01-15   → historical price
/crypto/BTC/PLN    → Bitcoin in PLN
/forex/USD/PLN     → exchange rate

One quirk I ran into was that many crypto symbols conflict with stock tickers. ETH is both Ethereum and Ethan Allen Interiors, LUNA could be Terra or Luna Innovations. I prefixed crypto endpoints with /crypto/ to avoid ambiguity.

Another thing to handle was price holes. Markets don't trade on weekends and holidays, so instead of returning errors for those dates, the API falls back to the last available price.

Lazy fetching

Yahoo Finance has thousands of tickers, so pre-populating the database with all of them wasn't practical. Instead, when someone requests a ticker that's not in the database, it gets fetched from Yahoo on demand, including full price history, and cached for future requests. The first request is slower, but all following ones are instant.

Wrapping up

My own spreadsheet pulls ~20 tickers with history going back to 2021. No more #N/A cascades. There are limitations, though, where Google Sheets struggles to load many values at once, sometimes taking a long time to populate. I might dig into those in a future post.