Google sheet - How to get stock prices from websites using ImportXml and scraping - updated 2022

It is no secret that I like to spend some time on investing, buying and selling stocks and index funds. I use Google sheets in order to keep track of my portfolio. Using Google spreadsheets you can get most prices using the built-in function =GOOGLEFINANCE(<insert stockticker>), for example: =GOOGLEFINANCE("CPH:GEN"). I describe this in detail in another post of mine.

However some markets are not supported by this for example:

  • Oslo stock exchange (Norwegian stocks)
  • Spotlight (Aktietorget - Sweden)
  • First north (Danish stocks)
  • Nordic funds
  • US penny stocks

Therefore I decided to try and scrape this off external sites using ImportXml.

You can find a working examples of the below here. It contains several examples of different websites and types of stocks!

How get stock prices using ImportXml

I first tried my favourite website for charts and quotes tradingview. However they use websockets and load the data asynchronously. Therefore I could not scrape the data from their website as it is not present when the scraper gets the HTML. Other sites like marketscreener or yahoo have the stock prices as part of the initial HTML and their sites can therefore be scraped. In this example I use marketscreener.com. It is a good idea to find a site that has all the funds you wish to scrape. Even though you of course can get them from different sites.

The fund I started with was "Danske Invest fjernøsten" (not a recommendation for or against). I used the following to import the quotes:

IMPORTXML("https://www.marketscreener.com/quote/etf/DANSKE-INVEST-FJERN-STEN-19156377/";"//td[@class='fvPrice colorBlack']")

The part of the HTML where the price was located, looked like the following:

<td style="padding-bottom:5px" class="fvPrice colorBlack" id="zbjsfv_dr">154.55</td>

The current price as I am writing this is "154.55" as seen in the above XML. The xpath "//td[@class='fvPrice colorBlack']" used to find the price, reads like the following: "Find any td element with the classes fvPrice and colorBlack". I could also have used the Id: "//td[@id='zbjsfv_dr']". This is enough to import the data into your Google spreadsheet.

Depending on your locale it may look odd with extra .00 after it or it looking like a completely different number. In some countries a dot (.) sets the decimal place and in others a comma (,) is used. This can cause issues if the site uses a different format than your spreadsheet. Google spreadsheets will maybe (depending on your settings) add the decimals if it believes they are missing, which gives the extra .00.

I therefore ended up trimming any ".00" and then converting the dot to a comma. This I do using the SUBSTITUTE method twice as seen below:

=SUBSTITUTE(SUBSTITUTE(IMPORTXML("https://www.marketscreener.com/quote/etf/DANSKE-INVEST-FJERN-STEN-19156377/";"//td[@class='fvPrice colorBlack']"); ".00"; ""); "."; ",")

Pretty? no, but that is what you get when you try to scrape data off websites. I should warn that these sort of "integrations" can break if the site you are importing from changes their HTML. If for example they change the classes or id on the element you get the price from, you will get an error.

Below is the end result:

Google-spreadsheet-get-stock-price-import-xml

Finding Danish index funds

The function also work for other funds, such as "Sparindex index Japan Value KL" (not a recommendation for or against):

=SUBSTITUTE(SUBSTITUTE(IMPORTXML("
https://www.marketscreener.com/quote/etf/SPARINDEX-INDEX-JAPAN-VAL-19156245/";"//td[@class='fvPrice colorBlack']"); ".00"; ""); "."; ",")

Which gives the current price: "97,28" as I am writing this.

Finding Norwegian stocks

Another type of stocks not found in the Google finance API are Norwegian stocks. Prices for these can also be found using ImportXml, such as "Equinor" (not a recommendation for or against):

=SUBSTITUTE(SUBSTITUTE(IMPORTXML(" https://www.marketscreener.com/quote/stock/EQUINOR-ASA-1413290/","//td[@class='fvPrice colorBlack']"), ".00", ""), ".", ",")

This gives the price "134,35" as I am writing this.

Finding stocks on Nordnet

Based on the chat below I have added the following example for getting quotes from Nordnet:

=IMPORTXML("https://www.nordnet.dk/markedet/aktiekurser/17043840-danish-aerospace-company","(//span[contains(@class, 'epuleM')])[1]")

However their classes seem auto-generated to me, so it might break. Anyway it might serve as inspiration on how to get the first occurrence of a class in the HTML.

This gives the price "5,36" for Danish Aerospace as I am writing this.

That is it

You can find a working examples of the above here.

This is my way of importing stock prices from external websites. I hope this can help you to have less to update manually in your spreadsheets! The spreadsheets of course update these values automatically. Leave a comment down below if you found this helpful - please :) You may also leave a comment if you want help with scraping prices off other websites than mentioned in this post.