Sunday, October 14, 2007

Pulling Images into Excel

The other day I did a quick $50 Project for a guy who asked if there was a way to automatically pull images into a spreadsheet based upon a values in a range of cells...you know, like for an inventory list or something.

Sure.

Actually, the VBA code involved is quite simple -- one line really. Let's say that the value in the cells is actually the name of the image (only without the file type extension) that you want to pull in. That code might look like this:

ActiveSheet.Pictures.Insert(<IMAGE_DIRECTORY> & "\" & cel.Value & ".jpg").Select


Pretty simple. Now, one tip that I'd pass along when working with images or other similar objects via VBA in Excel is to immediately rename the object, since it can become cumbersome trying to figure out if it's called shape1 or shape2 or shape 300, etc. So I would rename the image to that same value in the cell, so it's easy to reselect later if you need to:

Selection.Name = cel.Value


It does get a bit more involved, though, as you probably want the images to fit nicely in a certain column and/or row. To do this you first have to determine what the height and/or width is (and adjust it if you wish) and then determine what the dimensions of the image you've imported are and adjust them to fit. Also, you probably would want to check to see if an image already exists and delete and replace it.

The code to do these things is pretty straightforward. If you'd like to learn how to do these sort of things, I'd encourage you to record these sort of actions using the macro recorder in Excel and then look at the code and see how it works. Actually, that's the way I'd suggest you learn pretty much anything VBA in Excel. That's what I do...

I added some of this other functionality to the file, which, Excel_Geek Insiders, is on it's way.

Later,

Excel_Geek

Wednesday, September 19, 2007

I found another Excel geek at Dreamforce

So this week I've been out in San Francisco at Dreamforce '07, and I've learned some incredible things about Salesforce.com -- better ways to extend what we're already doing and some cool new features do to hit this winter. I've also met some amazing people. One such person is Ezra Kenigsburg, a consultant and Salesforce.com Data Architect out of Chicago working in the Professional Services group.

Turns out Ezra is a bit of an Excel geek, too. Actually we met at a breakfast discussion of analytics, and he was mentioning that he was "...a bit of an Excel geek..." so I jumped in and said, "Oh, really? Well, I am THE Excel_Geek." We got along just fine from then.

Once we were finished chatting about Salesforce stuff, we jumped on this blog, so that he could see what I was all about. The last post on "Filling in the blanks..." was up, and a second or two after he read it, he showed me a far better, simpler way of doing was I had done.

Ever heard of "Edit --> Go To... --> Special"? Well, it's a pretty cool, little known feature. What you can do is select a large range, like we were dealing with in "Filling in the blanks..." and go to Edit --> Go To... --> Special, and select "Blanks". This will select only those cells within the range that are blank. (There are many other cool options, too, like selecting only formulas, comments, conditional formatting, data validation, etc. Oh the possibilities! Anyway...) Once you've got only the blank cell selected, if you click up into the Formula Bar and type in a formula you want -- in our "Filling in the blanks..." case, we wanted to average the four cells immediate left -- and instead of hitting <Enter> you'll hit <Ctrl> + <Enter>, which will copy that formula, adjusted for relative ranges, into all selected blank cells. Wow. The simplicity. Wanna make 'em all red now, simply Format --> Cells... --> etc.

Thank you, Ezra. Truly a pleasure to meet you.

Wednesday, September 12, 2007

Filling in the blanks - Interesting $50 Project

Recently I completed a $50 Project for a client who had what could be a fairly common data problem -- missing data points.

This client has large tables of data over a series of dates, but for some reason he's missing data points for some of the dates. To fill them in, his plan was to simply average the values of the four prior dates. In cases where the blanks occurred within the first four dates' data points, he was just going to put zeros.

The formulas needed to do this were simple, but there was a problem: His data table was very large. It would take him a long time to copy-paste that formula over and over.

"Isn't there a way to use to Excel's replace function to find blanks and replace them with these formulas?" my client asked. "Oh, and it'd be nice if when blanks were replaced, the font color was changed, so I can see which points are averages and which ones are authentic data values."

Here's kinda how my thought process went as I tried to solve this problem:

Hmmmm.

Well, the replace function idea wasn't really a go, because even though you can use it to replace blank cells with formulas (simply leave the "find what" field blank, put the formula -- e.g. "=1+4" -- in the "replace with" field and select the "match entire cell contents" option) there's no way to specify which cells to reference in the formulas. Plus the replace function, by itself, couldn't change the font color. You could use conditional formatting and change the font color if the cell contains a formula, but wait...there's no built in Excel formula to test if a cell contains a formula...and you can't just look to see if the leftmost character in the cell is a "=", since that evaluates the value in the cell (the result of the formula) not the text of the formula. I could have just wrote a custom function in VBA -- say, called ISFORMULA -- to evaluate whether or not the cell contains a formula. That VBA might have looked something like this:

Function ISFORMULA(CELL_REF as Range)

If Left(CEL_REF.Formula, 1) = "=" Then

     ISFORMULA = True

Else

     ISFORMULA = False

End If

End Function


But none of that mattered because, again, the replace function path wasn't going to work, so I instead wrote a relatively simple macro that would allow the user to select the range of cells he or she wanted to check for blanks and replace them with either zeros (if they were in the first four date columns) or formulas to average the prior four date columns' values. Then if the blank was replaced with a zero the font would be changed to green, and if the blank was replaced with the average formula, the font would be changed to red.

In the end, the result was a solution that didn't make any material changes to what the client was already doing, but solved his problem with the click of a button.

Insiders Subscribers, your file's on the way. I also made some explanatory notes in the spreadsheet in a comment and put lots of explanatory comments in the code itself.

Enjoy.

Excel_Geek

Oh, and did anyone notice two shiny new features (one temporary, one permanent) on the blog?
  1. An image and link on the left side promoting a charity fundraiser my wife and I are involve with in the near future, and
  2. A Google search feature on the right side. Now you can easily look for those oldie but goodie posts from way way back...

Friday, August 24, 2007

I Geeked Out on Redbull Roshambull

So what does the Excel_Geek do late at night when he can't sleep, but isn't motivated enough to work on one of the countless other Excel projects he's got on his plate?

He plays rock paper scissors on Redbull Roshambull, a Facebook app created for Redbull by the guys down at Archrival.

Being an Excel_Geek, though, I couldn't just play, right? I had to better understand how strategy comes into play. Now I'm not talking about throw strategy -- that is when to throw rock vs. paper vs. scissors. There are countless strategies you could enact based upon things like an opponents past five matches, or throw by throw tendencies over their history, or overall throw tendency, or the overall throw tendency of the total Roshambull userbase (if you had access to that data, which I do not) or even strategies not based upon tendencies, but on the weather or day of the week or even random. This is not what I concerned myself with.

I have my own throw strategy I've been toying around with, and so far, it's lead me to a fairly consistent 55-59% win rate. (Thank you, thank you.) For this exercise, though, I assumed away a player's overall winning percentage, and actually looked across a range of winning percentages, from 50% to 60%. I didn't concern myself with win rates lower than 50% -- those which we'll call "losers" -- since to me the obvious best strategy for "losers" is to simply give up and not play. ;) Seriously, though, I thought this material would be much more relevant to those who believe they have some ability to beat the 50:50 odds inherent in this game or at least tie them. Maybe some other time I'll look at -- ahem -- solid "loser" strategies for Roshambull...

What I wanted to explore was betting strategy. Say your goal is to amass as many creds as possible. Given that you start with 20 creds (and if you lose all your creds you can start over with another 20) and you can bet as much or little of that as you like -- either directly to your friends or using the Match Finder -- what would be the best strategy for how much you should bet on any given match based upon how much you have and what your overall winning percentage has been? You know what I mean? Is it best to continually put it all on the line or should you pull back after you've gotten so many?

So I decided to look at two betting strategies:

  1. All-In-All-The-Time, which is just like it sounds. It assumes you can always find an opponent to take on at any given level of creds, and you bet it all on every match.
  2. Match-Finder-Only, which assumes you'll only use the Match Finder feature and bet as much as you can for any given cred level. Example: if you had 40 creds, you'd bet 20, since you don't have enough for a 50 cred match yet. This strategy has the built-in characteristic of pulling back once you've amassed so many creds, since you can only go up to 2,500 cred matches.
So I built a model whereby I could simulate up to 100 consecutive matches -- just the win vs. loss, not actual RPS throws. To do this I randomly assigned a value between 1% and 100% to the match that would correspond to either a win or a loss, based upon what the player's assume win rate. Example: a random value of 45% for a player with a 50% win rate would be a win because it was less than or equal to that win rate, where a random value of 76% would be a loss since it is higher. Based upon the cred balance before the match and the betting strategy, a bet amount would be assigned to each match, and based upon the results of the match the cred balance would be adjusted accordingly. Following a completed 100 match streak, I only wanted to know the high cred balance seen during the string. Then I realized that one 100 match string was just that -- one instance -- and I wanted to be a bit more probabilistic, so I wrote a script that ran it through 500 trials of 100 match strings, and calculated the probability of a maximum cred balance at various levels.

You following this?

Now 500 trials is a lot, but much less than infinity, so we're not talking true probabilities here -- I'm no actuarial scientist or even really that good with statistics -- but 500 trials is fun to look at and should give us all a feel, right? Plus, I simulated nearly 3 million matches to get this data, and even with a relatively speedy processor in my laptop, this took almost an hour's worth of processing time!

Here's an example of what the results looked like for the "Match-Finder-Only" strategy, assuming a 57% win rate (in histogram form):


(Click on the image to pop open a new window or tab with a larger version.)

That data breaks down in to the chart below, which shows the likelihood (in that 500 trial experiment) that a player would have ever hit the various maximum cred levels:


(Click on the image to pop open a new window or tab with a larger version.)

Over these 500 trials of 100 match strings, there was a 15.2% chance the player would have amassed more than 10,000 creds using this strategy. Interesting? (If you said or though "Yes" you're probably a geek, too.) Just wait, now we'll look at the corresponding charts for the other "All-In-All-The-Time" strategy:


(Click on the image to pop open a new window or tab with a larger version.)


(Click on the image to pop open a new window or tab with a larger version.)

Interesting now? See how everything has "slid" to the right a bit? Notice how over these 500 trials the player using this strategy has a 26.2% chance of amassing >10,000 creds -- an increase of 11%. Also, notice that now the player has nearly a 1% chance of amassing over 500,000 creds! What this effectively means is that the player has nearly a 1% chance of hitting a 16 match win streak during any given 100 consecutive match period if he or she has a win rate of 57%. Huh... Just the other day I went on a 12 or 13 match streak myself...too bad I wasn't using this betting strategy then.

If we pick one point along these graphs -- say hitting >10,000 creds -- and plot the data for the two strategies' results over multiple assumed win rates, we get something that looks like this:


(Click on the image to pop open a new window or tab with a larger version.)

At every win rate from 50% to 60% the All-In-All-The-Time strategy outperforms (during this 500 trial experiment). Similar results would be seen at other relatively higher points along the "maximum creds amassed" scale. I should also note that while we really didn't see it over this 500 trial experiment, I would expect to see relatively greater volatility for this strategy, given it's propensity for higher and higher wager amounts when long winning streaks do occur.

One other factor that I thought would be interesting (to me at least) is the impact on the number of consecutive matches played on the results. It stands to reason that the more matches you play using the strategy, the more likely it is that you'd see a winning streak that could result in higher maximum creds amassed, so not surprisingly, here's the two strategies over 10, 25, 50, 75, and 100 matches:


(Click on the image to pop open a new window or tab with a larger version.)


(Click on the image to pop open a new window or tab with a larger version.)

You can see how the likelihoods are pretty slim when only playing 10 matches. After all it takes a streak of 10 (all ten would have to be wins) to go over 10,000 creds using the All-In strategy and 13 (impossible with on 10 matches) using the Match-Finder-Only strategy. The likelihoods grow, however, as you move up to 100 matches, and I assume they'd continue to grow and eventually plateau as we moved past 100 matches to 1,000 matches and beyond.

What does this all mean? What am I concluding?

I think I can only conclude that if you want to amass huge numbers of creds in Redbull Roshambull, in order for probability to be on your side you should...
  1. Play as many matches as possible, and
  2. Go all-in all the time, baby!


What have you got to lose?

Later,

Excel_Geek

Tuesday, August 21, 2007

3D XY Scatter Plot Graph

Hey everyone.

I just recently did a quick project for a guy who wanted to visually represent data along three dimensions. All I can say is thank goodness for Andy Pope and his charting wizardry!

By using Andy's "3d XY-Scatter" as a starting point, doing some clever formatting, and adding in some vertical error bars, I think I've accomplished a pretty decent looking 3D "push pin" sort of representation. I even used a little known trick to add transparency to the "heads" of the "pushpins", so that when they're close together, you can still distinguish them. Here's what it looks like:


(Click the image to open up a new tab/window with a larger version.)

Now, it'd be nice to add in the ability for the graph to automatically "resize itself" -- i.e. change the values that create the reference "cage", axes, and actual minimum and maximum X and Y axes values -- based upon the data to be plotted and the rotation of each of the axes. Maybe I'll work on it if someone else wants that. Just too many projects going on at once these days.

Insiders Subscribers, your file is on its way!

Later,

Excel_Geek

Friday, July 20, 2007

Renaming files in a directory

I recently completely a handy little $50 Project that ended up being immediately useful to me: a simple tool to rename a bunch of files in a given directory automatically.

Why might this be useful? Well...

  • What if you use some program that outputs a bunch of text files that you use later for something else, but it gives them archaic names and you want to give them all (there could be hundreds) more intuitive names?

  • What if you've got a long list of Word documents that you want to convert to text files?

  • What if you've got a lot of files relating to a project named "Secret Project" and all of a sudden the project gets renamed "Super Secret Project"?

  • What if you use Salesforce.com and you exported a whole list of attachments, but what you're left with is a bunch of hexadecimally named files with no file extension with an accompanying Excel file associating those hex names with their real English names and file extensions? (This, you might have guessed, is the issue I found myself with nearly immediately after having completed this project for my client, who was dealing with the first issue.

What I created is a simple file where the user can input the path to the directory in which the files are stored. Then the user lists the names of the files to be renamed in one column, and the new names and/or filetype extensions for each file in another column. Using the File System Object the macro finds the files in the list and renames them accordingly. It's remarkably fast, too.

To my Insiders subscribers, the file's on it's way.

Later,

Excel_Geek

Oh, and if you didn't already notice, I've decided to offer my originally created content on my blog to anyone who'd like to use it via a Creative Commons license. Do what you like with it -- commercial, non-commercial, make derivatives, whatever -- provided you attribute the original to me. I'm doing the same within the code modules in the files I send out to my Insiders subscribers. Enjoy!

Thursday, June 07, 2007

Excel & VBA on a Mac: Look out for Missing Acrobat Distiller

So I just recently completely my first Excel + VBA project for a Mac user. Wow.

It was a Private Project, so I'm not at liberty to share the file with my Insiders subscribers or talk in too much detail about what exactly I was doing, but I did want to relay to my loyal readers the nature of one of the quirks I encountered on this project.

First and foremost is that the script would not run on the Mac. At all. It popped up some obscure message about "Compile error: Can’t find project or library". I struggled long and hard with this. It seemed that some reference was not selected on the Mac user's computer in the Visual Basic Editor --> Tools --> References... menu. Only after having lunch with a good buddy of mine so I could play on his Mac Book Pro, did I see something that my client told me about, but that didn't register immediately with me: She told me that the references checked were...

  • Visual Basic for Applications

  • OLE Automation

  • Microsoft Office 11.0 Object Library

  • MISSING: Acrobat Distiller

Now the project initially had involved the possibility of automatically creating some .PDFs from the worksheets (though, in the end we scrapped that aspect), so I misunderstood that she was telling me that "Acrobat Distiller" (which we would have needed to create the .PDFs) was missing from the list. But NOOOOO. She was telling me that it was actually in the list as "MISSING: Acrobat Distiller" and checked.

When I unchecked this reference on my friend's Mac, all was well. The script ran just fine.

When I worked with my client and had her do the same, it worked on her Mac, too.

How about that? Now, there are also some other odd differences between running Excel in PC vs. Mac, such as inconsistency in the amount of print space rows and columns take up, which we had to work out separately, but I thought most interesting was this odd "MISSING: Acrobat Distiller" issue. I'm sure others have faced the same issue, but I never found any references to it on the web when I searched, so here it is.

Have a good day,

Excel_Geek

BTW, (and this is a completely unpaid and unsolicited endorsement), for those of you PC users who who regularly do collaborate with Mac users, and vice versa, I recommend GatherPlace for sharing desktops, etc. When GoToMeeting failed, GatherPlace shined.