Daily Dose of Excel

Ross is live blogging the Excel Dev Conference in the UK. It’s already after lunch over there, but if you’re interested, check it out.

Recently, Microsoft introduced a way to create a “mashup” using Excel. Fellow MVP, Jan Karel, put together a tutorial on how he created his first mashup. It helped me understand the basics, which are also well explained by Microsoft itself.

In my case, for a long, long time I have wanted to draw any graph on a web page and do so easily. Some years back Google introduced an API that I experimented with but it never caught my fancy.

So, here’s my attempt with JavaScript and MS Excel. Of course, Microsoft has a less than stellar history on web-based Excel/Office products (Office Web Components comes to mind). But, maybe, this time around it will be different, given the push that the company as a whole is making for web-centric products.

Graph any Excel formula as a function of a single variable

I will post a “how I did it” article in a day or two together with links to whatever documentation / samples I could find on the ‘Net.

HTML5 provides for fractional representation of halves, thirds, fourths, fifths, sixths, no sevenths, and eighths. Excel has a fractional number format. This post is about bringing these concepts together for exporting an Excel table into Wiki or HTML designs. The basic representations are:

 
The format for the name is &fracnd; where n is the numerator and d is the denominator. Thus ½ is a half, and ⅞ is seven-eighths. The HTML code representations for these are:

 
In theory (more on “in practice” later) every representation in a row is equivalent. This is our test table to export to Wiki or HTML format:

 
Cell D1: =CHAR(32)&ROW()&”/”&COLUMN()-3, then fill down and right. The right hand column has a few test cases. I used that formulaic construction to keep Excel from doing the divisions. The Excel fractional format is “# ?/?” for single digit denominators. The intermediary space is important. It indicates that a fraction may follow, just as a forward slash indicates a fraction may be present. And a format of /?? is a fraction not translatable into HTML5. Turning these patterns into VBA, this is my MakeFracs() function. It checks that there is a slash, then that there is not a slash–digit–digit pattern, and finally that there is a “space–digits 1 through 7–slash–digits 2, 3, 4, 5, 6, 8″ pattern to screen out sevenths and ninths. If all of those pass, it substitutes in the &fracnd; formulation for the fraction.

 
At least that’s all I wanted it to do. In practice, Wikipedia and WordPress seem to be not fully onboard with HTML5 and do not handle all fifteen &fracnd; formats (I confirmed Firefox does). That added “j-loop” in the middle translates the &fracnd;’s into Dec code. This works fine, though it’s a step back from HTML5. Option is given to use Hex if desired. Your Excel table then looks like this:

 
Where N1: =MakeFracs(D1) filled down and right. Arranged that way you can see the HTML5 design thoughts. The very ugly website would look like this:

 
Frankly, I’m not sure that this is an improvement. You’ll come across Wikipedia editors, however, who are convinced of it. I thought about adding a trailing space, as in MakeFracs = VBA.Replace(Arg, sOUT, sIN & Chr(32)), but for every time I wanted to, I thought of an example where I didn’t, and the logic got very convoluted. Better I decided to put the space in the table where wanted and not in the function. My HTML tablemaker is here, but it’s being overcome by the hard steady march of technology. Wiki and CSS tablemakers are coming up. I used MakeFracs() in the above. No fractions were harmed in the making of this post.

…mrt
©¿©¬

I’ve been searching for a new search engine in the wake of Google’s ‘plus’ enhancements. I’ve tried blekko, ixquick, and I’m currently using DuckDuckGo. They’re all lacking, but I’ll let you know if I settle on one. In the meantime, I happened on kleemi and tried it out. I was startled that half the page was a donut chart, so I couldn’t resist trying these search terms.

Someone in Microsoft’s Excel forum had a question for which I don’t have an adequate response.

In A2:A7 enter the values:
6494.55
1311.36
198.08
124.9
131.81
131.81

In A1 sum the values with =SUM(A2:A7)

In A9 compute the same result by double counting all the values and subtracting the total, i.e., =SUM(A1:A7)-A1

In A10 check if A1 and A9 are the same. =A9=A1. The result is FALSE, i.e., they are not the same.

8392.51
6494.55
1311.36
198.08
124.9
131.81
131.81

8392.51
FALSE

Turns out that A9 has a 1 in the 11th decimal place. 8392.510000000010

Fair enough. There’s the 15 digits of accuracy thing going on and we have a rounding error.

