Tag Archives: Easy Excel Analysis

Calculate NPV, XNPV, IRR, MIRR, and XIRR like a boss

So you need to calculate the NPV, IRR, or any of the above financial values for one investment.  Simple, right?  In Excel, you just use the relevant function on the data e.g.

But what if you need to calculate the numbers for hundreds of investments, by different categories?  For example, say we have a worksheet containing the cash flows for over 200 companies, with varying starting and ending periods, grouped by industry, sector, and risk level.

Are you able to calculate measures like the NPV and IRR for individual investments or groups of investments in Excel fast?

What if we said you could compute and display the NPV and IRR measures like this using Easy Excel Analysis in 60 seconds?

Watch this video to see how.

It could not be simpler.  Once you’ve set up the summaries, it’s just a matter of dragging which columns you want to group and compute the measures for.

Download a 14-day trial now, and experience working with your data in ways you never thought possible and gain new insights into your data.

If you need to work with data stored in databases instead of Excel worksheets, then give SQL Data Analysis a try.  It provides the same analysis capabilities as Easy Excel Analysis.

Importing data in Easy Excel Analysis – sample walk-through

In this walk-through, we will be importing data from 13 Excel files containing financial transactions.  The end-result will allow us to compute summary values for a single grouped column e.g.

and for multiple levels of grouping e.g.

or

Prerequisites

Install Easy Excel Analysis using the installer located here.

The source Excel files

Our source files consist of 13 Excel files, each containing data for one month, except for February which consists of 2 files.

Note that the each file identifies the month the data belongs to in its naming convention e.g. OBE9940 01 2020.xlsx indicates that the data in this file is for January, and OBE9940 12 2020.xlsx is for December.

Each Excel file contains 2 worksheets.  We will be importing data from the first worksheet.

Importing the data

Open Easy Excel Analysis.  Click on the Import Excel/CSV file box.

Select the first Excel file to import.  This will be our primary source file.  Easy Excel Analysis opens the file and displays the first 50 rows.

The list of available sheets are displayed on the left, and the first sheet is selected by default.  Our source file contains some header details, and the actual data only starts from the 6th row.

Thus, we need to set the Skip first value to 5.

Our source file also contains a total at the end of our data block.

As we do not require this row, we need to set the Skip last value to 1 so that Easy Excel Analysis ignores this row.

Click Next and ensure that the column types have been identified correctly.

Click Next.  Here, we can set up derived values from the date columns in our data set.  We will skip this step.

Click Next.  Here, we can enter the other files we want to import.  Click on the Add button.

Select the files we want to import.  You can use the CTRL and SHIFT keys when clicking on the file names to select multiple files.  Click Open once you have selected the files.

The selected files are listed, and the default option is to import the data from the sheet of the same name as our primary source file.

Unfortunately, our source files do not have a consistent naming convention with regards to the sheet names.  Thus, we will need to change the import option to import only from sheet 1.

Our source files do not contain any values that identify the month each transaction.  Thus, if we import the data now, we cannot identify which month  the transaction belongs to.  As this information is important to us, we need to add an additional column to our combined data.

We can either open each worksheet and add a column containing the month value, or add a new column using Easy Excel Analysis.  To do that, click on the Add button in the additional columns section.

This brings up the Add additional column window.

We will name this column MONTH.  As we will be getting the value for this column from our file name (recall that our file names contain the numeric month value), we will specify a number type.  The month value in our files names is the 2nd value, so we select the filename 2nd value as the source for this column.

Easy Excel Analysis recognizes spaces, dashes, and underscores as separators for values, so any of these file names in our example would provide the correct month value.

  • OBE9940 01 2020.xlsx
  • OBE9940-01-2020.xlsx
  • OBE9940_01_2020.xlsx

Click Next , and Easy Excel Analysis will then start importing from our primary source file and the additional files we listed.

Analyzing the data

Once the data has been imported, you can start grouping and summarizing the values.  Let’s start by creating a group summary value for the TOTAL column.

