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.

To report this post you need to login first.

27 Comments

You must be Logged on to comment or reply to a post.

  1. Kevin Small

    Interesting post, good stuff Aron.

    A question on the scheduling part you touched on at the end. I’ve read that at some point in the future some XS level scheduling is coming but I’d be interested to know what capabilities are there now to schedule XSJS routines, if any.

    I know cron can schedule procedures, but not XSJS as far as I know.

    (0) 
    1. Aron MacDonald Post author

      Thanks Kevin. I look forward to trying that in SP7.  The lines between HANA and BW are blurring with each release. Next thing you know they’ll be building some ETL tools for data staging in Native HANA.  🙂

      Perhaps I could write a small linux script to trigger a new XSJS function, e.g.

      curl -u username:password https://yyy.zzz.com:8000/FTSE_SIMPLE/GetLastFTSE.xsjs

      Then setup a cron job to schedule it.  Should be pretty easy in theory but I’ve a few other plans in mind.

      HADOOP Oozie is actually more of a Workflow tool.  A bit like a ‘Do it yourself’ version of BODS. Less UI more Code. A very big sledge hammer, complete over kill for the small amounts of data this will generate. 

      Which ever method I adopt, if I run it every minute it should still only generate 3 million records a month. I could probably run it for 5 years before even the smallest HANA box starts creaking.

      (0) 
    1. Aron MacDonald Post author

      Cool. If you do let me know how you get on.

      You’ve probably already seen but section 8.7 in the SPS 7 Developer User guide now  has a nice example loading/scheduling stock prices into HANA.

      (0) 
        1. Aron MacDonald Post author

          Hi Krishna,

          HANA XS/SAPUI5 can easily render external URL’s

          e.g. SAPUI5 SDK – Demo Kit

          You can also easily store static images in your XS repository and refer to them.

          If you wanted to store images dynamically in a HANA table (which would be an expensive storage medium) then you  probably could use BLOB fields.  You would then perhaps need to use javascript to convert back and forth, between reading, rendering and saving.

          Using HANA for that type of storage though probably wouldn’t make sense, but would make a fun exercise 😉

          Cheers

          Aron

          (0) 
            1. Aron MacDonald Post author

              Hi, 

              I use Google chrome to help debug issues.  Switch on Tools -> Developer tools and see what error you are getting in the Console tab.

              Cheers

              Aron

              (0) 
              1. Krishna Tangudu

                I don’t see any error.. i just see a blank page 🙁 when i call that index.html file. what ever is there in the BODY of the HTML comes up but i am trying to call the SAP UI view from the <HEAD> and i don’t why it is not referencing it .

                Can you help me?

                Regards,

                Krishna Tangudu

                (0) 
                1. Aron MacDonald Post author

                  Hi,

                  That’s odd if you are not seeing any error at all. I suggest you make a simple HTML page first in same package to confirm the Network, HANA XS and Authorisations are all ok in your environment.

                  Once you can confirm that works then, the next step is to check the HTML returned to browser when you select index.html

                  Cheers

                  Aron

                  (0) 
                2. Krishna Tangudu

                  Hi Aron,

                  If i write my name in the index.html it is coming up but i don know why it is not calling the SAP UI5 view.

                  So any logo or name i keep in the HTML file are coming up in the browser but UI View is not getting invoked.

                  Is there any authorizations you know which are required if i have missed something?

                  Regards,

                  Krishna Tangudu

                  (0) 
  2. Krishna Tangudu

    Hi Vivek,

    Yes am using the same code mentioned in that blog.

    PFA

    sap.ui.jsview(“testui.TestUI”, {

      /** Specifies the Controller belonging to this View.

      * In the case that it is not implemented, or that “null” is returned, this View does not have a Controller.

      * @memberOf testui.TestUI

      */

      getControllerName : function() {

      return “testui.TestUI”;

      },

      /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.

      * Since the Controller is given to this method, its event handlers can be attached right away.

      * @memberOf testui.TestUI

      */

      createContent : function(oController)

      {

      return alert(‘createCallback returned successfully’);

          

      /*var oModel = new sap.ui.model.odata.ODataModel(“/TwitterTest/services/TwitterTest.xsodata/”,false);

      oTable = new sap.ui.table.Table(“Test”,{visibleRowCount:5});

      oTable.setTitle(“Test Details”);

      var testId = new sap.ui.table.Column({label: new sap.ui.commons.Label({text: “TestId”}),               

                         template: new sap.ui.commons.TextView().bindProperty(“text”, “TestId”)  ,

                          width: “100%”});

      oTable.addColumn(testId);

      var testName = new sap.ui.table.Column({label: new sap.ui.commons.Label({text: “TestName”}),               

                             template: new sap.ui.commons.TextView().bindProperty(“text”, “TestName”)  ,

                              width: “100%”});

      oTable.addColumn(testName);

      oTable.setModel(oModel);

      oTable.bindRows(“/TwitterTest”);

      return oTable*/;

      }

    });

    Regards,

    Krishna Tangudu

    (0) 
    1. Aron MacDonald Post author

      Hi,

      Can you please confirm you you changed the ‘sap-ui-core.js’   line in the html to point to your HANA box?

      Also If you are new to XS development you might want to try a slightly easier one I posted on SCN first. SAPTechEd2013 USB Explorer

      That one should work without redirecting to your HANA box, as it uses the public SAPUI5 js scripts.

      If you are still getting stuck I suggest you use Google chrome (or suitable equiv) to breakpoint and step through the Javascript.   The code in this example is very short so you should be able to quickly finds where it stops.

      Cheers

      Aron

      (0) 
      1. Aron MacDonald Post author

        Also can you confirm the table FTSE schema and FTSE_LATEST table were created when you activated the objects in the repository.

        You should see them in the catalog.

        Do you mind attaching a screen shot of your workspace showing the active objects, similar to mine above, in the “project explorer”  tab, of HANA Development perspective.

        Cheers

        Aron

        (0) 
      2. Krishna Tangudu

        Thanks Aron for your reply.

        Can you please confirm you you changed the ‘sap-ui-core.js’   line in the html to point to your HANA box?

        I think the problem is with locating that file. I changed it but however it is not identifying it. When i sued external link:         src=”https://sapui5.netweaver.ondemand.com/resources/sap-ui-core.js

        atleast UI elements worked. HANA side data didn’t popup.

        I was wondering what i need to do to correct the installation on the AWS system i am accessing.

        Regards,

        Krishna Tangudu

        (0) 
      3. Krishna Tangudu

        Thanks Aron for your reply.

        Can you please confirm you you changed the ‘sap-ui-core.js’   line in the html to point to your HANA box?

        I think the problem is with locating that file. I changed it but however it is not identifying it. When i sued external link:         src=”https://sapui5.netweaver.ondemand.com/resources/sap-ui-core.js

        atleast UI elements worked. HANA side data didn’t popup.

        I was wondering what i need to do to correct the installation on the AWS system i am accessing.

        Regards,

        Krishna Tangudu

        (0) 
            1. Vivek Singh Bhoj

              I guess something is wrong with UI5 installation

              Don’t know why you are not able to download UI5 files from HANA on demand tools- as it works perfectly fine for me

              Regards,

              Vivek

              (0) 
              1. Krishna Tangudu

                Do you think as the UI5 is already installed ( Seems to be faulty) so to reinstall.. what is the best way? is there some thing like “uninstall” and “install” or “Repair”? Any inputs on those lines?

                Regards,

                Krishna Tangudu

                (0) 
                1. Aron MacDonald Post author

                  Hi,

                  Perhaps I’ve just been lucky, but SAPUI5 has worked straight out of the box for me on HANA, so I’m not sure about Uninstall or Repair options on the server. I think there is a config setting to enable HANA XS. I think it is ‘on’ by default for new installs, but perhaps if you are working on a shared box perhaps someone may have disabled it. Many of HANA’s new optional features (e.g. XML editor) will probably need this ‘on’.

                  Sometimes it can be a little tricky at first pointing to the correct location of ‘sap-ui-core.js‘ as the path may differ on your HANA box.  Also if you are pointing to it ‘by reference’ you need to be aware of your own package structure to correctly navigate back to it,  e.g. ‘../../sap/ui5/1/resources/sap-ui-core.js’.

                  It might be a little out of date but this is a great intro to SAPUI5 development on HANA by Thomas Jung.

                  It walks through the basics of installing the HANA Studio SAPUI5 addon as well as a simple Hello world application http://www.saphana.com/docs/DOC-3034

                  I hope you get your SAPUI5 up an running.

                  Cheers

                  Aron

                  (0) 
                  1. Krishna Tangudu

                    Thanks Aron for info. I will try them..

                    I had many use cases to try and got struck with this issue as with other link to sap-ui-core.js i can get UI elements but cannot again connect to HANA server to test XS 🙁

                    Regards,

                    Krishna Tangudu

                    (0) 

Leave a Reply