Now, build the same model except this time put the A1 formula in A8 (and adjust A9 and A10 accordingly). The result is A10 shows TRUE!

6494.55
1311.36
198.08
124.9
131.81
131.81
8392.51
8392.51
TRUE

As far as rounding errors go, there isn’t one. This time A9 contains 8392.510000000000

Like I wrote at the beginning. I don’t know why the two models yield different results.

For several years now, Google has shown ads on my website and when someone clicked on one of the ads, it shared with me some of the revenue it earned.  For some time now I have had the impression that the Adsense revenues have been in decline – mine, not Google’s. {grin}

So, earlier this week I analyzed the performance of my Adsense account and concluded that it no longer made sense to show these ads.  This post discusses my analysis and the role of Excel in it.

The data from Adsense system included, on a month-by-month basis, information on number of ads shown, number of clicks, and revenue (for me, not Google).  It also included derived information such as the revenue per click, called Cost-per-click, or CPC, and the conversion rate (clicks/ads shown).  Of course the last two are easily computed from the first three data items.

Before going further, it might help to understand the different drivers of Adsense revenue.  I thought of 3 key elements:

1) The kind of ad Google shows.  It has to be relevant to the website visitor.

2) The number of times a website visitor clicks on an ad, and

3) The amount that Google pays me for the click.

Interestingly enough, the website owner has no control over any of those elements!

OK, back to the Adsense data.  Google Adsense has an option to download the data in CSV format.  So, after downloading into Excel, I “normalized” the data so that all the numbers for January 2006 were 100.  This had two effects: one could look at large numbers and small numbers in the same visual space, thus making trend comparison easier, and it masked the actual data.

[A technical note.  The month column included the start and end date of each month, e.g., 2006-01-01 - 2006-01-31.  So, I added a new column to get a month as Excel understands it, using the formula =DATEVALUE(LEFT(B4,FIND(" ",B4)-1)), and formatted it to show only yyyy-mm.]

Next, I created a PivotTable and PivotChart showing on a month-by-month basis the number of views, clicks, and revenue over time.  Since Google provided data for two products (Adsense for Content and Adsense for Search), I filtered the PT to show only Adsense for Content.

As Figure 1 shows, the views (in blue) have gone up while the clicks (in red) and revenue (in green) have dropped.  So, this makes it evident that item 2 above (number of clicks) has not fared well over the years.

Figure 1

Out of curiosity, I decided to check if the revenues-per-click (my share of what the advertisers paid Google) had changed over the years.  As Figure 2 shows, item 3 in the list above (the amount Google paid me per click) remained steady until early 2009, dropped in 2009 and the first half of 2010, rose in the latter half of 2010 to 2.5 times the 2006 level and remained steady through 2011.

Of course, since the total revenue continued to drop, it must mean that the drop in the number of clicks was far greater than the increase in the CPC.

Figure 2

To test item 1 in the list above (relevance of ads), I checked 3 pages at random.  The results were, frankly, surprising.  Given Google’s reputation of delivering accurate search results, I would expect website ads to be related to the website content.  That, amazingly enough, was not the case.

On a page that explains supply and demand curves, there were ads for power tools and plastic enclosures.

Figure 3

On the TM AutoChart page there were ads for “Find Autos Near You” and project management software.

Figure 4

And, on the TM Randomize Slideshow page there were ads for Google Chrome, Microsoft Private Cloud, reverse look up of cell phone numbers, and a network monitor.

Figure 5

I am sure the relevance of the ads to the website content must make sense to Google’s Adsense algorithms but it surely escapes me.

In any case, I decided to pull the Adsense ads, at least temporarily.

If you export a Profit and Loss statement from Quickbooks to a CSV file, you get dates that look like this:

Excel turns them into this.

January 2005 becomes January 5th of the current year. February 2005 becomes February 5th. That’s no good. In a spare row, I used this formula

to create the date I wanted. Then I copied and pasted special – values over the top of the original dates. It adds 2000 the day to get the correct year. Adding 1 to the month and taking the zeroth day will get you the last day of the month.

January 5, 2012 becomes January 31, 2005.

You’ve probably watched The Price is Right TV game show. On it they run the Clock Game.

