Here’s a fun little fortune cookie program. The fortunes are generated from a list of random words. I developed four different grammars for the fortunes, which you can find in the backend data. (Is “grammars” the right word? I’m not sure.) Some of the fortunes, like the one above, seem to make sense. Here are a few I’ve captured, which appear thoughtful and humorous, perhaps even poetic.
- The ugliest way finds evil.
- If you satisfy this empty girl generously, you are the one who … does it even matter?.
- Who wants the answer to be 42? The mammoth formula does!
- Who finds true love? The beautiful water does!
- The easy government gives up now.
- Want the story now? Reluctantly face your family.
Most fortunes, however, come out as nonsense. For example:
- Want the head now? Frenetically melt the day.
That’s great advice.
Feeling lucky? You should.
Fortune Cookie.xlsx (If the file doesn’t download immediately from Google drive, press the black down arrow in the upper left of the screen, under the menu.)
Most of my work is developed for wide screens monitors. How about you? I usually develop my work for 1366 x 768 resolution, but sometimes I’ll have to develop for different resolutions to accommodate my clients. Sometimes, I’ll include a fit-to-screen button that works will about 80 percent of the time.
So let’s do an informal poll. In the comments section, post your screen resolution of choice – and any tips you have for accommodating resolutions different from yours.
Also, have a great weekend!
Every once in a while, I have a spreadsheet that sits in my ‘lab’ for way too long. At a certain point, I have to admit that I’m never going to finish it completely or do a full blog post on it. I’ve decided just to release one such spreadsheet as is. Perhaps you will be the one to carry it forward? (or, write to tell me I’ve wasted my time?)
I while ago, I read about a phonetic algorithm called Soundex. Soundex is useful (well, there’s some argument about that) for searching through a list of surnames while compensating for common misspellings in those names. Using a dummy dataset and UDF developed by Richard Yanco, I created the following:
As the animation demonstrates, you can type in your search term into the ActiveX textbox and both the listbox and graph will automatically update to show you relevant search results and corresponding values. I’ve added some additional search functionality beyond Soundex as well. For each name in the dataset, I’ve assigned a random value to plot.
I’m rather unconvinced of the usefulness of this. I’ll put it you to see if you have any luck with it. For what it’s worth, the mechanism is formula driven. I didn’t use VBA except to set the original Soundex values for each surname (which, you could also probably do without VBA if you were so inclined.)
Download the source file:
I want to highlight two applications of the rollover technique that are really quite phenomenal.
Square Charts and Treemaps in Excel
If you haven’t seen the work of the Frankens Team yet – you’re missing out. They’re a group of…geniuses, really…who investigate and push Excel’s limit. Very recently, they wrote on the development of square charts and treemaps in Excel combined with the rollover method as shown below.
Read more about it, here. And be sure to look through the rest of their catalogue.
Mark Kubiszyn of Excel Boilerplate and Kubiszyn.co.uk used the technique in the development of two very slick boilerplate spreadsheets. His works adds a much needed streamlined process to creating and inserting new hyperlinks – as well, he’s expanded the ways in which you can link from hyperlinks. Please do take a look at his work, here. Also take a look at his YouTube videos on the subject.
Want to see more rollovers?
First, the updates.
If you’re an avid follower or even a casual reader, you’ve probably noticed the formatting of this blog seems inconsistent, even amateur at times. At least, that’s how I see it. Maybe it’s me, but Blogger just doesn’t seem to correctly handle anything I want it to do. I’ve recently heard, too, that some folks have had trouble publishing comments. I don’t really know what’s going on; Blogger just sucks, I guess. I think I’m going to switch to WordPress in the future. When that day comes, I’ll let everyone know.
Right now, I’m writing this from Windows Live Writer. Hopefully, it does a better job than blogger’s editor. We’ll see when this is posted.
Ok, now a book update.
My publisher told me I’ve sold 32 copies of my book on preorder. Holy Crap! Thanks guys. Some readers have told me Chapters 1 and 2 are already available if you preorder through the alpha program. I’ve received some really good feedback about them. Thanks to the folks who’ve read them and reached out to me. I’ll be adding a link to the book at the top of the blog. Unfortunately, because blogger sucks, the mere addition of another link will mess up my layout. Then I’ll have to spend a few hours redoing everything. Therefore, it may a while after I poste this before the link appears. Just hold tight.
Is Excel to Blame?
If you’ve been following the news recently, two major events have brought focus on the use of Excel for modeling and research. The first is the London Whale incident, in which JPMorgan employed a spreadsheet based model for their default credit swaps. According to JPMorgan’s internal report, “Spreadsheet-based calculations were conducted with insufficient controls and frequent formula and code changes were made.” Specifically,
…further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR, 129 although it is unclear by exactly what amount, particularly given that it is unclear whether this error was present in the VaR calculation for every instrument, and that it would have been offset to some extent by correlation changes. It also remains unclear when this error was introduced in the calculation.
The other event is this this critique of a paper called Growth in a Time of Debt by Harvard economists, Reinhart and Rogoff. (I’m not an economist by the way, so if I make mistake in what follows, feel free to let me know.) From what I gather, Reinhart and Rogoff show that countries with incredibly high public debt (in excess of 90% of gross domestic product, I think), suffer from hampered economic growth. In the critique, authors Herndon et al accuse Reinhart and Rogoff of selective exclusion and coding errors. But what’s really become headlines is the fact that Reinhart and Rogoff used Excel for their analysis.
The argument is that Excel just isn’t a good program to use for data analysis. My response to this has been, “It’s a poor craftsmen who blames his [or her] tools.” To be fair, I actually stole this response from Jon Peltier. I really think it concisely describes how I feel. So kudos to Jon. I used the quote in a response on Andrew Gelman’s blog. I excerpt part of my response below because I think it’s a good lead-in to my overall point.
I remember in my elementary matrix algebra class some years ago, we had to perform Guassian elimination by hand. After the first test, it became clear to me I wasn’t paying close enough attention to my operations. Sometimes, if I wrote down a number too quickly, my hand would “skip” on the sheet of paper and make it appear as if I wrote a negative number. Imagine if I told my professor, “it’s not my fault, my paper isn’t rough enough to provide the adequate friction to perform these operations.” Or: “My pencil breaks off too much graphite, which makes my mistakes too hard to detect.” Or even: “My calculator’s buttons are too close. I can’t help fat fingering operations.” Even if could show that the pen, paper, and calculator actually contributed to my making mistakes, do you think my professor would (or should) care? Let me put it to you this way: What IF Reinhart and Rogoff had used only pen and paper? Should we blame BIC for making a pen unsuitable for analysis? Should we blame Mead for making unsuitable paper? I don’t think so. Reinhart and Rogoff’s analysis was faulty because they were inattentive. That’s not Excel’s fault.
JPMorgan, according to their internal review, knew the potential for error in the Excel model that ultimately became the London Whale scandal. Managers had even recommended that the model be automated and audited, but never followed through. Why not? Why wasn’t the model thoroughly tested when it provided results that appeared counterintuitive? Because they liked the model’s results and its potential for big returns.
Reinhart and Rogoff—what makes them so different? Are we expected to believe that Excel is responsible for their not rigorously unpacking and verifying their data and model? To me, a more reasonable answer is they really liked their results; and their reviewers really liked their results; and economists looking for more ammunition against government spending loved their results. Their results meant big impact.
That’s my thesis: there’s too much pressure to make an impression, to be different, to be sensational. We’re less likely to be skeptical of work that plays on our own confirmation biases, especially if it’s going to be huge. That same sensationalism, by the way, is behind the blame-excel bandwagon. Here are some headlines: “Quote of the day: Excel error destroys the world” from Mother Jones; “Microsoft Excel: the ruiner of Global Economies?” from arstechnica. To me, blaming Excel feels like more causation creep. Sure, Excel was used both by JPMorgan and Reinhart and Rogoff—but so what?
The real problem
The real problem concerns how we, as a society, treat data and research. For some reason, data is given a privilege status in our society. We aren’t as skeptical of it as we should be. We believe first and question later; that is, only if things go wrong—and, at some point, they will. When our news presents a study to us, we tend to interpret the results as being hard facts and not the output of a model that is potentially as flawed as the Humans who devised it. We look to technology to solve our problems, and when technology “fails” to meet our expectations, we toss it out as if it were a consumable good, like an old cell phone. But this view of technology, as the great solver—or destroyer—of the world’s complex problems is foolhardy at best and catastrophic at worse. It removes responsibility from our institutions and companies to make something better with time, instead of simply making it newer. It removes the responsibility from us to apply rigor to our own work.
Sensationalism drives the backlash against Excel. And don’t get me wrong, there are many things to criticize Excel for. However, that someone did not provide the necessary rigor to their own work is not Excel’s fault. In fact, while everyone is arguing over whether Excel provides the necessary information assurance, they’ve missed a recent addition to Excel’s capabilities that should have everyone concerned.
Specifically, Microsoft’s recently unveiled GeoFlow add-in.
From a technology standpoint, the fluid-like movement of anything in Excel is likely to dazzle; it certainly feels impressive. But before you make a flyover tour of your data, consider that this type of visualization has lots of problems.
According to the write up in the Office Blog, GeoFlow should allow you to:
- Map Data: Plot more than one million rows of data from an Excel workbook, including the Excel Data Model or PowerPivot, in 3D on Bing maps. Choose from columns, heat maps, and bubble visualizations.
- Discover Insights: Discover new insights by seeing your data in geographic space and seeing time-stamped data change over time. Annotate or compare data in a few clicks.
Discover new insights? No, I don’t think so. We’ve been able to place data on maps for as long as I can remember. And mapping how data changes overtime can probably be better presented using a timeseries plot. What Microsoft isn’t telling you is that plotting a column chart in a 3-d environment makes values hard to discern and occludes data from view. Virtual environments are useful when the physical environment is critical to understand the data within (like, say, in a flight simulation training program). But the data presented above is only hurt by us the use of three dimensions. Truly, what do we gain by adding a horizon which only limits data in the distance is it would in real life? Why should we choose to see less and not more?
Let's face it; Microsoft's Excel spreadsheet program is certainly useful but it's also kind of dull to look at a flat 2D row of numbers. Isn't there some way that someone could turn these dull strings of numbers into something more exciting? Microsoft thinks the answer to that question can be found in a newly launched Excel add-on, GeoFlow. [emphasis added]
Let’s be concerned about this. Let’s not argue over which technology best allows us to forgo a certain level of rigor in case we make a mistake in our analysis. Instead, let’s focus our concern on technologies that research shows cannot deliver on what they promise. In my book, I argue that vendors are trying to remove the decision making process from us. They want us to trust their gimmicky programs and tell us that we humans are too error prone to make decisions on our own. We need data visualization and dashboards to make the decisions for us, they argue. Things have just gotten too big, and too complex.
It’s true we Humans are error prone. But we can analyze our actions and intentions, and start anew. If our Excel analysis is wrong, we need the ability to unpack and analyze what’s going on. When something pretends to do the analysis for us, to show us new insights like never before, let’s be skeptical. What happens when we rely on a technology that does not allow us to apply rigor? what will happen when it doesn’t deliver? should we stop using it and throw it away? I can see why people view that as the only option.
We should be happy that Excel doesn’t do the work for us. Because when we make mistakes, and when we learn form our mistakes, that’s progress. That’s making the world better, not just newer.
What to expect
First, if you're into the e-book thing, you can go to my publisher's website and order a copy through their Alpha Book program. The Alpha Book program allows you to see each chapter as they are approved by my editors. The chapters will still be in development, so if you're the type who likes giving feedback, you could email me directly with your thoughts. If you do choose to do the Alpha Book program, and you do frequently email me with solid feedback about each chapter you've read, I can maybe refund your purchase or even send you a signed copy of a print book.
The DataFirst, I'll need some data. I use Dick Kusleika's Random Data Generator to get some quick data. I selected the fields I needed to generate and voila! 20K records very quickly.
I converted the entire range of data to an Excel Table. I named the table, "tblData"
The AnalysisTo analyze the data I went to another sheet and setup some criteria fields based on the Field Headers.
Think of this section as a Page Field in a Pivot table. You do not necessarily have to setup your analysis in this manner. It is just a structure I use as it makes sense to me.
"Page Field" - Any criteria that is common to all formulas may be left in his section
- Any criteria that is unique to each formula may be placed in this section
The CriteriaI entered all of the crieria in each cell that I am interested in
The Formula - Long FormI wrote a formula using Excel Tables and Structured References
Not too bad. But it is a little long and hard to read. The image causes us to have to scroll left right on the blog post to see the whole thing so we lose clarity of what is on the left. I'll "wrap" the formula in the formula bar to see if that clears things up.
The Formula - Wrapped FormTo "Wrap the formula in the formula bar, click before the text where you want to insert a break, then hold down the [ALT] key and press [ENTER]. I'm going to add a break for each Field/Criteria set so each set is on its own line in the formula bar.
Looking better. I'll see what it looks like if I indent each line as well. To indent a line, use the space bar to mve the text to the right.
The Formula - Wrapped And Indented FormI indented all lines after the first line to line up the name of the table.
I think this version is the easiest to read and understand.
I drilled in to the data, In this instance, there was only 1 line item that met the criteria so validation of the formula results in this instance was easy.
Download a copy of the workbook
Let us know how you use wrap and indent in your formulas in the comments section below.
The Cell Styles Group:
List Cell Styles:I opened a workbook, Alt+F11 for the Visual Basic Editor and cooked up the code below. I then opened another workbook and made sure the first sheet was active by clicking on cell $A$1.
I then ran this code:
1: Option Explicit 2: Sub ListStyles() 3: 'List all styles in a workbook 4: Dim C As Range 5: Dim rng As Range 6: Dim i As Long 7: Dim lRows As Long 8: With Application 9: .ScreenUpdating = False 10: .EnableEvents = False 11: End With 12: With ActiveWorkbook 13: 'Add a temporary sheet 14: .Sheets.Add before:=Sheets(1) 15: 'List all the styles 16: For i = 1 To .Styles.Count 17: ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _ 18: .Styles(i).Name 19: Next i 20: End With 21: 'Tidy up 22: 'Destroy objects 23: Set rng = Nothing 24: Set C = Nothing 25: 'Excel environment 26: With Application 27: .DisplayAlerts = True 28: .EnableEvents = True 29: End With 30: End Sub
This returned 47 different styles. I am not going to list them all here. They are included in the workbook that you may download at the end of the post. Your workbook may have more or less depending on any customizations you have already made to your workbook or if you are working on a workbook you received from someone else.
Once you have a list of the styles, you may edit the list for thes styles you wish to keep or leave them all on the list if you wish to delete them all.
Let's see what happens if we delete all of them, shall we? This will only impact Cell Styles. It will not impact Table Styles or Chart Styles.
Styles Group Before Delete:
Delete Cell Styles:Here's the code I am going to use to delete all cell styles from the cell styles group.
1: Option Explicit 2: Sub ClearStyles() 3: 'Deletes all styles from the active workbook 4: Dim lRows As Long 5: Dim C As Range 6: Dim rng As Range 7: With Application 8: .ScreenUpdating = False 9: .EnableEvents = False 10: .DisplayAlerts = False 11: End With 12: 'Make sure to click on sheet with list of styles to be deleted 13: 'Assumes list begins in $A$1 14: With ActiveSheet 15: lRows = .Cells(Rows.Count, 1).End(xlUp).Row 16: Set rng = Range(.Cells(1, 1), Cells(lRows, 1)) 17: End With 18: With ActiveWorkbook 19: For Each C In rng 20: On Error Resume Next 21: .Styles(C.Text).Delete 22: .Styles(C.NumberFormat).Delete 23: Next C 24: End With 25: 'Tidy up 26: 'Destroy objects 27: Set rng = Nothing 28: Set C = Nothing 29: 'Excel environment 30: With Application 31: .ScreenUpdating = True 32: .DisplayAlerts = True 33: .EnableEvents = True 34: End With 35: End Sub
Deleted 46 out of 47. Apparently cannot delete all styles or at least the "Normal" style.
Chart styles still work
You may now add any custom styles to your workbook. But please, no Gangnam Style :-)
More on Styles and VBA from Jan Karlel Pieterse
Download the Cell Styles workbook here
How do you work with Cell Styles and VBA? Let us know in the comments section.
Here is what I would like the final chart to loo like. Note the white dashed lines in front of the columns:
Setup:I started with the chart I ended with in my last post, Points Of Interest.
I then added some data to a separate worksheet in my workbook to replicate the horizontal gridlines.
Note in Col A that I used the exact same scale that I used for Series 1 which is what was used to plot the column charts (Really 1 chart)
Also note that I put data that is used for formatting on a separate worksheet from "Value Data". This makes it easier to maintain data as it needs to updated overtime
1. Horizontal gridlinesI am going to add fake horizontal gridlines to the chart, so first I'l remove the current horizontal gridlines.
- Click on the chart (This will activate the Chart Tools Group on the Ribbon)
- Click on "Layout" in the chart tools group
- Click on gridlines
- Click on Primary Horizontal Gridlines
- Click on "None"
2. Add "Fake" horizontal gridlinesI copied all data from the formatting tab including the horizontal axis column, clicked on my chart and pasted the new series.
3. Change the chart type:First I need to chage the chart type for each series that is not a value series which was series 1. So I'll loop through the SeriesCollection starting at number 2
1: Option Explicit 2: Sub SetChartType() 3: Dim wb As Workbook 4: Dim ws As Worksheet 5: Dim i As Integer 6: Set wb = ThisWorkbook 7: Set ws = wb.Worksheets("Sheet1") 8: With ws 9: .ChartObjects(1).Activate 10: For i = 2 To .ChartObjects(1).Chart.SeriesCollection.Count 11: .ChartObjects(1).Chart.SeriesCollection(i).ChartType = xlLine 12: Next i 13: End With 14: 'Tidy up 15: Set ws = Nothing 16: Set wb = Nothing 17: End Sub
4. Format the color of the linesI would like each of the horizontal lines to be white
1: Option Explicit 2: Sub SetChartColor() 3: Dim wb As Workbook 4: Dim ws As Worksheet 5: Dim i As Integer 6: Dim lWhite As Long 7: Set wb = ThisWorkbook 8: Set ws = wb.Worksheets("Sheet1") 9: lWhite = RGB(255, 255, 255) 10: With ws 11: .ChartObjects("Chart 1").Activate 12: With ActiveChart 13: For i = 2 To .SeriesCollection.Count 14: .SeriesCollection(i).Select 15: With Selection 16: .Format.Line.ForeColor.RGB = lWhite 17: End With 18: Next i 19: End With 20: End With 21: 'Tidy up 22: Set ws = Nothing 23: Set wb = Nothing 24: End Sub
5. Change the line type:The chart is looking pretty good. But the lines are a little thick, I would like something more subdued. I played around a bit with different line weights and dash styles until I found values that I liked
1: Option Explicit 2: Sub SetLineProperties() 3: Dim wb As Workbook 4: Dim ws As Worksheet 5: Dim i As Integer 6: Dim lWhite As Long 7: Set wb = ThisWorkbook 8: Set ws = wb.Worksheets("Chart") 9: lWhite = RGB(255, 255, 255) 10: With ws 11: .ChartObjects("Chart 1").Activate 12: With ActiveChart 13: For i = 2 To .SeriesCollection.Count 14: .SeriesCollection(i).Select 15: With Selection 16: .Format.Line.Weight = 1.5 17: .Format.Line.DashStyle = msoLineRoundDot 18: End With 19: Next i 20: End With 21: End With 22: 'Tidy up 23: Set ws = Nothing 24: Set wb = Nothing 25: End Sub
I added the major horizontal gridlines back to the chart and made them very thin and light grey
The final chart:
The lines may be a little thin for your tastes, just adjust the line weight until you get the effect you are trying to achieve.
I hope you enjoy the post and that you find something of value in it.
Hello World! (Sorry, I could not resist J) I was recently asked by our host, Jordan, if I would be interested in being a guest author here at Option Explicit VBA. I quickly and humbly accepted. I will strive to do my best to add something of value. Let’s dive right in.
I was inspired the other day by Chandoo’s post on his blog in regards to tax burden as well as Jared’s subsequent submission regarding service levels. Both charts use a consistent color across what appears to be different series in panel charts that are arranged closely together.
In fact, they are not, the area charts are one series with blank rows or columns inserted in the data range to create the separated effect. Here is a sample initial column chart I created using the same concept
Looking good, but I'll need to manually select an additional 22 data points and change the fill color for each point. It gets worse if I want to add additional, "Series" to the chart or decide to go back and change a color - more manual work!
So I thought to myself, "Self, there must be an easier way!" The good news is that there is an easier way through VBA! Let's cook up some code (Option Explicit VBA - Remember?)
I only have one ChartObject with one SeriesCollection, so that part is straight forward. But there are many points in the SeriesCollection to be considered. Additionally, I dont want to plot anyting or add color to anything for points 13 and 25 where I have blank rows in my data.
So, I want to do something with points 1-12, 14-25, 27-38. Sounds like a good candidate for a Select Case..Case..End Select structure.
Option Explicit 1: Sub ColorDataPoints() 2: Dim wb As Workbook 3: Dim ws As Worksheet 4: Dim i As Integer 5: Dim lBlue As Long 6: Dim lRed As Long 7: Dim lGreen As Long 8: Set wb = ThisWorkbook 9: Set ws = wb.Worksheets("Sheet2") 10: lBlue = RGB(79, 129, 189) 11: lRed = RGB(192, 0, 0) 12: lGreen = RGB(155, 187, 89) 13: With ws 14: For i = 1 To .ChartObjects(1).Chart.SeriesCollection(1).Points.Count 15: Select Case i 16: Case 1 To 12 17: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lBlue 18: Case 14 To 25 19: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lRed 20: Case 27 To 38 21: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lGreen 22: End Select 23: Next i 24: End With 25: 'Tidy up 26: Set ws = Nothing 27: Set wb = Nothing 28: End Sub
Now I have a chart with one x-axis and what appears to be 3 different series, when in fact, it is one. Perhaps more importantly, I have a process that requires very little updating as my needs change to display more "Series" or to change colors.
More on the Points Collection.
Download the workbook .
How do you work with points in your charts and VBA? Let us know in the comments section.