Right click on the TOTAL column header.  Select the Add group header summary item, then the sum function.

In the Summaries listing on the right panel, you should now see that a summary has been created for the TOTAL column, using the sum function.

Now drag and drop the TYPE column header to the column grouping area.

The data rows are now grouped by TYPE, and the summary values calculated for each TYPE value.

Expanding any of the TYPE value will display the details for that value.

We can create as many grouping levels as we require simply by dragging and dropping the columns into the grouping area.  Say we want to see how much we earn from each client, then by each plan.  We just need to place the CLIENT NAME and PLAN columns into the grouping area.

We can create additional summary values using the same steps for the TOTAL column.  Say we want to display the total COMMISSION value at each grouping level.  We right click on the COMMISSION column header, select the Add group header summary item, then the sum item.

The summary is then calculated and displayed immediately for each group.

Saving the grouping layout

You can save each grouping layout by clicking on the Save current layout button.

Enter a name for the layout.  You can then later apply each saved layout by clicking on the Apply layout button and select the grouping layout you want to use.

Saving the data for future use

To save this consolidated data, click on the Save the current analysis data to a data file item, and enter a file name.

The next time you want to analyze this set of data, click on the Open data file box in Easy Excel Analysis and select the data file.

You can also save the consolidated data to an Excel worksheet.  Click on the Export to Excel item in the Export options.

Enter the file name, and the layout or data option.

If you want to export only the rows without any grouping applied, ensure that your analysis table does not contain any grouping rows before you start the export.

Customizing the appearance

The Appearance tab on the Customize panel allows you to change the appearance of the displayed data.

The Skin option allow you to quickly change the look of the data e.g.

 

Overcoming Excel (and other product) technical limitations using Easy Excel Analysis

We recently had an interesting support call from an Easy Excel Analysis trial user, who described to us various ‘interesting’ technical issues, which we were happy to solve.

Excel’s worksheets are limited to a million rows.  The user wanted to analyze data spread over 4 Excel files, totaling over 1.7 million rows.  So she used another application that was supposedly able to combine all the rows from the Excel files, and export them into a single CSV file.

Unfortunately, that application failed for unknown reasons.  So while she was interested in evaluating Easy Excel Analysis, she felt stuck because she couldn’t consolidate all the 1.7 million rows from the 4 Excel files into a single CSV file.  She was also unsure if Easy Excel Analysis could handle that many data rows.

First off, in Easy Excel Analysis, you can work with a data set containing millions or tens of millions of rows, as long as your computer system has adequate RAM and a fairly good CPU.  For e.g. to work with 1.7 million rows, you would require approximately 6 GB of RAM to work comfortably.

Secondly, Easy Excel Analysis can import data from multiple Excel files and combine them into a single data set.  The user did not need to consolidate the data herself.  She just needed to prepare the settings to import the first Excel file, then enter the names of the additional files to import from.

In this way, you can combine the data from one or more Excel files without having to rely on other applications.

Once she was able to do that, she still wanted to export the data to a CSV file.  Guess what?  She can do that in Easy Excel Analysis too.  Just select the Save the current analysis data to a data file option, and all 1.7 million rows is exported to a single CSV file.

So as it turns out, all the questions related to the technical aspects of getting data into and out of Easy Excel Analysis, which was solved without much fuss.

Download a 14-day trial and give Easy Excel Analysis a try.  You might just find new ways to work with your data you previously thought impossible.

Why do you need to define a period for the IRR, MIRR and NPV financial functions in Easy Excel Analysis and SQL Data Analysis

Easy Excel Analysis and SQL Data Analysis added support for financial functions in version 2.  One quirk you may have noticed is that for the IRR, MIRR, and NPV functions, you need to enter the column containing a period.  Excel doesn’t require this, so why is it required here?

Let’s take a step back and use a simple example in Excel to explain this requirement.  Say we have a single investment we want to calculate the NPV for.  We would list the net cash flow in chronological order and calculate the NPV accordingly.