The game is played for two prizes. The actual price of the first prize is shown to the studio and home audiences. After the contestant gives their first bid, a 30 second clock is started and the host tells the contestant whether the actual price is higher or lower than the bid. The contestant continues to bid, responding to the host’s clues, until either the contestant wins by correctly guessing the price of the prize or the time expires. If time remains after the first prize is won, the process is repeated for the second prize.…With few exceptions, only prizes valued below $1,000 have traditionally been offered in the Clock Game.

The algorithm to use is simple:

• Pick a ceiling ($1000) and a floor ($0)
• Bid the average ($500)
• If the bid is too low, the bid becomes the floor
• If the bid is too high, the bid becomes the ceiling
• Repeat

Being computer types, we’ll put the ceiling at $1024. For a $407-priced prize, your guesses would look like this:

 
Ten guesses, and you’ve won a washing machine. Here are the formulas that make this work.

• D1: = 407 (the unknown price)
• C3: = 0 (the initial floor)
• F3: = 1024 (the initial ceiling)
• D3: = (C3+F3)/2 (512—the initial bid)
• E3: = IF(D3=$D$1,”Stop”,IF(D3>$D$1,”Too High”,”Too Low”)) (the host’s clues)
• C4: = IF(E3=”Stop”,”",IF(E3=”Too Low”,D3,C3))
• D4: = IF(E3=”Stop”,”",(C4+F4)/2)
• E4: = IF(D4=$D$1,”Stop”,IF(D4>$D$1,”Too High”,”Too Low”))
• F4: = IF(E3=”Stop”,”",IF(E3=”Too High”,D3,F3))

Watch out for “curly quotes” if you copy and paste in. Filldown C4:F13. So what’s the point? We knew the “unknown price” going in. Here’s a recent prospective employee question the BBC got from Qualcomm:

Given 20 ‘destructible’ light bulbs (which break at a certain height), and a building with 100 floors, how do you determine the height the light bulbs break?

You watch The Price is Right or you read DDoE, and you think “Clock Game!” In 20 bulbs, if they break from a 407.407 foot drop, and a floor = 10 feet:

 
You’re 2-ten-thousandths of a foot off. You get the job, a great way to start the new year.

 
…mrt
©¿©¬

Depending on the requirements, I’ll choose a method for populating my custom collection classes using from data from a worksheet.

In this example, I’m using a list of the best selling albums of all time.

My Album class has properties as follows:

The code in my main routine is:

Filling the collection is just a matter of reading each row and popping the contained values into the right property.
The difficulty is knowing which columns relate to what properties. It’s a mapping problem – mapping columns to properties.

I could make an assumption about the positions of the columns and assume each is a known index.

I don’t really like this because I’ve been taught and have tried to follow the approach that offsets/indices like these should be defined as constants.
I’ll modify my FillFromSheet code.

Seems roughly better, but this too has problems. It can be a pain when you want to change the column order or insert a new column.
You’d have to go through the code and update all of the numbers.
While this is ok for 5, as I have here, changing out 50 columns is a chore.

So, I use an Enum block to determine column positions.
At the top of the class module:

And another modification to my FillFromSheet code:

That works well. If I rearrange my columns on the worksheet, the only code change needed is a swap of items in the Enum block – a painless and quick update!

But that’s only if the changes to column order are in my control.
What if an end user changes the column order? Do I really want them poking around in my Enum code too?
I’d usually stop now and decide that if the end user starts screwing with column positions, it’s fair enough that the code should break.
However, if I wanted to go the extra mile, I’d have to find the column index by searching for the text in the column headers.

Excel 2007 (and 2003 to an extent) has a feature called Tables, otherwise known as ListObjects.

My code in the main routine changes to:

… and the code in my collection class is:

In the preceding code I created a collection of key-value pairs. The key is the column label, the value is column index.
I use that as a lookup when populating my properties.

That’s just a few approaches to reading a table of values into a collection.
I’m interested in feedback. How do you map column positions to properties?

I have a utility that determines the date of the upcoming Friday. It used to look like this

It has served me well for many years. Until this year. This year, the US celebrated Veterans Day on November 11. While I’m work, the bankers are all at home rolling around in their money. Since my bank would not process any ACH transactions that day, I paid my employees the preceding Thursday. But my function didn’t say “return this Friday unless it happens to be a banking holiday” but now it does.

And the HasChecks property is just a loop

Man I hate when real life gets in the way of good code. I need to get rid of that arrow antipattern, but otherwise I’d say this is solved for all time. Jinx.