Build a Real-time Global Stock Watchlist in Google Sheets

Producing an entire stock watchlist can be a difficult task, particularly when trying to find real-time data across a myriad of companies or other assets from across the globe. Whilst you may be interested in some international stocks your current broker may have limited or no information on those markets. Or perhaps you're diversified across different asset classes such as cryptocurrencies, FOREX and commodities and now you want to see it all in one place.

Well in this article we'll step through how to build a global watchlist in Google Sheets leveraging SheetsFinance. The watchlist runs off only a few batch functions and so the data loads in super fast. Not only that, but a bit of conditional formatting and some sparklines can give you a great 'at a glance' view on all the symbols you're tracking. Let's dive into it.

Getting Started

1. Make sure SheetsFinance is properly installed and linked to your google account, if not, you can find how to do so here.
2. Check out the free template and create a copy.
3. Want to learn through a video tutorial? Check out our Youtube Video.

Building the company information

Okay so our list of stock symbols lives in column A.

For our next few columns of our watchlist we want to see some more detailed company information. We can't always recognised a company from it's ticker symbol and if this list starts to get big, say 100 or 200 items it will start to get a bit confusing. So let's first generate the Name, Exchange, Sector and Country for each row.

To do this we use the Company Info Batch function. This is a batch function which means we can load data for multiple companies at once:

    =SF(A:A, “companyInfo”, “name&exchange&sector&country)
    

Of course, we could add more company information if we'd like, such as industry, website, CEO and more... You can read about all the available metrics on the company info docs page.

Adding an inline price and volume charts (sparkline)

When we look at our watchlist we want to, at a glance, have an idea of the performance of the stocks or assets we're tracking. So in the next column lets add a chart of the assets 1-year performance, highlighting losses in red and gains in green. We’ll also include a bar chart to track 1-month volume over time. These are generated with a combination of Google's in-built SPARKLINE in conjunction with the SheetsFiance SF_SPARK function.

IMPORTANT: The SF_SPARK function is not a batch function and therefore to display a chart for each row, you will need a separate function per row. Be aware of this if you're concerned about loading times or going over your Google Daily Quota.

So let's generate the 1-year price charts:

    SPARKLINE(SF_SPARK(A2,365,"price"))
    

And for the 30-day volume charts:

    SPARKLINE(SF_SPARK(A2,30,"volume"))
    

You'll notice I've added some conditional formatting to the price sparkline to highlight losses in red and gains in green. I've also added some conditional formatting to the volume sparkline to highlight stocks where the current current volume is above the daily average. This is a great way to quickly see which stocks are moving and which are not. I'm not going to dive into the specifics of how this is done in this article but you can watch and learn each step from the YouTube Walkthrough.

Price Changes

Next up in our mega watchlist we're interested to know how the stock or asset has been performing over certain intervals. For example, the stock may be up 20% in the last month (woohoo 🥳) but down 50% over the last year (oh 😢). To do this we can load in another super powerful batch SheetsFinance feature, the Price Change Batch function.

With one function we can load it the 1-month, 3-month, and 1-year price change of the asset relative to real-time (right now), like so:

    =SF(A:A,"change","1M&3M&1Y")
    

There are a number of other price change intervals to choose from but we'll keep it to these 3 for this watchlist.

Real-time data

Finally we're at the point where we want to see what's happening right this moment and while we're here we'd like a few fundamental ratios or metrics to helps us put the stock in context next to it's peers. Let's use the super powerful Real-time Batch to load in the current real-time price, the OHLCV (Open, High, Low, Close, Volume), average volume, market capitalization, Earnings Per Share (EPS), and the Price to Earnings (P/E) ratio. Here we go:

    =SF(A2:A51,"realTime","price&changesPercentage&open&dayHigh&dayLow&previousClose&volume&avgVolume&marketCap&eps&pe")
    

Amazingly, there are more real-time metrics we didn't include in this function, for the full list of available data items, please refer to the docs.

Where to from here?

If you're interested in knowing how to sort this list check out our Sortable Watchlist Template. Honestly, the only difference with this setup is that you load raw data into one sheet and then reference it in your main watchlist allowing the watchlist to be sortable without impacting your Batch Real-time function. So if you'd like to quickly be able to filter your list by price change, market cap and so on this template is for you.

Finding the correct symbols

If you aren’t sure what ticker to use for a certain company or asset, either use the Symbol Search tool within Google Sheets (Extensions > Sheets Finance > Symbol Search), or visit the SF Symbol Search page. You can also use the SF_MAP function to translate any ISIN, CUSIP or CIK code into the correct ticker used at SheetsFinance, for more information, kindly look through this documentation page.