Calculate financial fiscal year and quarter based on some given dates

Is very well known that US financial fiscal year and quarters are not the same as calendar ones. As a result, is pretty much common in US companies to be required to calculate these based on some dates representing accounting information. In general, management needs to present financial reports at the end of each quarter or year.

How is typically solved? In almost 100% of the cases, people are using very long Excel formulas or based on VBA or even calculated SharePoint fields if data is stored into a SharePoint list. I have nothing against, just I do not like to complicate things. There is an easier way to do this with a little help from Power Query as you can create simple custom formulas to extract the information you need.

Let’s take a look on the formula to calculate fiscal quarter.

let
    fiscalQuarter = (fd as nullable date) =>
    let 

     m = Date.Month(Date.From(fd)),    
     fm = if m = 11 or m = 12 or  m = 1 then "Q1" else 
          if m = 2 or m = 3 or m = 4 then "Q2" else 
          if m = 5 or m = 6 or m = 7 then "Q3" else 
          if m = 8 or m = 9 or m = 10 then "Q4" else  ""          
     in fm

in
    fiscalQuarter

I have created a formula which accepts a nullable date as parameter. I get the month number based on it and depending on it I return the quarter name. For 1st, 11th, 12th months the returned results is Q1, for 2nd, 3rd and 4th is Q2, for 5th, 6th and 7th is Q3 and for 8th, 9th and 10th is Q4.

To calculate the fiscal year is even an easier thing to do.

let
    fiscalYear = (fd as nullable date) =>
    
    let m = Date.Month(Date.From(fd)), y = Date.Year(Date.From(fd)),
        year = if m = 11 or m = 12 then y + 1 else y
    in year 

in
    fiscalYear

I get the year value from a date, which is always a number if provided number is not null, and for 11th and 12th months I increase it with 1 unit.

I found this was easier than classical ways which sometimes generate ugly and long code. After all, who wants to work with very very long formulas in Excel formula bar or with calculated SharePoint columns which doesn’t always work as expected. And as a rule I am driven by in my career: a better solution can be much closer to you than you think. 🙂 Just do more research and you will probably find it.

Advertisements

Create custom formula in Power Query

Power Query is a very powerful extension and requires more attention. So, for today, I have decided how to write and use a custom formula. It will be a simple example to show friendly name of the months based on month’s numbers.

Let’s imagine we have this data.
PowerQueryDummyData
And now we want to show friendly name of the month for each date in the “Start Date” column. Of course we can use some VBA formulas as well, but let’s use something more modern. Just go to “Power Query” ribbon to create a blank query, which will contain our custom formula.Actually the sequence is: Power Query -> Get External Data -> From Other Sources -> Blank Query. We name the query “ShowMonthName” and in Advanced Editor we enter the formula (to enter in the editor respect the sequence: “Home” ribbon -> “Query” tab -> “Advanced Editor” button).
Custom formula
What we actually did? We create a formula called “ShowMonthName” which can be used in Power Query connection. And I am going to show how to do this.

Place mouse inside used range containing dates and do the following: Power Query -> Excel Data -> From Table. Excel will select by default entire used range, so you probably do not need to adjust it and you can go to the next screen, which is our query editor window. Here you go to Add Column tab and click on Add Custom Column button from General tab. And for the next step you need to name your column and insert our formula in formula text area control. This is straight forward and there no other explanation needed.
Add custom column
If everything is correct, you will see the results immediately in the query editor window and, if there is no error, you can use the connection you have just created, which includes the custom formula.
Formula results
I really encourage you to start using Power Query. It will open a new world for you and you will love using it, because it is a step forward in reporting and business intelligence. Leave behind ugly VBA formulas and start looking for the future.

Combining Excel worksheets with Power Query

Microsoft released an extension for Excel called Power Query. It makes some tasks easier, like combining Excel worksheets in a single one. This is the subject I am going to talk about, but in the future I will cover more about Power Query.

Let’s assume we have a worksheet containing foods.

Food Worksheet
Food Worksheet

And we also have another worksheet containing electronics.
Electronics Worksheet
Electronics Worksheet

Both worksheets have the same columns and we want to combine them into a single worksheet to display all data. With Power Query this becomes an easy task. You just need to follow these steps.

Creating connection from tables

Click inside a worksheet used range and from Power Query ribbon choose to get data from table.

Get data from table
Get data from table

You are asked to choose the range you want to use for data. By default used range is selected.
Choose range
Choose range

Query editor window is opened. Just give a name to your query (I named it “Table_Food”) and choose “Close & Load To” option from “Close & Load” menu. From the new window choose “Only Create Connection” as we already have data into table.