The point to note is that the order of the net cash flow needs to be sorted by period to be accurate.  If the values were not sorted, we would not get the correct value e.g.

Now say we have to deal with multiple ‘groups’ of investments.

In the example above, we are calculating the NPV for each investment.  In Easy Excel AnalysisSQL Data Analysis, we get the same values when we group our data by the Investment column.

Now say we want to compute the NPV of each category of investment.  In Excel, we can’t just sort by the Category column to compute the NPV.

neither can we just sort by both the Category and Period columns this way:

The correct way is to sum the Net cash flow values for each period by category i.e.

To perform the same computation In Easy Excel Analysis and SQL Data Analysis, we just drag the Category column to the grouping area like this:

That is the reason why Easy Excel Analysis and SQL Data Analysis requires a period value when calculating NPV, IRR and XIRR.  It uses the values in that column to determine how to group the cash flow values when grouping rows together.

To see the NPV value by individual investments,  we just drag the Investment column to the grouping area.  Now we can easily see the NPV per category and per investment.

Thus it’s just a matter of dragging and dropping which columns you want to group by, and Easy Excel AnalysisSQL Data Analysis will calculate the NPV for you at each grouping level.  It could not be easier.

Download a 14-day trial of Easy Excel Analysis or SQL Data Analysis, and start analyzing data in ways you never could in Excel, or would never do because it was too time consuming.

Financial and statistical functions in Easy Excel Analysis and SQL Data Analysis

In Easy Excel Analysis and SQL Data Analysis 2, we added statistical and financial functions to the analysis tables.

To use these functions, right click on the column header you want to compute to bring up the context menu.

Select the advanced item.  This then brings up the Advanced Summary Item window where you can define the data source for the various parameters required for the function you select.

See also:

 

Importing from multiple CSV files in Easy Excel Analysis

To import data from multiple CSV files in Easy Excel Analysis, first set up the import settings for a single file as described here.

NOTE: The layout and format of the data in all the additional files must be identical to that of the initial file. 

When you reach this page of the import wizard, you can then set up the additional files to import from.

To add files, click on the Add button below the file list window.

If you need to be able to identify each data source and your worksheets do not contain any columns that identifies the source data, you can add additional columns on this page to help identify the data source.  Click on the Add button below the additional columns list.

On this page, enter the column name, type, length, and source of the column.  There are several options available to use the filename or worksheet details.

Using an example file name of G:\data\Sales_Osaka_July2020.xlsx,

  • filename = Sales_Osaka_July2020
  • filename 1st value = Sales
  • filename 2nd value = Osaka
  • filename 3rd value = July2020

Note that the separators can be a dash symbol (e.g. Sales-Osaka-July2020.xlsx), a comma (e.g. Sales,Osaka,July2020.xlsx), or a dot (e.g. Sales.Osaka.July2020.xlsx).  You cannot mix separator symbols e.g. Sales-Osaka.July2020.xlsx.

For further details, please refer to this topic in the help file.

Importing multiple worksheets and/or Excel files in Easy Excel Analysis

To import data from multiple worksheets and/or Excel files in Easy Excel Analysis, first set up the import settings for a single worksheet as described here.

NOTE: The layout and format of the data in all the additional files and worksheets must be identical to that of the source worksheet. 

When you reach this page of the import wizard, you can then set up the additional import sources.

To add a file, click on the Add button below the file list window.  For each additional file that you want to import, select the option that best fits your needs.

If you want to import data from all the worksheets in a single file, first set up the import settings for any one of the worksheets in that file.  Then on this page, add that file again and select the Import from all worksheets option.  Easy Excel Analysis will not import from the same worksheet twice.

If you need to be able to identify each data source and your worksheets do not contain any columns that identifies the source data, you can add additional columns on this page to help identify the data source.  Click on the Add button below the additional columns list.

On this page, enter the column name, type, length, and source of the column.  There are several options available to use the filename or worksheet details.

