Posted by CANbike on Fri, 15 Feb 2013

Stock Quotes for LibreOffice and OpenOffice

Spreadsheets provide a nice simple way to manage and track stock portfolios. However, the open source spreadsheets provide very limited options for automatic stock quotes compared to Microsoft Excel.

Automated downloading of stock quotes to a CSV file can provides an alternative method to get quote data into a spreadsheet.

The following is based on a Linux environment, but similar programs and equivalent scripts are available for Windows.

The Short Version

getquotes.sh

#!/bin/sh
curl -s 'http://download.finance.yahoo.com/d/quotes.csv?s=USDCAD=X,BCE.TO,CSCO,MSFT,INTC&f=sl1d1t1c1ohgv&e=.csv' | tee /path/to/quotes.csv

stocks.sh

#!/bin/sh
xterm -hold -e /path/to/getquotes.sh
libreoffice3.5 /path/to/Portfolio.ods

LibreOffice / OpenOffice Function Example

=VLOOKUP("INTC",'file:///path/to/quotes.csv'#$Sheet1.$A$1:$B$5,2,0)

The Long Version

The Old Ways to Get Stock Quotes

The two most common options were to either use a plugin/addon or to use the external data function. Both methods have become outdated and no longer work.

The plugin/addon method relies on the author to update the code every time the spreadsheet version is updated or the source of the data (Yahoo! Finance) changes it’s website layout. In addition, the plugins/addon tend to be slow and quirky as they scrape data off of finance websites.

The spreadsheets built-in external data function use to work well with finance websites, but with advancement of websites (html, php, scripting, dynamic content, etc), these function are no longer able to scrape the data off the website.

Automatic Stock Quotes Via CSV File

A CSV file approach is preferred as almost any modern spreadsheet program is able to import and read CSV data. A comma-separated values (CSV) is a plain text file of tabular data separated by line breaks, and a comma or a tab. This would make the stock quote data accessible to any modern spreadsheet program. i.e. LibreOffice, OpenOffice, and Gnumeric.

International Stock Quotes Downloaded to a CSV File

Yahoo! Finance is great as they provide stock quotes for many international indices. This is important as a Canadian who invests in stocks listed in the TSX, Nasdaq, NYSE, AMEX, etc.

In addition, Yahoo! Finance makes it easy to download stock quotes as a CSV file.

The link often looks like this

http://download.finance.yahoo.com/d/quotes.csv?s=BCE.TO,CSCO,MSFT,INTC&f=sl1d1t1c1ohgv&e=.csv

The above would download quotes for BCE (TSX: BCE), CSCO (Nasdaq: Cisco), MSFT (Nasdaq: Microsoft), INTC (Nasdaq: Intel) to a CSV file “quotes.csv”.

“curl” and “tee” to Help Automate the Downloading of “quotes.csv”

It would be inconvenient to type in the URL or even go to Yahoo! Finance Portfolio’s to download the CSV file every time quotes are needed.

The command curl and tee can help to automate the process of downloading stock quotes.

curl -s 'http://download.finance.yahoo.com/d/quotes.csv?s=USDCAD=X,BCE.TO,CSCO,MSFT,INTC&f=sl1d1t1c1ohgv&e=.csv' | tee /path/to/quotes.csv

According to the man page, curl

curl is a tool to transfer data from or to a server, using one of the supported protocols (HTTP, HTTPS, FTP, FTPS, SCP, SFTP, TFTP, DICT, TELNET, LDAP or FILE). The command is designed to work without user interaction.
curl offers a busload of useful tricks like proxy support, user authentication, FTP upload, HTTP post, SSL connections, cookies, file transfer resume and more. As you will see below, the number of features will make your head spin!
curl is powered by libcurl for all transfer-related features.

and the -s option

Silent or quiet mode. Don't show progress meter or error messages. Makes Curl mute.

This data is then piped ( “|” ) to the tee command which reads the input and writes it out to a file and a standard output. In this case, the file “quotes.csv” is written to the linux directory “/path/to/”. The tee command is used as I like both a visual confirmation of the stock quote data and for the data to be written to a file.

As an alternative, the redirect command “>” can be used instead if the user wants the data to be only written to a file.

curl -s 'http://download.finance.yahoo.com/d/quotes.csv?s=USDCAD=X,BCE.TO,CSCO,MSFT,INTC&f=sl1d1t1c1ohgv&e=.csv' > /path/to/quotes.csv

The Script

The above command is then saved into the shell script file “getquote.sh”.

getquotes.sh

#!/bin/sh
curl -s 'http://download.finance.yahoo.com/d/quotes.csv?s=USDCAD=X,BCE.TO,CSCO,MSFT,INTC&f=sl1d1t1c1ohgv&e=.csv' | tee /path/to/quotes.csv

Whenever additional stocks quotes are required, just add them to the list of comma separated ticker symbols. For example, the addition of Apple, ticker symbol AAPL, would look like this.

#!/bin/sh
curl -s 'http://download.finance.yahoo.com/d/quotes.csv?s=USDCAD=X,BCE.TO,CSCO,MSFT,INTC,AAPL&f=sl1d1t1c1ohgv&e=.csv' | tee /root/my-documents/quotes.csv

Now there are two things that can be done with this file. One, is to execute the script every time stock quotes are needed. Or two, set up a cron job to execute the script every 15 minutes. I prefer method one, except I’ve set it up to automatically execute and import the stock quote data when opening my spreadsheet file.

LibreOffice / OpenOffice

Currently, I use LibreOffice for portfolio tracking. Outline below is the process for automatically importing the stock quotes. Previously, I used OpenOffice before it was acquired by Apache. The process and commands would work in a similar manner.

There are two parts to the LibreOffice process.

  • Part 1: Automatically update the stock data when opening the main “Portfolio.ods” spreadsheet
  • Part 2: Importing the stock quote data into the main spreadsheet

Part 1: Update Stock Quotes

This part involves the creation of another shell script file. I called it “stocks.sh” and placed it as an icon on the desktop.

stocks.sh

#!/bin/sh
xterm -hold -e /path/to/getquotes.sh
libreoffice3.5 /path/to/Portfolio.ods

Instead of directly executing the script “getquotes.sh”, I use xterm to get a visual confirmation of the stock quotes.

According to the man page the -hold option

Turn on the hold resource, i.e., xterm will not immediately destroy its window when the shell command completes. It will wait until you use the window manager to destroy/kill the window, or if you use the menu entries that send a signal, e.g., HUP or KILL. 

and the -e option

This option specifies the program (and its command line arguments) to be run in the xterm window. It also sets the window title and icon name to be the basename of the program being executed if neither -T nor -n are given on the command line. This must be the last option on the command line.

After the user closes the prompt or presses enter, LibreOffice is executed and opens the file “Portfolio.ods”.

Part 2: Import Stock Data

LibreOffice has a built in function VLOOKUP for vertical searches with reference to adjacent cells to the right. Moreover the function can be used to lookup data in another spreadsheet file like “quotes.csv”.

=VLOOKUP(SearchCriterion; Array; Index; SortOrder)

where

SearchCriterion is the value to search for.
Array is the grid to search for the value and pull data from.
Index is the column of the data to return.
SortOrder denotes if the first column is sorted. Zero indicates the search column is in random order.

For example, if a stock quote for INTC was needed then the following could be entered into a spreadsheet cell,

=VLOOKUP("INTC",'file:///path/to/quotes.csv'#$Sheet1.$A$1:$B$5,2,0)

where

SearchCriterion = "INTC"
The ticker symbol INTC, is the search term and specified as a string value via the double quotes.

Array = 'file:///path/to/quotes.csv'#$Sheet1.$A$1:$B$100
The external spreadsheet file, 'file:///path/to/quotes.csv', contains the data. The search area, #$Sheet1.$A$1:$B$100, is a 2×100 array to retrieve values from.

Index = 2
Is the column number to retrieve data from. Column 2 in “quotes.csv”, contains the stock prices.

SortOrder = 0
The search area is an unordered list and specified by the value zero.

Once entered, the import feature for the external spreadsheet will automatically load. Since it’s a standard CSV file, the default setting should suffice.

LibreOffice Final Notes

When the shell script stocks.sh is executed or double clicked, new stock quotes are saved to the “quotes.csv” file. A prompt with the data is displayed, waiting for the user to click enter. Afterwards LibreOffice will launch and open the file “Portfolio.ods”. There will be a prompt about updating the spreadsheet as it refers to external data that has changed. Once confirmed, stock prices will automatically be updated into the spreadsheet.

Should additional ticker symbols be required, update getquotes.sh. It’s a simple list of ticker symbols separated by a comma. However, if the number of symbols increased beyond 100, then also update the function call VLOOKUP’s array to reflect the expanded search area.

After executing “stocks.sh”, LibreOffice only requires two extra presses of the “Enter” key to get stock quotes automatically updated in the spreadsheet. One prompt to confirm the import of data from an external file, and two to confirm the format of the import data.


Related Item(s):