Load To
Load To

Repeat this operation for the other worksheet, of course giving a different name for the connection (like “Table_Electronics”).

Create the connection to combine the data

To create a connection to show combined data, choose “Show Pane” from Power Query ribbon.

Show Pane
Show Pane

On the “Workbook Queries” pane, right click on any data connection and choose “Append”.
Choose Append Option
Choose Append Option

On the next screen choose source and destination for append query.
Choose source and destination
Choose source and destination

Give a name for your new query (like “Combined Data”) and again choose “Close & Load To” option from “Close & Load” menu, of course only creating connection.

Display data

Now, as you have created all connections, go to “Data” ribbon and choose to get external data from existing connection.

Get data from existing connections
Get data from existing connections

Choose your connection which displays data from both worksheets and click “Open” button.
Choose connection
Choose connection

On the next screen is really up to you what you choose. You can choose to display data into an existing worksheet or you can tell Excel to create a new for you to place the data. You can consider now the task is done. 🙂

There is an interesting part with Power Query. If, for example, you add new data into one of the worksheets you used as a source and refresh data connections, Power Query knows and put new data into combined connection.

Adding a custom task in Excel add-in VSTO application

This article is describing how to add a custom task pane in an Excel add-in application. Custom task panes are very powerful and represent the right way to enhance Excel with custom functionality. So in case you want to create input form to collect data from the user, you just need to add a task pane which includes a user control (typically these are used in Windows forms application).

Because I did not want to repeat the code each time I am requested to add a custom task pane, I have add it to a static method.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Microsoft.Office.Core;

namespace ExcelApplication
{
    public static class ExcelUtils
    {
        public static Microsoft.Office.Tools.CustomTaskPane AddCustomTaskPane(System.Windows.Forms.UserControl userControl, string taskPaneTitle)
        {
            Microsoft.Office.Tools.CustomTaskPane pane = Globals.ThisAddIn.CustomTaskPanes.Cast<Microsoft.Office.Tools.CustomTaskPane>().FirstOrDefault(taskPane => taskPane.Title == "Forecast Report") ??
                                                         Globals.ThisAddIn.CustomTaskPanes.Add(userControl, taskPaneTitle) as Microsoft.Office.Tools.CustomTaskPane;
            return pane;
        }
    }
}

Each time I call this method, a custom task pane is added to the application with specified title. In case a custom task pane having specified title already exists, method is returning existing one. Of course in this case it doesn’t matter what user control you have specified and you should pay attention not to create a conflict.
Let’s see how we can use this in a concrete case.

           UserControl forecastReport = new UserControl();
           Microsoft.Office.Tools.CustomTaskPane pane = ExcelUtils.AddCustomTaskPane(forecastReport, "Forecast Report");

           pane.VisibleChanged += ((object s, EventArgs ee) =>
           {
               if (pane.Visible == false)
               {
                   Globals.ThisAddIn.CustomTaskPanes.Remove(pane);
               }
           });
           pane.Visible = true;

So, not only I just showed how to use my static method. I also showed also how to release resources when user hides the pane. But to be honest, if you want to even forward, you can adapt the static method a little bit.

        public static Microsoft.Office.Tools.CustomTaskPane AddCustomTaskPane(System.Windows.Forms.UserControl userControl, string taskPaneTitle, bool autoDisposeOnHide = false)
        {
            Microsoft.Office.Tools.CustomTaskPane pane = Globals.ThisAddIn.CustomTaskPanes.Cast<Microsoft.Office.Tools.CustomTaskPane>().FirstOrDefault(taskPane => taskPane.Title == taskPaneTitle) ??
                                                         Globals.ThisAddIn.CustomTaskPanes.Add(userControl, taskPaneTitle) as Microsoft.Office.Tools.CustomTaskPane;

            // Retun pane if auto dipose on close is false
            if (autoDisposeOnHide != true) return pane;

            // If auto dispose is set to true, we continue the code and define handler for visible change event
            EventHandler disposeOnClose = (object sender, EventArgs e) =>
            {
                Microsoft.Office.Tools.CustomTaskPane pn = sender as Microsoft.Office.Tools.CustomTaskPane;
                if (pn.Visible != false) return;
                Globals.ThisAddIn.CustomTaskPanes.Remove(pn);
                pn.Dispose();
            };

            // Bind the event handler to task pane visible change event
            pane.VisibleChanged -= disposeOnClose;
            pane.VisibleChanged += disposeOnClose;
            return pane;
        }

I have introduced a parameter called autoDisposeOnHide. If is set to true, when user hides the task pane, it is disposed and remove from task panes collection.

Happy Coding !!!