Annual Financial Report

The following sample shows how you can use the SpreadJS spreadsheet to create different financial reports and statements for your JavaScript applications, such as an Annual Financial Report.

This example uses a predefined workbook that is loaded into SpreadJS. It highlights the use of formulas, sparklines, charts, conditional formatting and hyperlinks. This example also uses WEBSERVICE and FILTERJSON functions to get data from a webservice and parse the data into a value, an object or an array of objects. *Data provided for free by Alpha Vantage.
import { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@grapecity/spread-sheets-angular'; import GC from '@grapecity/spread-sheets'; import '@grapecity/spread-sheets-charts'; import '@grapecity/spread-sheets-shapes'; import './styles.css'; @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { hostStyle = { width: '100%', height: '100%', overflow: 'hidden', float: 'left' }; constructor() { } initSpread($event: any) { let spread = $event.spread; spread.fromJSON(data); this.initsheet1(spread.sheets[1]); this.initsheet2(spread.sheets[2]); this.initsheet3(spread.sheets[3]); this.initsheet4(spread.sheets[4]); } initsheet1(sheet: GC.Spread.Sheets.Worksheet) { //summary 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) { sheet.setFormula(i,2,'=IFERROR(PROPERTY(A36,"'+properties[0][index]+'"),"")'); sheet.setFormula(i,12,'=IFERROR(PROPERTY(A36,"'+properties[1][index]+'"),"")'); sheet.setFormula(i,16,'=IFERROR(PROPERTY(A36,"'+properties[2][index]+'"),"")'); index++; } sheet.setRowVisible(35, false); sheet.resumePaint(); } initsheet2(sheet: GC.Spread.Sheets.Worksheet) { //income statement sheet.suspendPaint(); var properties =["fiscalDateEnding", "totalRevenue","costOfRevenue","costofGoodsAndServicesSold","researchAndDevelopment", "sellingGeneralAndAdministrative","depreciationAndAmortization","interestIncome","interestExpense","otherNonOperatingIncome","incomeTaxExpense"]; var index = 0; 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++) { if([5,8,9,11,12,16,17].includes(j)){ continue; } else { for(var i=0;i<5;i++) { sheet.setFormula(j,8-i,'=IFERROR(1*PROPERTY(A40,"annualReports.'+i+'.'+properties[index]+'"),0)'); } index ++; } } sheet.resumePaint(); } initsheet3(sheet: GC.Spread.Sheets.Worksheet) { //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(); } initsheet4(sheet: GC.Spread.Sheets.Worksheet) { //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 ++; } } sheet.setRowVisible(40, false); sheet.resumePaint(); }; } @NgModule({ imports: [BrowserModule, SpreadSheetsModule], declarations: [AppComponent], exports: [AppComponent], bootstrap: [AppComponent] }) export class AppModule {} enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule);
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/angular/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Polyfills --> <script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/en/angular/node_modules/zone.js/dist/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script> <script src="$DEMOROOT$/spread/source/data/annualReport.js" type="text/javascript"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('$DEMOROOT$/en/lib/angular/license.ts'); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>
<div class="sample-tutorial"> <gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"> <gc-worksheet></gc-worksheet> </gc-spread-sheets> </div>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }
(function (global) { System.config({ transpiler: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/dist/zone.min.js', 'rxjs': 'npm:rxjs/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/bundles/core.umd.min.js', '@angular/common': 'npm:@angular/common/bundles/common.umd.min.js', '@angular/compiler': 'npm:@angular/compiler/bundles/compiler.umd.min.js', '@angular/platform-browser': 'npm:@angular/platform-browser/bundles/platform-browser.umd.min.js', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/bundles/platform-browser-dynamic.umd.min.js', '@angular/http': 'npm:@angular/http/bundles/http.umd.min.js', '@angular/common/http': 'npm:@angular/common/bundles/common-http.umd.min.js', '@angular/router': 'npm:@angular/router/bundles/router.umd.min.js', '@angular/forms': 'npm:@angular/forms/bundles/forms.umd.min.js', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': 'npm:plugin-typescript/lib/plugin.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js', '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-shapes': 'npm:@grapecity/spread-sheets-shapes/index.js', '@grapecity/spread-sheets-charts': 'npm:@grapecity/spread-sheets-charts/index.js', '@grapecity/spread-sheets-angular': 'npm:@grapecity/spread-sheets-angular/bundles/grapecity-spread-sheets-angular.umd.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);