Probably, you or your manager is demanding a financial charting capabilities in SharePoint® - substantial amounts of data coming in at real-time, and a wealth of historical data to put everything into context. You want to track for a specific ticker (represents an acronym of a company or a corporation made up of up to four letters) the stock market historical records, but you also want the flexibility to change that ticker.
And how this could be represented in SharePoint? The answer is
SharePoint Business Connectivity Services
With this post we will demonstrate how to configure a simple WCF service which would retrieve JSON response from Yahoo Financial Services.
The financial services will be consumed, as in a similar manner to our IG Finance XAML Application Sample, using a Yahoo Pipe inside the WCF service. In the end, the result would look something like this:
Yahoo Pipes
What is Yahoo Pipes®? It is a composition tool to aggregate, manipulate, and mashup data feeds and output them in a suitable format such as RSS, JSON, and others. This blog post is not focused on configuring a pipe, so should you want to learn more and start developing your own pipes, just go to http://pipes.yahoo.com/pipes/ .
We are interested in the following pipe: http://pipes.yahoo.com/pipes/pipe.info?_id=98aa7fee9ad07efbdbde5699c53f1bcb
You can see it requires a few parameters, including
- Ticker
- Start month
- Start day
- Start year
- End month
- End day
- End year
Feel free to input some parameters and test it yourself. After running the pipe you would have a few options - to get it as RSS, JSON, PHP, etc. In our case, we are interested in the JSON response, by clicking it you will get something like this:
{
"count": 109,
"value": {
"title": "Yahoo Finance Historical Data",
"description": "Pipes Output",
"link": "http:\/\/pipes.yahooapis.com\/pipes\/pipe.info?_id=98aa7fee9ad07efbdbde5699c53f1bcb",
"pubDate": "Fri, 08 Jun 2012 08:15:21 +0000",
"generator": "http:\/\/pipes.yahooapis.com\/pipes\/",
"callback": "",
"items": [{
"Date": "2012-06-07",
"Open": "22.50",
"High": "22.75",
"Low": "22.01",
"Close": "22.06",
"Volume": "14333400",
"description": null,
"title": null
}, {
"Date": "2012-06-06",
"Open": "21.70",
"High": "22.36",
"Low": "21.69",
"Close": "22.35",
"Volume": "14727100",
"description": null,
"title": null
}...
}
The Solution
For this example, we are going to build a WCF service which would retrieve the data from the given Yahoo Pipe JSON response. This WCF would accept a Ticker parameter which we are going to use later as a filter in our SharePoint External List. Why we are building WCF service? It will provide us a single tier of data which could be easily configured with the SharePoint Designer as an External Content Type, thus not requiring sophisticated or coding knowledge for creating a .NET assembly connector.
The WCF Service
We begin by creating a WCF service - we will use Visual Studio 2012 RC to build and publish our service.
1. Start Visual Studio and create a new WCF Service Application. Choose the target .NET framework version for your application. Keep in mind depending on the version of the framework some classes, which we are going to talk about later, would be available or respectively not available. For now, we choose 4.0
2. Add a new class which would represent your in-memory data object, describing an "item" from the JSON array.
Do not forget to mark your class and properties with DataContract and DataMember attributes so they could be serialized.
If you return to your JSON response, you would notice that there is not Ticker property. So why do we add it? We will use it inside our SharePoint list views to filter out the data.
3. Return to your service interface.
Here we will define which parameter to pass - as we already mentioned this is Ticker, which is of type string.
4. Coding the service.
After running your pipe and getting the JSON response, you probably already have noticed something like the following as an URL in the address bar:
http://pipes.yahoo.com/pipes/pipe.run?_id=98aa7fee9ad07efbdbde5699c53f1bcb&_render=json&a=0&b=1&c=1900&d=0&e=1&f=2012&s=HPQ
After the render parameter, which is self-explanatory, there are six other parameters. Each of them, as you probably already guessed, stands for:
- a = Start Date Month. Months start from 0 (January = 0)
- b = Start Date Day
- c = Start Date Year
- d = End Date Month
- e = End Date Day
- f = End Date Year
- s = ticker company (For example MSFT = Microsoft Corporation)
We start simply by getting a response from that pipe (URL).
However, how should we proceed from now on? How to make a selection in value.items.Date for example and map it to our in-memory data object? You cannot use XDocument class since it is throwing an exception that the root element is invalid.
- You have a few possibilities: one of them is to code a custom JSON converter (used for registering the JavaScriptSerializer instance), then using the JavaScriptSerializer class to deserialize the JSON response. Finally, you can use the known dot notation to access the members in each item. In this case, you are creating a dynamic object and then iterate through it, filling a List instance of your in-memory data object.
- Another approach is to use the DataContractJsonSerializer class deserializing the JSON data into objects. This class is available as of .NET framework 4.0 - in this case, you would need to create an object which describes the "value" object and the "items" array list.
In our case we are using the first approach. Following the blog from Shawn Weisfeld (http://www.drowningintechnicaldebt.com/ShawnWeisfeld/archive/2010/08/22/using-c-4.0-and-dynamic-to-parse-json.aspx) we create a DynamicObject, and JavaScriptConverter.
Now we can continue developing our service. As we already said we will be using dynamic object, thus the code would look like this:
Note the way we are accessing the elements - we access them through the dot notation from the deserialized object and add each element into our in-memory data object list.
Finally, we return the List instance of our in-memory data object.
5. Testing the service
You can now test your service with the integrated WCF test client. Not a lot of people are aware of its usage - you can debug your WCF application opening the SVC and just hitting F5.
Once loaded, selected your GetData method and in the Request window, for company parameter type a ticker value - in our case we test it with Microsoft Corporation. Hitting the Invoke button would process the request.
6. Publishing the Service
Now we have to publish our service, so it could be consumed by the SharePoint BCS. For this example, we will publish it locally on the IIS. Beforehand we have configured a web application running on the same .NET framework version we created our WCF service.
Once published, we can verify if everything is OK by visiting the web address of the service.
Configure the External Content Type
As we already have mentioned we are going to use SharePoint Designer to configure our External Content Type which we will use to create an External List in our SharePoint web application.
1. Start SharePoint Designer and open your web application.
2. Navigate to the External Content Types.
3. Click Create New External Content Type button from the ribbon.
4. Give name of your ECT - in our case StockMarketBCS
5. Click the External System link
SharePoint Designer will take you to a page where you can manage the connections to the external systems. Click the Add Connection button.
From the list, select WCF and then click the OK button.
For a Service Metadata URL enter the URL to your service with a WSDL parameter.
For Service End Point URL enter the URL to your service.
For a name provide something meaningful so you could remind what this connection is for.
For now, you can leave the rest of the settings as they are. Click the OK button.
6. Configure your Read Item and Read List operations.
Now configure Read Item and Read List operations. The blog has no subject in configuring different ECT operations, but here is one trick here. For the Read List operation create a comparission filter as follows:
Why we do this? When we are creating a List View in our External List, we get a Data Source Filter.
This is our ticker value, i.e. the company abreviation, which we would like to retrieve stock data for. When a user is creating another view and passes a filter for another ticker, data for that particular company will be retrieved in the newly created view.
Charting the Stock Data
We have our data now, so we could chart it using the Infragistics Chart web part. The web part provides two types of financial charts - OHLC and Candlestick. It is up to you which one you want to use.
Configuring the Chart is easy, using the Design Editor - you just select the type of series you want to plot, and mark up the data by dragging and dropping the SharePoint list view fields onto the data properties using the Data source picker dialog.
Additionally, you could set a refresh interval for the Chart to ensure that the decision makers or analyzers are getting the latest data.
Also, should there are a lot of data points, you might want to enable zooming so that certain records could be previewed in greater details.
You can add multiple series onto the same Chart scene by selecting different list view (a list view retrieving information for one company) and comparing how the stocks for different tickers are trending.
If you want to explore more about the Chart web part, you can check the online samples at http://sharepoint.infragistics.com/chartWebPart/SitePages/Home.aspx
To learn more about configuring the Chart, you can check the online documentation at http://help.infragistics.com/NetAdvantage/SharePoint/2012.1/CLR4.0
