In this blog we will cover how you can use your data in Spreadsheet and create a Dashboard comprised of multiple Charts and Graphs using the REST API services of StatsPanda.com.
It takes approximately 4 to 5 steps before a Dashboard is ready for share. The activities ranges from data preparation, to import of Data, to create the Charts and Graphs using the imported data and finally putting all of them together in one dashboard. We will go step by step through each of the stages for creating an Example Dashboard.
For this example, we have picked up the Financial Data of Tata Consultancy Services (TCS). TCS is a large IT Services company.
1. Data Preparation
Data preparation involved collecting the Profit and Loss Statement data, calculating the key data on per employee basis and calculating the Y-O-Y data for few key performance parameters. Data has been collected for 11 years 2005 till 2015. You can view the data on Google Sheet - TCS Financial Data or can download the data from as MS Excel from Google Storage
2. Create Datasource by importing the Data
Next, you need to import the data into StatsPanda.com system to create Datasource. You have five options here -
- Import the data as CSV from public web url.
- Import the data as Excel from public web url.
- Import the data from Google Sheet. Note Google Sheet needs to be published and must be public.
- Upload a CSV or Excel and import the uploaded file to create the Datasource.
- Import the data as JSON Data.
For our example here, we will use option#4.
2.1. Upload a File to StatsPanda.com
Upload the Spreadsheet using the File Upload interface that is available under Console > File Upload.
Once uploaded it returns the response as JSON Data as shown below. Note down uniqueKey below, this ID will be used to import data from the Excel Spreadsheet.
2.2. Import Data from uploaded Excel Sheet
As mentioned above an Excel Sheet can be imported either from a public url or from an uploaded file using it's uniqueKey. For this example, we imported the data using the file uploaded above. All apis for importing are available under Console > Connectors.
The API used here is -
http://api.statspanda.com/datasource/connector/excel This is the most flexible and powerful of all Data Import APIs for importing data. It can import specific rows, columns, starting from specific cell. For example, we have created three different Datasource from one Sheet in the uploaded excel spreadsheet.
Here is the response returned for importing 34 rows of Profit and Loss data from the first sheet on the spreadsheet starting with row number 2 & column 1.
"spreadsheetUrl": "/ui/charts/datasource/data-table?unique_key= 2f1b41db19d6e1606aa354cb9ad8dc4a"
Similarly, we will import 5 rows of Per Share Data starting at row number 37 & column 1.
Note down the unique_key values in above json. These values will be used while creating charts and graphs. You can view the imported Datasource here. Note that the data has been Transposed. Similarly, we imported Key Financial Data by Employee for Tata Consultancy Services. This unique_key is the Datasource id and will be used for creation of any chart, graph etc. The second imported Datasource can be viewed here. You can find them by going the Datasource listing page under Datasource > Excel
3. Create Charts
Next we will create multiple charts using the three different but related Datasources created in above section.
To keep things simple, we will use only couple of types of Chart APIs.
Chart#1 - Create a line Chart with Net Sales, EmployeeCost, OperatingProfit. The REST API used is - http://api.statspanda.com/charts/morris/line-chart. This Chart API is available under Console > Charts.
Here is the url of the generated Chart. This url will be used while creating the Dashboard.
Chart#2 - Next, lets find out how API Console is used to create a line chart with various expenses. Here is a screenshot of the API Console after the API was invoked and it returned response and showed a preview. You can view the input to the API in the upper Textarea and the response is seen in the lower Textarea.
Here is the returned JSON response from the REST API invocation as seen in the lower Textarea:
Chart#3 Plot Net Sales, Operating Profit and Employee Expenses over last 11 ( 2005 till 2015).
JSON Response :
Here is the Chart when embedded in a blog independently.
Chart#4 Key Parameters Y-O-Y for Acme Corporation
Chart#5 A comprehensive chart with all key fields Year, NetSales, Total Expenses, OperatingProfit, Tax, Reported Net Profit, Total Value Addition
Chart#6 A chart with Average Revenue per Employee, Average Salary and Employee Strength
Chart#7 Acme Corp per share data
4. Dashboard Creation
It's the final step where the user will assemble all the Charts created so far into a Dashboard. Go to Console > Charts , pick http://api.statspanda.com/rest/dashboard , edit the input Jason Data. Once created, you will find following url as part of the response. This url is the Dashboard we created in blog. Take a look.
Hopefully you find this blog useful.