Latest Share Prices: Google Finance & ODATA CRUD

Inspired by http://ftse.richardallen.co.uk/  I thought it would by interesting to use  Google Spreadsheet & Google Finance Functions  to publish a JSON feed of the latest share prices (or at least accurate to within 20 minutes), which can then be loaded into HANA using XS, SAPUI5 & ODATA CRUD.

The Refresh button reads the latest information from Google spreadsheet.

The Save button writes it to HANA.

Here is a link to the complete Project/Package I created (FTSE_SIMPLE.rar):

https://drive.google.com/file/d/0Bxydpie8Km_fb3NBSUh5Z1Q2OVU/edit?usp=sharing

Please feel free to try and load and run in your own HANA environment.

Create the JSON Feed:

—————————-

1) Create a spreadhseet in Google Drive

https://docs.google.com/spreadsheet/ccc?key=0Ahydpie8Km_fdFRwajhOWTRsNGUtMWZROXF5a1pLN3c&usp=sharing

2) Use Google Finance Functions to get the latest share prices (within 20 minutes)

Example Cell(F2) Formula for Current Price:

=GoogleFinance(E2,”price”)

Example Cell(F2) Formula for Day Change:

=GoogleFinance(E2,”change”)

3) Publish The Google Spreadsheet to the Web

The following JSON link can then be used.

https://spreadsheets.google.com/feeds/list/0Ahydpie8Km_fdFRwajhOWTRsNGUtMWZROXF5a1pLN3c/od6/public/basic?alt=json

All the code is provided in the earlier link and there is now plenty of training videos on HANA XS development so I won’t bore you with the details of creating a table, role, SAPUI5 view etc.  Below are the objects included in the ‘FTSE_SIMPLE’ package.

The key points within the HANA XS build are:

——————————————————-

1) A Function which reads the JSON link, and re-formats it a bit to enable it to be linked (bind) to SAPUI5 Table.

    See FTSE_functions.js -> getFTSE(dataStocks) function.

2) An ODATA service with custom create procedure for saving

Note: I didn’t strictly need the create procedure to use ODATA to create records into table ‘FTSE_LATEST’, but wanted to have flexibility to add custom validations and additional logic.

3)  Using the Model.create statement to write individual entries back to HANA table using ODATA Create, rather than an Insert statement.

    See See FTSE_functions.js -> saveFTSE(dataStocks, oModel)  function.

The benefit of using ODATA Create is that all the save operations can be encapsulated, and controlled, rather than using direct SQL inserts statements.

It can’t be used for insert large volumes of record (e.g. as you may find with real-time stock market feeds from Rueters or Bloomberg) however in this example it’s fine.

While this example has been interesting, in practice I wouldn’t want to click ‘Refresh‘ and ‘Save‘ all the time if I wanted to start tracking Stock market changes over time.

What I need to do now is schedule some of the operations to run.

I could of course use:

-BODS

-BW on HANA Batch scheduling

-Server scheduled tasks

My plan though is to use HADOOP Oozie to schedule the loads into HANA. 

Why crack an egg with with a teaspoon when you have a sledge hammer. It’s definitely more fun. 🙂

For another example of using Google spreadsheets & JSON see:

The SAP TechEd Amsterdam 2013 Mobile App – built on SAPUI5 and SAP HANA Cloud

Update SPS7:  Now with SPS7 there is  a JOB scheduling tool for HANA.

See Section 8.7 of the SPS 7: SAP_HANA_Developer_Guide_en.pdf it now has a great alternative for loading Share prices, with example code.