Get data from a site into Google docs

Posted by Strainu on January 29, 2021
Personal / No Comments
Disclaimer: Data and text in websites is protected by copyright unless specified otherwise. Use this guide only under the fair use limitations in your country.

A question that I often receive when I talk about my spreadsheets is how do I get data directly into Google docs, so I’ve put together this tutorial on how to get Romanian stock prices. We’re going to use Tradeville‘s website to do that. As usual, the published data is delayed, but it’s good enough for end of day tracking.

1. Identify the data you need

From the homepage, you can use the search box in the upper-right corner to search for a ticker symbol (e.g. BRD). This will get you to a page like the one below. In that page, identify the URL (in the address bar of your browser) and the price.

Picture 1

2. Get the data

Now, you need a bit of tech knowledge: you need to open the developer tools in your browser. Fear not, in most browser you can use the Ctrl+Shift+I key combination (Cmd+Shift+I on Mac) to open it. You can see how that looks like in Firefox in the picture below.

Click the little arrow in the top-left corner (in red towards the middle of the picture), then select the element that contains the price. In the code pane, the equivalent line of code is selected (the red highlight at the bottom of the picture 2).

Picture 2
Picture 3

You’re almost done: right click the line of code in picture 3, then look for the command that allows you to copy the XPath of the element. In Firefox, that’s Copy->XPath.

3. Use the data in Google docs

Not that you have the path, you can use the information you got above (the URL of the page and the XPath of the element containing the price) in the IMPORTXML function in Google Spreadsheets. Just put in any cell the following function call:


Let’s see how that looks:

Picture 4

Ooops! Looks like we’re not quite there yet! Google returns 150.4 instead of 15.04. That’s actually because of the encoding differences between English and Romanian (the dot as a thousands separator in Romanian vs decimal separator in English) and how the Google code interprets the data. It can happen that Google considers the data as a date, which will leave you with a totally unusable value.

I will leave the solution to this problem as a task for the reader, since it might be different for different symbols. I personally chose a more complex formula including parsing more of the page using regular expressions, just because it was more resilient to small site changes.

If this guide helped you, please leave a comment or share it!

Share and Enjoy:
  • Facebook
  • Twitter
  • LinkedIn
  • Reddit
  • Add to favorites

Tags: , , ,

BSE stock prices in GNUCash

Posted by Strainu on April 06, 2009
My Projects, Perl, Software / 4 Comments

The "Add security" Window

Fig. 1: The Add Security window in GNUCash

GNUCash has a very good automatic price retreival utility, based on the Perl Finance::Quote library. Unfortunately, the sites it is using to get the data from don’t have any quotes from the Romanian markets.

Therefore, I decided to write my own modules, and I started with the Bucharest Stock Exchange, since their data was reliable and available in a accessible format.

Here is how you can add this package to your own system:

  1. Install Perl. If you’re using Linux, you probably already have it. Write perl -v to find out the version you have. If you’re using Windows, ActivePerl or Strawberry Perl should do the trick.
  2. Install Finance::Quote. GNUCash offers an automated program to do that.
    1. Make sure GNUCash is not running.
    2. In Linux, open a root shell and write update-finance-quote. In Windows, go to the start menu and you will find a shortcut to a BAT file called “Install Online Price Retreival”.
  3. For now, the script is not yet included in Finance::Quote and must be installed separately.
    1. Download the provided archive and extract it along with the rest of the Perl scripts for Finance::Quote (you will see the download path during the installation). Make sure the Finance folder overwrites the current folder.
    2. In the Finance folder you will find 2 files named and You can apply the patch by using the command

      On Windows, just open the file in Notepad, search for a line that begins with @modules = and add BSERO to the list of modules.

  4. Start GNUCash and go the the Tools->Security Editor menu. You will see a window similar to the one on the left side of Fig. 1.
  5. Click on the Add button. A new window, just like the one on the right side of Fig. 1 will appear. Let’s say that you want to get the stock price for Banca Românâ pentru Dezvoltare (symbol: BRD). You add the following information in that window:
    1. Full Name: Banca Română pentru Dezvoltare
    2. Symbol: BRD (this is very important, as this is the search criteria)
    3. Type: BSE (or BVB, or whatever you want)
    4. Check the Get Online Quotes checkbox. Three new options will be activated.
    5. Automatic price retreival

      Fig.2: Automatic price retreival

      Choose Type of quote source: Unknown

    6. From the drop-down choose bsero.
    7. Click OK twice and you’re ready to test it.
  6. Go to the Tools->Price Editor menu. You will see the dialog in Fig. 2. Click the Get Quotes button and you should see a new price appearing. You can notice the source is “Finance::Quote” and the price is the correct one for that date. If you wish, you can check it on the BSE website.
Share and Enjoy:

  • Facebook
  • Twitter
  • LinkedIn
  • Reddit
  • Add to favorites

Tags: , , , , ,