4253_ed.jpg

PUBLISHED NOVEMBER 6,  2020

Power-Excel with OneStream

By Niki Dedhia

Principal Consultant, B-eye Solutions

Let’s be honest; finance likes to work with Excel and come what may, we can take a person out of Excel but not Excel out of a person. It is that intertwined in the life of a finance person. 

How about using a CPM platform that lets you work with Excel while offsetting its limitations to a great extent? Add to that the benefits that come along with a robust CPM system and you have a perfect brew.

OneStream XF Office Blend delivers a unique Microsoft Word, PowerPoint, and Excel reporting solution by combining OneStream XF content with Microsoft Office for a rich financial reporting experience.

Let us explore the ways in which we can use / integrate Excel with OneStream.

Data Load

CSV files as well as Excel templates can be used to load data. They can be imported using OneStream’s workflow channels – Import, Forms, Journals, Data attachments and custom tables

Import using CSV


One of the simplest ways to load Excel data is to convert the file into CSV format and capture the required fields in OneStream through the data source. 

1

Import using Excel Templates

Existing Excel files can be easily converted into Excel load templates for OneStream using the dimension keywords (E#, A#, etc.). Named Ranges are created on the fly for column/row headers, data and each row and column combination.

2

Import to Forms

Form templates can be created in Excel to import Form information to a specific intersection in OneStream. The header rows in Excel specify the location in which the form data is loaded.
XFF named range needs to be created in order to import this data in.

3

Import to Journals

Journals can also be imported using Excel, with a named range XFJ. The header rows specify the location where the journal will be loaded and the data rows contain the journal amount and the details to import to the specified location.

4

Import Cell Detail

Cell details for specified intersections can be imported in OneStream using named range XFC in an excel template. 

5

Relational Table XL template

This file is used to load data into custom tables in OneStream. These custom tables could also be a part of MarketPlace solutions

6

Import through live Excel connection

XFSetCell formula can be used in existing Excel files and very quickly be converted to a OneStream formula file to load data. This can be used to load exchange rates too.

7

Cube Views in Excel

Cube Views can also be opened in Excel or Spreadsheet. Named Ranges are created on the fly for column / row headers, data and each row and column combination.

8

Spreadsheet Forms

Forms created using the spreadsheet tool within OneStream have the look and feel as well as functionality of excel. This helps end users adapt easily to OneStream.

  

Using Existing Excel Sheets

 

An existing Excel file/template (e.g. Planning templates or Supplemental data templates) can be used as a form in OneStream using the Spreadsheet tool. This helps reduce implementation time and end users find it easy to work with.

Reporting and Data Analysis Sheets

Cube Views can be opened in Excel to view and analyze the data. They have a live connection with the database; all you need to do is a ‘Refresh’ and the updated data is displayed. This allows users to –

 

  • Drill down on the cells even in Excel 

  • Run Calculate, Translate, Consolidate

  • Build and create dynamic Quick Views on the same sheet without impacting other existing views, thereby giving the flexibility to quickly slice and dice the required information on a single page. 

Maximize video for better desktop viewing. View in landscape mode for mobile devices

Additionally, the same cube view can be set up to have different views when opened in Excel vs PowerPoint vs Word (header footer).

Existing reports, be it in Excel, Word or PowerPoint, can be quickly converted into live data connections. This means that users still have the comfort of using their existing reports, now interacting with OneStream directly.

 

Balance Sheet, Profit & Loss a/c, Schedules and Notes can be put together into a book, in spite of different formats.

The Takeaway

 

Taking advantage of Onestream XF’s diverse capabilities, we can seamlessly integrate existing excel files to import, view and analyse data in the application. With extensive Microsoft Office Suite integration, users are able to create and design complex data reports directly from their desktop and quickly format, scale and integrate them into OneStream XF with instantaneous results.

 

Well, you need not get rid of excel but use it smartly with minimal manual intervention. With OneStream, Excel is not just Excel but is transformed into a more useful and reliable tool; its Power-Excel.

- Niki

MODERNIZE YOUR FINANCE FUNCTION AND DRIVE STRATEGIC DIGITAL TRANSFORMATION WITH CPM 2.0

Click on the button to download the OneStream Software White Paper on:

"Enabling Modern Finance with CPM 2.0 Platforms"