Using an example file name of G:\data\Sales_Osaka_July2020.xlsx,

  • filename = Sales_Osaka_July2020
  • filename 1st value = Sales
  • filename 2nd value = Osaka
  • filename 3rd value = July2020

The same extraction rules apply to worksheet names.  Note that the separators can be a dash symbol (e.g. Sales-Osaka-July2020.xlsx), a comma (e.g. Sales,Osaka,July2020.xlsx), or a dot (e.g. Sales.Osaka.July2020.xlsx).  You cannot mix separator symbols e.g. Sales-Osaka.July2020.xlsx.

For further details, please refer to this topic in the help file.

Easy Excel Analysis Guide

The Easy Excel Analysis Getting Started Guide

getting started
Explains how to import an Excel or CSV file into an analysis table.

formatting date and time values in CSV files
Explains how to import date and time values from CSV files when the format differs from the local machine regional settings.

importing from multiple Excel worksheets and/or files
Explains how you can import data from multiple worksheets and/or files.

importing from multiple CSV files
Explains how you can import data from multiple CSV files.

using data files
Explains data files, and how they can be used to share data with other users without having to grant them access to your databases.

using analysis tables
Explains how to create groups, summaries, and use charts with your data sets.

working with copies of the same data sets
Explains how you can create multiple analysis and pivot tables from the same data set without having to repeatedly run the same query.

working with analysis table columns
Explains how to hide and freeze columns, and create multi-row headers in your analysis tables.

working with charts
Explains how charts can be used to get a big picture analysis of your data set, and used as a navigation tool to support the analysis table.

Getting started with Easy Excel Analysis

To create an analysis table from an Excel file using Easy Excel Analysis, here are the basic steps to follow:

Importing your Excel file

Click on the Import Excel/CSV file item, and select the Excel file you want to import.

Select the worksheet you want to import data from.

A preview of the selected worksheet is displayed on the right.  Adjust the settings accordingly if your worksheet does not have a header, or has blank rows before the data rows.  Click Next.

On the next page, adjust the column types where necessary.  Check that the date and time columns are identified correctly.  If the columns have been formatted as date and/or time columns in Excel, they will be identified correctly in Easy Excel Analysis.  Click Next when done.

On this page, you can add additional columns that extract individual elements from date and time columns.  In our example, we have chosen to add columns for the year, month, and quarter based on the Order Date column.  Click Next after you have added the elements you require.

On this page, you can choose to import data from additional Excel files and/or worksheets.  See this post for further details.

Click Next, and your Excel data will be imported into an analysis table.

Importing your CSV file

The steps to Import a CSV file is similar to importing an Excel file.  When you select a CSV file, the Import CSV File wizard displays a preview of the file.  You may need to adjust the settings to import your file correctly.  To determine if the settings are correct, click on the Preview button.

In this example, we see a strange character next to the first column description.

This is a common issue, and is fixed by selecting the Detect byte-order mark option.  Easy Excel Analysis will try to set this option automatically depending on the CSV file format.

Other common settings to change include the Field separator symbol (default is the comma symbol), and the quote symbol.

To check if the CSV file will be imported correctly, click on the Preview button to check the lines that will be imported.

The rest of the steps is identical to that for importing Excel files described above.

 

Working with copies of the same data sets in Easy Excel Analysis and SQL Data Analysis

In Easy Excel Analysis and SQL Data Analysis, you can create multiple analysis tables and/or pivot tables by clicking on the Add analysis table and Add pivot table buttons on the toolbar.

You do not need to re-import the Excel/CSV file (in Easy Excel Analysis) or re-run the query (in SQL Data Analysis).  Each analysis table and pivot table is independent, and you can customize the layout of each analysis and pivot tables to your needs.

Because each analysis/pivot table consumes memory resources, the number of tables you can create from each data set is determined by your settings.  In Easy Excel Analysis, this limit is set in the Data loading settings in the Import file wizard.

In SQL Data Analysis, the limit is set in the Data options settings.

You can increase this limit if you need to be able to create more analysis/pivot tables per data set, and your machine has the available memory capacity.