yahooticker - A LibreOffice Calc Add-in


yahooticker - A LibreOffice Calc Addin

The add-in itself, an .oxt file, which is essentially just a renamed .zip file, consist of the following five files:

1. description.xml

2. manifest.xml

3. ytCalcAddIn.xcu

4. XYahooTicker.idl

5. yahooticker.py


description.xml

<?xml version="1.0" encoding="UTF-8"?>
<description xmlns="http://openoffice.org/extensions/description/2006" 
xmlns:d="http://openoffice.org/extensions/description/2006" 
xmlns:xlink="http://www.w3.org/1999/xlink">
<dependencies>
	<OpenOffice.org-minimal-version value="2.4" d:name="OpenOffice.org 2.4"/>
</dependencies>
<identifier value="com.olivermahmoudi.programming.yahooticker" />
<version value="0.1" />
<display-name><name lang="en">Yahoo Stock Ticker Function.</name></display-name>
<publisher><name xlink:href="http://www.olivermahmoudi.com/programming/yahooticker" lang="en">Oliver Mahmoudi</name></publisher>
</description>

META-INF/manifest.xml

<manifest:manifest>
<manifest:file-entry manifest:media-type="application/vnd.sun.star.uno-typelibrary;type=RDB" 
	manifest:full-path="XYahooTicker.rdb"/>
<manifest:file-entry manifest:media-type="application/vnd.sun.star.configuration-data" 
	manifest:full-path="ytCalcAddIn.xcu"/>
<manifest:file-entry manifest:media-type="application/vnd.sun.star.uno-component;type=Python" 
	manifest:full-path="yahooticker.py"/>
</manifest:manifest>

ytCalcAddIn.xcu

<?xml version="1.0" encoding="UTF-8"?>
<oor:component-data xmlns:oor="http://openoffice.org/2001/registry" xmlns:xs="http://www.w3.org/2001/XMLSchema" oor:name="CalcAddIns" oor:package="org.openoffice.Office">
<node oor:name="AddInInfo">
<node oor:name="com.olivermahmoudi.programming.yahooticker.python.YahooTickerClass" oor:op="replace">
<node oor:name="AddInFunctions">
<node oor:name="yahooticker" oor:op="replace">
	<prop oor:name="DisplayName"><value xml:lang="en">yahooticker</value></prop>
	<prop oor:name="Description"><value xml:lang="en">Returns the current market price of the instrument that is passed to the function.</value></prop>
    <prop oor:name="Category"><value>Add-In</value></prop>
    <prop oor:name="CompatibilityName"><value xml:lang="en">AutoAddIn.YahooTicker.yahooticker</value></prop>

		<node oor:name="Parameters">
			<node oor:name="instrument" oor:op="replace">
	        <prop oor:name="DisplayName"><value xml:lang="en">instrument</value></prop>
    	    <prop oor:name="Description"><value xml:lang="en">Instrument to query.</value></prop>
  	    	</node>
		</node>
</node>
</node>
</node>
</node>
</oor:component-data>

XYahooTicker.idl

#include <com/sun/star/uno/XInterface.idl>

module com { module olivermahmoudi { module programming { module yahooticker {

    interface XYahooTicker
    {
      double yahooticker( [in] string instrument );
    };

}; }; }; };

And here goes the python source code:

yahooticker.py

import uno
import unohelper
import requests
import re

from com.olivermahmoudi.programming.yahooticker import XYahooTicker

class YahooTickerClass( unohelper.Base, XYahooTicker ):
	def __init__( self, ctx ):
		self.ctx = ctx

	def yahooticker( self, instrument ):
		"Query yahoo finance with instrument return instrument's current market value"
		yahoo_base_url = "http://finance.yahoo.com/quote/"
		url_to_query = yahoo_base_url + instrument
		try:
			ws = requests.get(url_to_query)
		except requests.exceptions.RequestException as e:
			print e
			sys.exit(1)
		content = ws.text
		regex = re.compile(r"<!--\sreact-text:\s36\s-->[0-9]+,?[0-9]*\.[0-9]+<!--\s/react-text\s-->")
		result = regex.search(content)
		if result:							# We have found something...
			regex = re.compile(r"[0-9]+,?[0-9]*\.[0-9]+")	        # Extract
			result = regex.search(result.group(0))			# the price
			return result.group(0)				        # and return it...
		else:
			return "Nothing found"					# Futile search
 
def createInstance( ctx ):
	return YahooTickerClass( ctx )

g_ImplementationHelper = unohelper.ImplementationHelper()
g_ImplementationHelper.addImplementation( \
	createInstance,"com.olivermahmoudi.programming.yahooticker.python.YahooTickerClass",("com.sun.star.sheet.AddIn",),)

If you want to compile the above files yourself, you will need the LibreOffice SDK. This can either be obtained on the LibreOffice homepage or via your package manager.

To initiate the sdk, you will need to run a number of scripts, provided by the sdk itself, to initiate the environment. Depending on where you obtained the sdk from, these scripts may be in a different location on your computer. On my CentOS7 system, these scripts happen to be in the following locations:

/usr/lib64/libreoffice/sdk/setsdkenv_unix.sh

and

/usr/lib64/libreoffice/sdk/setsdkenv_unix

These steps are necessary, as you will need the idlc and regmerge utlilities to compile the idl file into an .rdb file.

For the sake of convenience, I've combined the two sdk initiation steps and put them into the following short shell script:

initiate_lo_sdk.sh

#!/bin/sh

# Initiate the libreoffice sdk
/usr/lib64/libreoffice/sdk/setsdkenv_unix.sh
/usr/lib64/libreoffice/sdk/setsdkenv_unix

exit 0

Now we can "compile" the add-in itself. As noted above, we will need to make use of the zip utility and later on rename the archive to and .oxt file, which can in turn be registered with LibreOffice.

The following script takes care of both, generating the .rdb file and creating the add-in itself.

generate_oxt.sh

#!/bin/sh

# Compile the .rdb file.
idlc -I /usr/lib64/libreoffice/sdk/idl idl/XYahooTicker.idl
regmerge idl/XYahooTicker.rdb /UCR idl/XYahooTicker.urd

# Delete the .urd file, as it's just of temporary need.
rm -v idl/XYahooTicker.urd

# Move the .rdb file to its destination.
mv -i idl/XYahooTicker.rdb ./

# In case there is an old .oxt file delete it.
rm -vf yahooticker.oxt

# zip the source files.
zip yahooticker.zip \
	META-INF/manifest.xml \
	ytCalcAddIn.xcu \
	XYahooTicker.rdb \
	description.xml \
	yahooticker.py

# An .oxt file is actually just a .zip file.
mv -i yahooticker.zip yahooticker.oxt

exit 0

Running the last script, generates the add-in called yahooticker.oxt

To register the add-in with the system, open LibreOffice and go to Tools -> Extension Manager -> Add... and open the file yahooticker.oxt. This will enable the function: YAHOOTICKER in Calc, which can be used to obtain the current market value of your favourite instrument.