Overview
If you have ever used custom functions in Excel, depending on the complexity of them, you have probably run into an issue where the accuracy of the results was sporadic. There is a quick solution. Read more
Tag Archive for: excel
Whether you play a technical role or are a financial analyst, Excel is likely a major asset in your toolbox. Whether it is the SUM function, the VLOOKUP function, or one of the many others, we have all used Excel functions for a plethora of reasons.
There is a lot of potential hidden in Excel that you may not be aware of. Excel offers the ability to create your own user defined functions, and it’s not hard to create them. With a little ingenuity and strategic thinking, custom Excel functions can be a huge asset. Read more
Working with people new to Essbase every three to six months, I am always looking for ways to show users their hierarchies effectively. Many of them don’t have access to Essbase administration services or EPMA. So, I always fall back to excel as a distribution method, as well as documentation, to show hierarchies.
Expanding hierarchies to all descendants is a great way to show small hierarchies, but, I am always asked to make it a collapsible hierarchy using the Excel grouping feature. The challenge of doing this manually to a hierarchy with thousands of members is that it is extremely time consuming and very error prone.
The following script can be added to any workbook to automate this effort.
Sub CreateOutline()
Dim cell As Range
Dim iCount As Integer
For Each cell In Selection
'Check the number of spaces in front of the member name
'and divide by 5 (one level)
iCount = (Len(cell.Value) - Len(Trim(cell.Value))) / 5
'Only execute if the row is indented
If iCount <> 0 Then cell.EntireRow.OutlineLevel = iCount
Next cell
MsgBox "Completed"
End Sub
Setup
First, this sub routine has to be added to a workbook. Open up the visual basic editor. Right click on the workbook in the project explorer window and add a new module. Paste the code above in the new module. The editor is in different places in different version. In Excel 2007 and 2010, the Developer ribbon is not visible by default. To make it visible, go to the navigator wheel and click Excel Options. There is a checkbox named Show Developer Ribbon that will make this developer ribbon viewable.
How To Use
First, open the member selection option in the Essbase add-in or smart view and select the parent. Add all its descendants. Alternately, change the drill type to all descendants and zoom in on the member of the hierarchy.
Retrieve, or refresh, the data, and make sure the indent is set so the children are indented. Now, highlight the range of cells that has the hierarchy/dimension that the grouping should be applied. This should include cells in one column of the worksheet. Open the code editor and place the cursor inside the sub routine you added from above and click the green play triangle in the toolbar to execute the script. When this is finished, go back to the worksheet with the hierarchy and it will have the hierarchy grouped.
Excel limits the level of groupings to eight. If the hierarchy has more than eight levels, they will be ignored. Now, the hierarchy can be expanded and collapsed for viewing.
Shortcut keys or toolbar buttons can be assigned to execute this function if it is used frequently. If you are interested in doing this, there are a plethora of how-to articles on this topic. This Google search will get you started if you choose to go down that path.
So, the next time you need to explain a hierarchy in Essbase, or distribute it in a common format, hopefully this script will help.
A lot of users like to see hierarchies in Excel and build groupings around these hierarchies so they can be collapsed and expanded easily. It is not a huge deal to do this for things that don’t change a lot, like months rolling to a quarter, but it can be extremely cumbersome to maintain for organizational or account hierarchies that are large or change frequently.
By adding some VBA code (a macro) to your workbook, managing groupings can be completely automated. This can be customized for a plethora of different scenarios. Below are 2 examples that Hyperion users will encounter. One caveat to this is that Excel limits the number of grouping levels to 8. If the worksheet has more than 8 levels, the following logic would not provide the expected result.
Creating a Hierarchy Based On Excel Indents
If a spreadsheet exists where the hierarchy is created with the indent (not multiple columns) feature of Excel, select the range for the groupings to be applied. Execute the following script. Basically, this loops through the cells you have selected and will create the groupings based on the number of indents in the cell.
Sub CreateGroupingsOnIndents() Dim cell As Range For Each cell In Selection If cell.IndentLevel <> 0 Then cell.EntireRow.OutlineLevel = cell.IndentLevel Else cell.EntireRow.ClearOutline End If Next End Sub
Creating a Hierarchy Based On SmartView/Excel Add-In Indents
When retrieving from Essbase, cells are indented by adding 5 spaces to the member name. By getting the length of the cell, subtracting the number of spaces preceding the member name, and dividing the result by 5, the level of the indent is identified. Select the cells with the member names and execute the following.
Sub CreateGroupingsOnSpaces() Dim cell As Range Dim iLength1 As Integer Dim iLength2 As Integer Dim iIndent As Integer For Each cell In Selection iLength1 = Len(cell.Value) iLength2 = Len(LTrim(cell.Value)) iIndent = (iLength1 - iLength2) / 5 If iIndent <> 0 Then cell.EntireRow.OutlineLevel = iIndent Else cell.EntireRow.ClearOutline End If Next End Sub
Setup a Module
If you are unfamiliar with adding custom code to an Excel workbook, follow the steps below.
Excel 2000 and below
- Select Tools/Macro/Visual Basic Editor
- Right click on the workbook in the Project window, and select Insert/Module
- Expand the module folder and open the new module (likely module1)
- Paste the example above in this window to the right
- Execute it by clicking F5 or the green play triangle in the toolbar
Excel 2003 and greater
- Select the Navigation Wheel, and check the “Show Developer tab in the Ribbon” checkbox in the Popular tab
- Select the Developer Ribbon and click Visual Basic
- Right click on the workbook in the Project window, and select Insert/Module
- Expand the module folder and open the new module (likely module1)
- Paste the example above in this window to the right
- Execute it by clicking F5 or the green play triangle in the toolbar
These can also be associated to a custom menu or toolbar if you choose to take the extra step!
As an Essbase user, you have more power to improve performance than you think. How many times do you lock and send data through Excel, SmartView, or web forms, that include zeros? How many times do you allocate data to a finite level out of convenience? Understanding what this does to Essbase is critical to understanding how a user can negatively impact performance without adding any value to the analysis or the results the database produces.
I analyzed a planning database used in one of the largest financial institutions in the world. Over 60% of the values entered were zero. Another 20% of the values were less than 1 dollar. By eliminating the zeros, the total calculation time of the planning application was under 20 minutes. With the zeros, it was nearly 2 hours.
There are two reasons for this. First, there is a different between empty and zero. Empty consumes no space to store whereas a zero consumes the same space as 1 billion. Think of this as a grocery bag. If you fill a grocery bag with nothing, it takes up no space. If you fill it with empty cans (a zero), it consumes the same amount of space as if those cans were full (1 billion).
The example below is very common. Assume that a forecast needs to be done for the last 3 months of the year. Frequently, a spreadsheet would hold zeros for the first 9 months. 18 cells have zero and 6 cells have a positive value. That means that 75% of your data could be eliminated by not loading zeros.
The same load with #Missing is more effective.
I highly recommend reading the article explaining dense and sparse to understand what a block is and what it represents before you continue this article.
There is also another very significant factor in loading zeros. Loading a zero that creates a block just to hold a value of zero can explode the size of the database, as well as the time it takes to consolidate and execute business rules. The more blocks that have to be loaded and consolidated, the longer it takes to finish. If each block was a spreadsheet and you had to do this manually, you would have to open each spreadsheet and enter the number into a calculator to consolidate. If 75% of the blocks you opened were zero, it wouldn’t change your total, but it would drastically increase the time it takes because you still have to open each spreadsheet. If an Essbase database has 1,000 blocks, and 75% of them only hold zeros, it will likely take 2 or 3 times longer to calc the zeros because it still has to open the block and add the zero. Remember, a zero acts no differently than a value of 100.
As an example to the above, the following example would create a block for South and West, inflating the database size.
Users can significantly reduce this unnecessary explosion in size by loading a blank as apposed to a zero. If zeros are already in the database, leaving the cell blank will NOT overwrite the zeros. If zeros are loaded inadvertently, a #Missing has to be used to remove them.
For all you users loading data, it can be a hassle removing the zeros. Being responsible can significantly improve your experience with Essbase. To make it easier, take a look at the function in the In2Hyperion Excel Ribbon that replaces all zeros with #Missing.
Many processes need to write large volumes of data in Excel. The typical method is to loop through each cell and perform the action.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
CurrVal = CurrVal 1
Next CurrCol
Next CurrRow
Rather than writing the values out cell by cell, it is quicker to store the value in an array and write the array to a range of cells at one time.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
Dim TempArray() As Double
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Update the array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal
CurrVal = CurrVal 1
Next j
Next i
' Transfer temporary array to worksheet
TheRange.Value = TempArray
This same method can be used when altering data. By changing the following line
TempArray(i, j) = CurrVal
To this
TempArray(i, j) = TheRange(i, j) * 3
By using TheRange(i, j), the existing value can be altered
The process of writing values cell by cell took 3.16 seconds. Using the array method, it took .08 seconds, nearly 40 times faster
Many processes need to write large volumes of data in Excel. The typical method is to loop through each cell and perform the action.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
CurrVal = CurrVal 1
Next CurrCol
Next CurrRow
Rather than writing the values out cell by cell, it is quicker to store the value in an array and write the array to a range of cells at one time.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
Dim TempArray() As Double
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Update the array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal
CurrVal = CurrVal 1
Next j
Next i
' Transfer temporary array to worksheet
TheRange.Value = TempArray
This same method can be used when altering data. By changing the following line
TempArray(i, j) = CurrVal
To this
TempArray(i, j) = TheRange(i, j) * 3
By using TheRange(i, j), the existing value can be altered
The process of writing values cell by cell took 3.16 seconds. Using the array method, it took .08 seconds, nearly 40 times faster
If you have users that rely on SmartView to pull data from your Essbase and/or Planning application, many of them may have large spreadsheets. One way to improve the perception of the performance of Essbase is the method in which SmartView (client side) communicates with the server.
APS, Planning, and HFM have the ability to take advantage of compression during the communication process. When large queries, retrieving and submitting data, are initiated, the performance can be significant.
The default compression settings for APS and Planning are not turned on. The good news is that turning this on is relatively simple.
Find the essbase.properties file on the APS server and change it to false. The path to this file is different in versions 9 and 11. In 11, the path is \Products\Essbase\aps\bin.
smartview.webservice.gzip.compression.disable=false
Open the Hyperion Planning application in question and change the SMARTVIEW_COMPRESSION_THRESHOLD in the System Properties (Administration/Manage Properties – System Properties tab) to a value no less than 1. This threshold is the minimum size of the query in which compression will be used. So, a value of 1000 would mean compression would be used for anything greater than 1,000 bytes.
For smaller queries, compression may not be necessary. It may even decrease performance because of the overhead to compress and uncompress the data. Every environment is different so there is no “right” answer as to what this value should be.
If you have used compression, please share your experiences.
Working with finance and accounting professional the majority of my career, I see a lot of spreadsheet “templates” that are reused for multiple budget passes or monthly forecasting processes (any repatative process). When the workbooks have a number of worksheets, and they are large, it can be extremely tedious to clear out the old data and get back to a fresh, empty shell. The script below can be executed on any worksheet to clear out all the numeric values and cell comments. It ignores cells with dates, formulas, or text values.
'loop through each cell in the range of cells used in the worksheet For Each c In ActiveSheet.UsedRange.Cells 'If the cell value is null, don't do anything If Not IsNull(c.Value) Then 'Do not execute on formulas or non numeric values If Not c.HasFormula And IsNumeric(c.Value) Then 'If a cell comment exists and it is not equal 'to "KEEP", set the value of the cell to null If Not (c.Comment Is Nothing) Then If c.Comment.Text <> "KEEP" Then c.Value = Null ElseIf c.Comment Is Nothing Then c.Value = Null End If End If End If 'Execute on all cells in the range where the cell has a comment If Not (c.Comment Is Nothing) Then 'If the comment is equal to "KEEP", don't delete the comment If c.Comment.Text <> "KEEP" Then c.Comment.Delete End If Next c MsgBox "Complete"
Breaking It Down
The outside loop will loop through each cell using the ActiveSheet.UsedRange.Cells. This function will get the range of cells on the worksheet that has been used. UsedRange will take the equivalent range of using CTRL-HOME to get the upper left cell and CTRL-END to get the bottom right of the range.
For Each c In ActiveSheet.UsedRange.Cells
…
Next c
Each cell will be checked to verify that the value is not blank, is not a formula, and is numeric (not text). If this criteria is true, the value will be set to nothing.
If Not IsNull(c.Value) And Not c.HasFormula And IsNumeric(c.Value) Then
c.Value = Null
End If
If the cell has a cell comment, it will be removed as well.
If Not (c.Comment Is Nothing) Then
c.Comment.Delete
End If
In the full example, some additional lines are added to ignore clearing any cell with a cell comment of “KEEP”.
How To Use
To use this script, it must be added to a module. The easiest way to do this is to create a macro and associate a CTRL-? key to it.
In Excel 2007, select the Developer ribbon and click the Record Macro button. Immediately click the Stop Recording button. This will create a function in a new module for you. If the Developer tab is not visible, click the Office Button and click the Excel Options button. On the Popular tab, select Show Developer Ribbon.
In Excel 2003, select the Tools / Macro / Record New Macro menu. Immediately click the Stop Recording button.
After opening Visual Basic in Excel, expand the spreadsheet in the Project window. Expand the Modules tree and open the module. Inside the module will be a procedure that is empty. Paste the script inside the procedure. This can now be accessed by the CTRL-? that was assigned.
I will be posting more scripts like this. If you find this helpful, add your email to our mailing list near the top of the right sidebar. You will get an email any time we add a new article!
Microsoft Excel does a great job of calculating only what is needed. If automatic calculation is turned on (and is by default), it only calculates formulas that have changed since the last calculations. If automatic calculation is turned off, F9 will accomplish the same thing.
CTRL F9 goes one step further and calculates formulas that have changed, as well as the formulas dependent on them.
CTRL ALT F9 calculates all formulas in the workbook, regardless of whether they changed since the last calculation.
When custom functions are used, Microsoft Excel doesn’t always know the dependencies because the function can reference cells outside those provided in the function arguments. When this occurs, using CTRL SHIFT ALT F9 is critical to ensure that all cells are calculated correctly. This rechecks dependent cells and calculates all formulas regardless of whether they have changed since the last calculation. This is the only way to ensure that all data is calculated.