Tableau Hold ’em — Part 2 — The Flop Card 2

Featured

Posted on October 9, 2017 by alexzbiciak

Remember the need we are trying to answer is: “I want to know if I should bet or not!”

Easiest solution is a YES/NO output, but really?

Welcome to Part 2:

We will

Import your table to Tableau

Map coordinates

Wait! What?

Yes Tableau has a nifty X/Y method that allows you to map out your image and it is a must for this process.

Be sure you have saved your Excel Poker Table as a png ( you can use the Windows Snipping Tool)

YouTube walk-through

1 Create a data set in Excel that you can reference — 3 columns

Position
X
Y

2 Open the Poker Table in Paint and look at the properties

3 Copy the Width and Height in pixels and enter into Excel

Save the Excel file so it can be used as a Tableau Data Source

4 Connect to that data source

5 Open a new worksheet and go to the Map tab at the top of the screen

Select Background images

6 Add your image

7 Place the X/Y pixel counts into the image as X = the right most position and Y = the top most position

8 Create a cross tab with X as column and Y as rows

Things to consider:

In Map >> Background Images be sure you have “Always show Entire Image” selected

Be sure you select “X” and “Y” when you enter your data

You will now see your Texas Hold ’em table

You can hide the headers to make a clean worksheet

Up next –> Tableau Hold ’em Part 3

Previous steps:

https://excelirate.wordpress.com/2017/09/05/tableau-holdem-series/

https://excelirate.wordpress.com/2017/09/18/tableau-hold-em-part-1-the-flop-card-1/

The Problem With KPIs

Posted on February 28, 2017 by alexzbiciak

I was walking around the production area with one of my former bosses earlier in my career. I had recently been appointed to a position I had been performing for years and during this walk I was told that a list of approximately 7 metrics were what I needed to hit to succeed in the new role. And, as a newly anointed member of management, I would be measured against these KPI’s. I reviewed the 7 and I assured my boss that hitting the targets would be no problem, to which he looked astonished and then smirked. I felt a lesson coming on.

My boss, about 10 years my senior, assured me in a lightly condescending tone that I should try hard as he would very much appreciate that effort. It was apparent to me that he felt my over exuberance was simply showing naivety. I turned to him and said that I honestly have no doubt I can accomplish these objectives, the real question is do you really want me to?

He stopped, not knowing how to handle my defiantly subversive comment. He was genuinely interested at my statement and my apparent lack political correctness. From my point of view, to make money and succeed, we need to be brutally honest and do what actually needs to be done.

Anyone can make a KPI turn green on a report, but that doesn’t mean we have done what is truly needed for success!

Success? — Declaring KPIs can give us a managerial assuredness of progress and the successful meeting of business measures, but it does one insidious thing–

KPIs become the proof, unquestioned, that business is succeeding!

And often are used to justify the very actions which lead to business failure. KPIs are not the truth, nor should they be confused with the truth.

KPI stands for Key Performance Indicator and nothing else

We have divorced ourselves from the main point of a KPI –> Indicator. An indicator is nothing more than a touch point to begin to understand. The entry point for guided analysis. To put it another way, a KPI is not the analysis, it is simply the beginning. And no KPI should be ignored, regardless of the target attainment characterization.

Analysis must be of the underlying root causes, but to often is relegated to simplistic summary reporting of KPI values, often with the infamous change over time comparisons of the very KPI such as Year Ago or Like Periods –essentially an analysis of the change in KPI status.

Do the hard thing–ask your team:

“What is wrong with the company?”
“Why are we losing market share?”
“Why are we meeting the SLA?”
Etc.

Will the team give you the KPI’s restructured with trend over time?

How about a song and dance about a series of initiatives?

I suspect no true scientific testing, very little statistical analysis, etc. And driving this behavior is the fear of reprisal for saying “we do not know why” and who could blame them when the analysts’ jobs have been to refresh the standard reports and send them to the proper stakeholders for the last 5-20 years, with only the occasional investigation into if a negative color is the result of a data refresh issue, a master data change that broke a formula or just actual performance!

No one is at fault, it is the trap of using KPI’s incorrectly

