Skip to Content

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:

use fintime1(stockcode) in the cell

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.

Kyith

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Simon

Tuesday 26th of March 2024

Hi again with an update, I used Co-Pilot to help me modify the function and added a bit of code to remove the comma like this: var finContent = content.toString().replace(/(]+)>)/ig, "").replace(/,/g, ""); It's fine now but a bit scary how easy AI makes it to do these things.

Simon

Wednesday 27th of March 2024

@Kyith, It works for me but sometimes stays on 'loading data' for quite a while. Here is the whole thing as I have it:

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(/([\d.]*).*?/)[0]; var finContent = content.toString().replace(/(]+)>)/ig, "").replace(/,/g, ""); Logger.log(finContent); return Number(finContent); }else{ Logger.log(parseFloat("fail")); returnvalue = ""; }

return "";

}

Kyith

Wednesday 27th of March 2024

Hi Simon, I tried putting in the codes but it doesn't seem to work. there is a syntax error.

Kyith

Wednesday 27th of March 2024

hi Simon! thank you so much! Yes it can get quite scary. Let me modify it and let readers know.

Simon

Tuesday 26th of March 2024

Hi Kyith, Thanks. I find this works for prices that don't have a comma for thousands included, but for example, for Microstrategy MSTR:NSQ, with a price like 1,856.00 I get a #NUM! error. I think there may be a problem with the regular expression in the function but I don't have the wherewithal to work it out!

cm

Thursday 21st of March 2024

Kyith. Thanks for the nice solution. Works for me. Really appreciate your sharing.

Added some code to scrap a few other financial sites using the same idea: https://docs.google.com/spreadsheets/d/1L7r2HEXvHHshV4K_PUUYPvgjMXgq7be01S4PzfQWE00/edit?usp=sharing

Kyith

Thursday 21st of March 2024

Hi CM Thanks!

Keith

Wednesday 20th of March 2024

Thank you so much for this sharing!! It works for me. Your excel had been life-changing as it helped me tracked my portfolio properly.

Kyith

Thursday 21st of March 2024

Hi Keith, that is ok happy it works for you.

This site uses Akismet to reduce spam. Learn how your comment data is processed.