Monday, January 31, 2022

Pivot Table Calculated Item Solve Order Issue

If you've created multiple calculated items in a pivot table you might get into a situation where they are not showing up correctly. Somehow the calculations are wrong or don't look right for what you're trying to communicate. Often times it will be the way that the calculations are ordered that is creating the confusion. Re-arranging the sort order of the calculated items is then the solution. This video will cover what dialogue windows can be used to resolve the issue and also go through the steps of creating the pivot table with the "wrong" series of steps that lead up to the problem.

Monday, January 24, 2022

Excel MIRR vs IRR

When looking to compare similar projects tools such as IRR and MIRR are used.  But what's the difference between these two when used for capital budgeting ranking? A major difference is that for IRR the cash inflows are reinvested at the project's IRR, while the MIRR is using an alternative re-investment rate for the cash inflows.  The video covers the basics of how to use the Excel IRR and MIRR functions.

Monday, January 17, 2022

Quickly Change Multiple Queries, Same Source in Power Query

If you've created multiple queries from the same source and then the location of that source changes, you'd think that you would need to go into each query and one by one change the source location. If there's a few queries, it not so bad. If it's a LOT of queries, that will be painful. You can actually use a parameter and then tweak some source settings to let you change the source location all in the same time. Plus at the end of the video I'll show a simple design choice that could be used to mitigate all this without using a parameter.

Monday, January 10, 2022

Extract Text in Power Query

When you have some values to pull out of a text or numerical string in a cell, you can use the extract commands in Power Query to make it easy. This is an great option to use instead of using the RIGHT, MID or LEFT functions in Excel or trying to split the value by some delimiter. It's so much more easier to pull the data into the Power Query editor and using Transform and Extract to pull your desired text string out.

Monday, January 3, 2022

Reverse a String of Text with Power Query

Reversing text or a string of text seems like something that is common task in software development either as a entry into how to perform this task by writing code instead of using built in function (or it's an interview question!). It seems to be an simple task but thinking it through and putting it into code is something that helps engineers think about how to write code elegantly. How does Excel help in this? By using it to already reverse the text output, you can use Excel to help validate the output of the code. And using Power Query, it's really easy to get this output. Check out the video to see how.

Monday, December 27, 2021

Calculate Future Amount based on Changing Rates in Excel

If you were offered a guaranteed interest rate for a couple of years but the rates changed depending on the year, how would you know know what the future value would be? Say you're investing $1000 and for year one the interest rate is 5%, year two is 4.5% and year three is 4%. This is basically compounded interest on top of each year. It's fairly easy to do manually and even easier to do with the FVSCHEDULE function. Another fun way to use this function is to find out what historical figures would be worth today based on an inflation adjusted amount. The video will cover how this could be done.

Monday, December 20, 2021

Compare Economic Data and Stocks with CORREL Function

If you're doing any investing whether it be stocks, bonds or ETFs you're going to do research. One of the avenues of research is to look at macro economic data to help you forecast. One great repository of economic data is the St. Louis Federal Reserve. They actually have an Excel Add-in where you can get decades past data and it's all free! Couple that with stock data you can get from Google or Yahoo finance, and you've got all the data you need to do some correlation analysis with macro economic data and individual stock data.