Business success requires being on the forefront of analytical understanding of data and a divorce from the “all green” KPI dashboard as proof of success. If someone is hitting their targets, question it. If a department is green, show me the details. Run an analysis on the underlying data points, you will probably not like what you see. Prove why hitting this target did what we needed!

Accept a normal operating tolerance that allows for the “yellow to red” as an OK and normal part of doing business. Ignore nothing. We must accept that:

Not every project will be Green and nor should they be

Yellow marks deserve the same level explanation as a Green ones–imvestigaton beyond the indicator!

VBA To publish a Batch File from MS Excel

Posted on April 30, 2015 by alexzbiciak

In a previous post I described how to set up a batch file to create a list of files in a directory, specifically MS Excel files.

Sample Excel file with VBA in it.

Question:

Have you ever wanted to bring the data from a set of standardized files into one file using automation?

If so, this blog represents step 2—generating the list remotely. Refer to my previous post to learn how the batch file works.

Tools: MS Excel, MS Windows

Fast Answer–MS Excel VBA Code:

Sub Build_Bat()
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Build_Bat generates a batch file
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FileNum As Integer
Dim Filepath As String
Dim Folder As String

Filepath = <<Enter your file path here with a closing \>> & "filelist.bat"

'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
'
' Example: Filepath = "C:\Users\Alexander zbiciak\Batch Example\" & "filelist.bat"
'
'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’

'create spot for batch file

FileNum = FreeFile()

'open file

Open Filepath For Output As #FileNum

'write batch file

Print #FileNum, "pushd " & Folder
Print #FileNum, "filetorun -options"
Print #FileNum, "dir /b *.xls? > fileslist.txt"

Close #FileNum

End Sub

Before Macro is run:

After Macro is run:

How To:

First open up Excel and be sure you have the developer tab enabled in your ribbon:

File >> Options >> Customize Ribbon >> Developer

Select the Developer Tab and then the Visual Basic Icon to open your editor:

Inside the editor you will need to add a module if there are none or open one you want to add this code to:

With your module open copy the code below and paste it in (be sure to change your file path):

Sub Build_Bat()
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Build_Bat generates a batch file
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FileNum As Integer
Dim Filepath As String
Dim Folder As String

Filepath = <<Enter your file path here with a closing \>> & "filelist.bat"

'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
'
' Example: Filepath = "C:\Users\Alexander zbiciak\Batch Example\" & "filelist.bat"
'
'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’

'create spot for batch file

FileNum = FreeFile()

'open file

Open Filepath For Output As #FileNum

'write batch file

Print #FileNum, "pushd " & Folder
Print #FileNum, "filetorun -options"
Print #FileNum, "dir /b *.xls? > fileslist.txt"

Close #FileNum

End Sub

Test your macro by opening the file path in the Windows file explorer. Leave this open so you can watch the VBA output:

With the file explorer window to the side, place your cursor inside the VBA code and then click the play button:

Congratulations—You have now published a batch file from MS Excel.

Vlookup: Learn it, Love it and Leave it… Part 2 Enhancements

Posted on March 4, 2015 by alexzbiciak

In Part 2, I showed you how to add the dynamic aspects to a Vlookup formula, but we left a lot of room for error. In particular the user may enter data that cannot be found in the data. I showed how entering an “a” for item # failed, likewise the user may wish to enter January, not knowing or realizing Jan is the column header. Part 2 Enhancements shows you how to add validation rules to speed reporting by helping the user enter only what is needed.

Tools to be used: Data Validation.

First we want to be sure that the user only selects valid data.

To do this we will look at our two selections we made Item # and Month in cells C16 and D16. Select cell C16 and then go to:

Ribbon Data tab >> Data Validation >> Validation Criteria >> select List

Click on Data Validation to open the contextual/dialogue box for your inputs:

In the dialogue box navigate to the Allow: box.

Use selection box by activating the drop down and then select List.

In Source: enter the limited range (array) that has all of your items.

For this example we will use the data table itself. In more advanced files we would use a control table to limit our possible items from a master list.

We will enter: $A$3:$A$12.

Once you hit enter, the cell C16 will now show a drop down arrow allowing you to select only what is in the list you referenced.

