Here’s a better way to Grab Stock, Fund, and ETF Prices from the Financial Times Website for your Google Spreadsheet (the FREE Script Code is Included).

Two months ago, I wrote 2 solutions to fix the most recent Yahoo Stock Price break in my Free Google Stock Portfolio Tracker to update readers on solutions to auto-update the stock prices of their investment portfolio tracker.

One of the solution is to try other platforms instead of Yahoo and I recommended getting the price data from Financial Times.

Personally, I have implemented my Financial Times solution because there is a wide database of securities that I would have price access to.

Financial Times is probably a good site if you have invested in a lot of funds such as the Dimensional Funds that I invested in.

However, a few days ago, readers have alerted me that the prices have stop updating. I have personally notice that as well.

You may see something like the following:

This must have cause some distress to some readers but given the amount of feedback that I gotten, not a lot of people are affected.

Why Does it Fail?

Google Sheets can prevent too many fetches of all the IMPORT functions.

This prevents people from commercially using this function to do heavy data lifting.

The ImportXML function we use falls within this problem.

If you delete the function and retype the function again, you will observe that the current function still work.

So what can we do to resolve this?

Fetch the Stock Prices from Financial Times via Code from App Script

We will have to code a function that does not use Google’s functions, making the fetching somewhat asynchronous.

Fortunately, I got a function for you to use.

I have included my function in the latest blueprint of my Stock Portfolio Tracker. You can download the latest one here.

By default, none of my sheets implements this function called fintime1(). But it should work.

Go to Extensions > Apps Script.

This would launch the script editor.

Add the following function to the script:

function fintime1(symbol) {
  symbol = symbol || "IE00BF20L762:SGD";
  
  symbol = encodeURI(symbol);
  Utilities.sleep(Math.floor(Math.random() * 5000))
  var url = 'https://markets.ft.com/data/etfs/tearsheet/summary?s='+symbol+''; 
  Logger.log(url);  

  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();
  if (responseCode === 200) {
    var html = response.getContentText();
    var content = html.match(/<span class="mod-ui-data-list__value">([\d.]*).*?<\/span>/)[0];
    var finContent = content.toString().replace(/(<([^>]+)>)/ig,""); ///(<([^>]+)>)/ig is a regex to remove bracket
    Logger.log(finContent);
    return Number(finContent);
  }else{
    Logger.log(parseFloat("fail"));
    returnvalue = ""; 
  }


  return "";

 
}

The code basically does the following:

1. Take in a stock symbol and try to get the Financial Times page where the price resides.
2. If we are able to successfully retrieve the webpage, we try to get the content.
3. In the content, try to match the span class that surround the price, with the help of regular expression.
4. Remove the surround HTML tag.
5. Convert the result to number and return the number.

With this you can implement fintime1() this way:

We want to retrieve the unit price of the fund Dimensional World Equity Acc SGD.

This successfully works.

After downloading the page above, we want to get the price of $30.41. Right-click and use the inspect tool help reveal the codes.

I have blue-boxed the span that fintime1 tries to grab.

Hope this solution works for you as well.

---

If you want to trade these stocks I mentioned, you can open an account with Interactive Brokers. Interactive Brokers is the leading low-cost and efficient broker I use and trust to invest & trade my holdings in Singapore, the United States, London Stock Exchange and Hong Kong Stock Exchange. They allow you to trade stocks, ETFs, options, futures, forex, bonds and funds worldwide from a single integrated account.

You can read more about my thoughts about Interactive Brokers in this Interactive Brokers Deep Dive Series, starting with how to create & fund your Interactive Brokers account easily.