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.
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.
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
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.
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.
I see, I didn't think of using cURL to kick off the XSJS, thanks for the tip Aron.
For those interested I've now created a few programs that enable this to be scheduled via HADOOP Oozie.
I've posted details on:
Creating a HANA Workflow using HADOOP Oozie
Nice blog Aron
Will give this a try
Regards,
Vivek
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.
Aron
Will HANA support loading of Images as well? from external URL links?
Regards,
Krishna Tangudu
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
Thanks for the input Aron. Will have a try on this 🙂
Regards,
Krishna Tangudu
Hi Aron,
I was trying this link
Native development in SAP HANA and consuming the oData services in SAPUI5
I was able to generate JSON output using oDATA but when am trying to use UI5 to display as table. I did the same mentioned in the blog but am unable to get any output in the browser, all i can see is what ever i keep in the <body>.
Am i missing anything here?
Regards,
Krishna Tangudu
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
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
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
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
Hi Krishna,
Are you using the same code as mentioned in that blog or a different one?
If yes, can you share your js file code
Regards,
Vivek
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
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
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
Thanks Aron for your reply.
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
Thanks Aron for your reply.
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
Hi Krishna,
Change src to /sap/ui5/1/resources/sap-ui-core.js
Regards,
Vivek
Hi Vivek,
I tried using that /sap/ui5/1/resources/sap-ui-core.js
it is not working and hence was checking with src="https://sapui5.netweaver.ondemand.com/resources/sap-ui-core.js" ( for which i atleast got the UI part )
And got struck here 🙁
Regards,
Krishna Tangudu
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
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
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
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