You can do the same for the Months by selecting cell D16 and applying the list limitation with the source set to $C$2:$N$2. You can either manually type this or select the range with your mouse. Use the Practice tab of the linked file to build your own.

Congratulations, you have now limited the user entries and also helped ensure they are selecting the right items. You are on your way to building reports.

Vlookup: Learn it, Love it and Leave it–Part 1

Posted on September 6, 2014 by alexzbiciak

Vlookup Part 1

Vlookup and the corresponding Hlookup are a new to Excel user’s best friend and over time will become one of your least desired solutions.

Vlookups are used to quickly pull data out of large data tables and are often the go to formula for almost anything an analyst does. Understanding Vlookup is paramount as most Excel users will employ this method extensively and, therefore, you will need to understand what is doing and how to navigate it as well as solve for its limitations.

How the Vlookup works:

When you type in =Vlookup( in Excel you will be greeted with the formula format:

=vlookup(Lookup_value, table_array, col_index_num, [range_lookup])

Of course what this means is obvious to a new user! Great so now what?

Data:

You probably need to find a particular data point.

Likely your team or boss asked you to find how much of item X was sold on date Y in region Z or some other business scenario. So here you have a lookup item and some limitations. Before we dig deep let’s start with the item lookup.

Assume your company has 10 items and the all sales data is kept in a simple table like below:

(Click here or the image for the sample file to play with housed on Google Drive)

In the table above you have sales quantity data for item#’s one through ten and by month.

To answer your boss’s request your Vlookup formula will need to be limited by which item you need and then which month you need.(For database users the Vlookup is a much like an SQL Select Where statement)

The formula:

vlookup(Lookup_value, table_array, col_index_num, [range_lookup])

Lookup_value: The selected item you need to find data for

Col_index_num: The column where the date you need data is located

Table_array: Excel description for the table–its spreadsheet location. We will tackle this in a minute.

[range_lookup]: Set this to False–False returns exact data

Suppose we want Item 1 sales quantity in January. Our Lookup value can be either the Item # or Item Description. As long as we have one of the two we can lookup that item.

Let’s start with the Item # which is in column A. I have the reference column highlighted in the image below.

Your data is to the right of column A. Columns are sequential with the first column = 1. We want January data. Jan is column C, which is the 3rd column of the table so the column # = 3.

(Note it is important that you are counting column position from the lookup_value column as a table may not start at column A).

We now know:

Lookup_value is 1

Col_index_num is 3

To complete the formula we need the Table_array.

A table array is the start and stop cells designating the location for the data table. In the table we are using the table starts at cell column A and cell row 2. Excel’s naming convention for cell location is column letter then row # thus A2.

In the below picture I highlighted the tables beginning cell and end cell in green. If you select a cell you will see the cell name populate in the area above the spreadsheet to the left. In this case I will select cell N12. You will also see the column and row indicators highlighted on the edges of the spreadsheet as well as N12 in the left top of the screen.

To define a range or array we need both the start and end cell names joined together with a colon. Thus this table range is

A2:N12

Notice Excel draws a blue outline around your referenced range:

Note: This range or array reference is referred to as an unanchored range. Some techniques in Excel will cause your unanchored reference to change when using drag, copy/paste, etc. Be sure to check your references.

You may opt for the anchored version which forces the reference to remain unchanged by adding in $ before the column and row indicators like so:

$A$2:$N$12

You can anchor by typing the dollar signs or by pressing F4. You can anchor the column, the row or both, Highlight the A2:N12 and press F4 1 time to anchor both (details for another post as to how, when and why).

Your formula will now look like this:

=VLOOKUP(1,$A$2:$N$12,3,False)

The output of this formula should be 5. Hit enter and you will see the answer:

Congratulations you have now used the Vlookup. Try one of my next guides to see what you can do with Vlookups!

Please Look at my additional posts as you delve into VLookups:

Part 2

Part 2 Enhancements

	Stuart Max on Tableau HOLD ’em
	Tableau Hold ’… on Tableau Hold ’em —…
	Tableau Hold ’… on Tableau Hold ’em —…
	Tableau Hold ’… on Tableau HOLD ’em
	Tableau HOLD ’… on Tableau Hold ’em —…

Excel-i-rate

Conquering the data madness

Tag Archives: reporting