Financial statements are needed financial records that show a company's business activity and financial performance. These statements help the stakeholders, including management, investors, and financial analysts, evaluate and make strategic decisions by comparing past and current performance, predicting future performance, and making decisions regarding resource allocation.
This blog will show you how to create three simple yet powerful Financial reports in JavaScript. We will also present a few ways developers can get the final data from different APIs and easily create these reports.
For the spreadsheet, we will use SpreadJS, GrapeCity's Excel-like JavaScript spreadsheet solution.
Overview
Annual Financial reports usually include these 3 reports:
We have created a page that will contain links to the elements mention below, which would be on other pages.
To begin, make sure you have downloaded SpreadJS.
This blog also uses the included SpreadJS Designer, which is included in the download and can be installed from the "\SpreadJS.Release.x.x.x\Designer\Designer Runtime" folder.
The Designer is a WYSIWYG stand-alone desktop application that helps JavaScript developers quickly design spreadsheets in a fraction of the time with the point and click interface.
The following steps show you how to link the cells with the other pages:
- Select the cell containing the sheet name and then go to Insert Tab and select Hyperlink menu.
- Enter hyperlink text
- Enter the desired colors for link and visited link
- Chose This Document and point to the right page
Our Intro page would look like below. If we were to click on the elements cells, those would point to the corresponding page.
The sheet contains some shapes that can be used in SpreadJS by adding:
<head>
...
<script src='.../spreadjs/gc.spread.sheets.all.x.x.x.min.js' type='text/javascript'></script>
<script src='.../spreadjs/plugins/gc.spread.sheets.shapes.x.x.x.min.js' type='text/javascript'></script>
</head>
Executive Summary
The second page of our demo contains a summary of some of the most important financial data of the report and data for the company.
We are using some demo data provided by Alpha Vintage to create our report. The API returns the company information, financial ratios, and other key metrics for the equity specified. Data is generally refreshed on the same day a company reports its latest earnings and financials.
For the API data to be used on SpreadJS, we will use these Spread JS powerful functions:
WEBSERVICE(URL) returns string data from a web service on the Internet or Intranet that supports CORS (Cross-origin resource sharing). If the request doesn't follow the same-origin policy and the target website doesn't support CORS from source origin, it will return #VALUE! error. The function result is a string.
FILTERJSON(json_string) parses a JSON string into a scalar value, an object, or an array of objects (can spill vertically).
*You can use the FILTERJSON function and WEBSERVICE function to get the JSON object from the server and use it in SpreadJS.
PROPERTY(json_object, property_name) gets the property value of the JSON object
With just some rows of code, you can retrieve all the API data and use those in SpreadJS continuously.
We have followed the below steps to create the summary page of the report:
Get the API data and put those on a particular cell (that will be hidden later) - we are proceeding in this way as our template does not look good if we "spill vertically" the data by using FILTERJSOn.
=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=OVERVIEW&symbol=IBM&apikey=demo")) - this formula gets the JSON from the web and parses it, so it is ready to be used on SpreadJS.
In the wanted cells, we use =IFERROR(PROPERTY(JSON Ob Reference,"property_name"),"") to put the values of the object's property.
function initsheet1(sheet) {
//executive summary page
sheet.suspendPaint();
var properties =[["Symbol","Address","Sector","Industry","FiscalYearEnd","EBITDA","ProfitMargin"],
["BookValue","SharesOutstanding","Beta","PERatio","DividendYield","ExDividendDate","ShortPercentFloat"],
["MarketCapitalization","AnalystTargetPrice","RevenuePerShareTTM","EPS","DividendPerShare","ShortRatio","PayoutRatio"]];
sheet.setFormula(35,0,'=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=OVERVIEW&symbol=IBM&apikey=demo"))');
var index = 0;
for(var i=9;i<28;i=i+3)
{
//company profile section
sheet.setFormula(i,2,'=IFERROR(PROPERTY(A36,"'+properties[0][index]+'"),"")');
//key values section - first part
sheet.setFormula(i,12,'=IFERROR(PROPERTY(A36,"'+properties[1][index]+'"),"")');
//key values section - second part
sheet.setFormula(i,16,'=IFERROR(PROPERTY(A36,"'+properties[2][index]+'"),"")');
index++;
}
sheet.setRowVisible(35, false);// hide the json object
sheet.resumePaint();
};
Our summary page would look like below - and every time the data change on the API, so would change in our sample.
Income Statement
The Income Statement is a financial statement used to report the financial performance of a company. It provides valuable information regarding operations, management's efficiency, sector's performance, and company's performance related to industry peers.
This statement usually contains data from various financial periods (fiscal years) for performance comparison.
For this blog, we have taken into consideration IBM's IncomeStatement demo data. The API contains information for elements of the Income statement for various years, but we will use the data for the last 5 years.
Keep in mind, to ease our work process, we are using a property array that contains the names of our elements. Some rows of the Statement do not have data that need to be taken from the API, which is why we are excluding those from the calculations. Also, remember that we are using the data for the last 5 years.
To add the values into the Income Report, follow these steps:
- Use the WEBSERVICE and FILTERJSON to get the JSON object from the server and use it in SpreadJS.
=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=IBM&apikey=demo"))
-
Create an array with the statement properties
-
Use PROPERTY(A40,"annualReports.'+i+'.'+properties[index]+'"), where A40 is the cell where the JSON object is located.
From the above API, attach the code to populate the report with data.
function initsheet2(sheet) {
//income statement
sheet.suspendPaint();
var properties =["fiscalDateEnding", "totalRevenue","costOfRevenue","costofGoodsAndServicesSold","researchAndDevelopment",
"sellingGeneralAndAdministrative","depreciationAndAmortization","interestIncome","interestExpense","otherNonOperatingIncome","incomeTaxExpense"];
var index = 0;
//GET the json
sheet.setFormula(39,0,'=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=IBM&apikey=demo"))');
for(var j=4;j<22;j++)
{
//these rows are empty or are needed for calculations
if([5,8,9,11,12,16,17].includes(j)){
continue;
}
else {
for(var i=0;i<5;i++)
{
//set the value of the json property on the right cell
sheet.setFormula(j,8-i,'=IFERROR(1*PROPERTY(A40,"annualReports.'+i+'.'+properties[index]+'"),0)');
}
index ++;
}
}
sheet.resumePaint();
};
In the below photo, we have hidden the column containing the Income Statement data. We have created a Cascade Sparkline that shows how Revenue is transformed into Net Income for the last 5 years and a Variance Chart that shows the difference between the values of the previous data with the actual one.
For further information on how to use Variance and Cascade Sparkline in SpreadJS, please visit this blog.
Balance Sheet
The balance sheet shows a company's assets (what they own), liabilities (what they owe), and stockholders' equity (or ownership) at a given moment. It is a statement of financial stability.
When working with real data: Assets = Liabilities + Shareholders' Equity.
We will use IBM's BalanceSheet demo data in this example.
Using the SpreadJS functions WEBSERVICE and FILTERJSON, we get the JSON object from the server and use it in SpreadJS.
=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=BALANCE_SHEET&symbol=IBM&apikey=demo"))
Create an array with the statement properties to have a neater code. Use PROPERTY(A43,"annualReports.'+i+'.'+properties[index]+'"), where A43 is the cell where the JSON object is located.
From the above API, attach the code to populate the report with data.
function initsheet3(sheet) {
//balance sheet
sheet.suspendPaint();
var properties =["fiscalDateEnding", "cashAndCashEquivalentsAtCarryingValue","cashAndShortTermInvestments","otherCurrentAssets","inventory",
"currentNetReceivables","investments","propertyPlantEquipment","accumulatedDepreciationAmortizationPPE",
"intangibleAssets","intangibleAssetsExcludingGoodwill","longTermInvestments","goodwill",
"otherNonCurrrentAssets","currentAccountsPayable","shortTermDebt","deferredRevenue",
"otherCurrentLiabilities","longTermDebt","otherNonCurrentLiabilities","treasuryStock","retainedEarnings"];
var index = 0;
sheet.setFormula(42,0,'=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=BALANCE_SHEET&symbol=IBM&apikey=demo"))');
for(var j=5;j<38;j++)
{
if([6,12,13,21,22,23,24,29,30,33,34,35].includes(j)){
continue;
}
else {
for(var i=0;i<5;i++)
{
sheet.setFormula(j,6-i,'=IFERROR(1*PROPERTY(A43,"annualReports.'+i+'.'+properties[index]+'"),0)');
}
index ++;
}
}
sheet.setRowVisible(42, false);
sheet.resumePaint();
};
The view of the balance sheet report would be like below:
Below we have created a treemap chart to show the elements of the balance sheet. You can create the chart directly from SpreadJS Designer or by using the below formula:
var chart = sheet.charts.add('treeMap_assets', GC.Spread.Sheets.Charts.ChartType.treemap, 30, 100, 400, 300, 'BalanceSheet!$G$8:$G$12,BalanceSheet!$G$15:$G$21');
Cash Flow Statement
The cash flow statement is a financial statement that measures the amount of cash generated in a financial period, including all the amounts added or substracted from cash during different transactions.
Said differently, this statement gives the information of the company's ability to meet short-term obligations, invest in the company, or pay dividends to shareholders by showing all the inflow and the outflow of the cash during a specific period.
In our example, we will be using IBM's CashFlow demo data. Using the SpreadJS functions WEBSERVICE and FILTERJSON, we retrieve the JSON object from the server and use it in SpreadJS.
=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=CASH_FLOW&symbol=IBM&apikey=demo"))
- Create an array with the statement properties to have a neater code
- Use PROPERTY(A41,"annualReports.'+i+'.'+properties[index]+'"), where A41 is the cell where the JSON object is located. As I mentioned before, we will take only 5 last annualreports objects from the API to use in our financial report.
Below is the code that we have used to populate the Cashflow sheet with the right data:
function initsheet4(sheet) {
//cashflow
sheet.suspendPaint();
var properties =["fiscalDateEnding","netIncome","paymentsForOperatingActivities",
"proceedsFromOperatingActivities","depreciationDepletionAndAmortization","changeInReceivables",
"changeInInventory","changeInOperatingLiabilities","changeInOperatingAssets",
"capitalExpenditures","proceedsFromRepaymentsOfShortTermDebt","paymentsForRepurchaseOfCommonStock",
"paymentsForRepurchaseOfEquity","paymentsForRepurchaseOfPreferredStock","dividendPayout",
"proceedsFromIssuanceOfCommonStock","proceedsFromIssuanceOfLongTermDebtAndCapitalSecuritiesNet",
"proceedsFromIssuanceOfPreferredStock","proceedsFromRepurchaseOfEquity","proceedsFromSaleOfTreasuryStock",
"changeInCashAndCashEquivalents","operatingCashflow"];
var index = 0;
sheet.setFormula(40,0,'=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=CASH_FLOW&symbol=IBM&apikey=demo"))');
for(var j=4;j<35;j++)
{
if([5,14,15,16,18,19,20,31,32,35,36].includes(j)){
continue;
}
else {
for(var i=0;i<5;i++)
{
sheet.setFormula(j,6-i,'=IFERROR(1*PROPERTY(A41,"annualReports.'+i+'.'+properties[index]+'"),0)');
}
index ++;
}
}
// hide the cell that has the json object
sheet.setRowVisible(40, false);
sheet.resumePaint();
};
To make our report more appealing, we can enhance its appearance via conditional formatting and sparklines.
The conditional formatting will help the users have a better view of the cash inflows and the outflows. Use blue to show cash addition and yellow for the deduction. Follow the steps below to add the formatting by using the SpreadJS designer.
- Select the Area, in our case: $C$7:$G$15,$C$18:$G$18,$C$22:$G$32,$C$34:$G$35
- Go to Conditional Formatting > New Rule
- Select Format all cells based on their values
- On the Format Style, select 3-Color Scale and enter the desired colors. For the MidPoint section, choose Number and enter 0 as a midpoint value
- Click OK
We will also be adding a 5 YEAR TREND column that will contain some COLUMNSPARKLINES to make it easier for management personnel to notice trends. These decision-makers can use these trends when making business recommendations and choices.
To add a sparkline, use this formula on the spreadsheet:
=COLUMNSPARKLINE(C7:G7,1,,,"{negativeColor:#fbdd5f,seriesColor:#a7ccdc,displayEmptyCellsAs:0,displayXAxis:true,showNegative:true,maxAxisType:0,minAxisType:0}")
You can use the following code if we want to add it via JavaScript:
sheet.setFormula(6, 7, '=COLUMNSPARKLINE(C7:G7,1,,,"negativeColor:#fbdd5f,seriesColor:#a7ccdc,displayEmptyCellsAs:0,displayXAxis:true,showNegative:true,maxAxisType:0,minAxisType:0}")');
Our report would look like this upon completion:
Key Takeaways
- Financial statements are one of the most important parts of a company's reports as they reveal the company's financial health information using numbers as metrics
- With the SpreadJS v14.1 (Service Pack 1) release, fetching data from a web service or parsing data from a JSON string has never been easier. Using WEBSERVICE and FILTERJSON formulas, you can effortlessly access financial data and use it to create financial reports
- You can enhance your report by using powerful means that SpreadJS offers, such as charts, sparklines, conditional formatting, etc.
SpreadJS is the most comprehensive spreadsheet solution for enterprise JavaScript development. Embed this component into your FintTech JavaScript applications to create financial reports and discover a complete Excel-like experience.
Download a free trial of SpreadJs and discover how easily you can embed Excel-like JavaScript spreadsheets and other business-related functionalities into your enterprise applications.
Download the example here and follow along.
Contact us to learn more: us.sales@grapecity.com
Tags:
