Excel to Insight Worksheets [Analysis with Insight]

One of the very powerful and commonly-used visualizations in Adobe Insight is the Worksheet visualization. It allows an analyst to create ad hoc metrics and filters. Like a standard spreadsheet, every cell can contain a static value or a formula - including references to other cells. In addition, though, Insight Worksheet cells can contain formulas that define metrics.

Several times a month, clients ask me if they can bring in external data - from another Excel worksheet for instance - quickly and easily without retyping.

Sometimes, it’s as simple as bringing in a dozen cells of benchmark data to write comparative metrics against.

Sometimes, it’s more like 12 rows times 12 columns of benchmark data.

Either way, it’s labor-intensive to re-type and introduces the risk of human error. Plus, it’s a pain if these values change over time and need to be imported frequently (but not frequently or significantly enough to warrant automating them in as a log source into your dataset.)

A few Fridays ago, I was talking to Doug McCormick, one of our rock star (my term of affection) Insight solution consultants, and mentioned this challenge. I mentioned it to him because he’s a whiz at Excel macros, and I figured he might have an idea or want to eventually work on t.

By Monday, he had a solution. I’m proud to introduce the “Excel to Insight” quick tool.

“Excel to Insight” will take data that you input into Excel (static values, formulas, and even metric formulas that you’d like to write in Excel and bring across to Insight) and convert it into an Insight worksheet in a .vw file that can be opened in Insight - effectively taking your Excel data to an Insight worksheet for you!

I do have to say that this isn’t part of the Insight solution - it’s just a supplementary tool, provided by Adobe Consulting, that might make life easier for many Insight analysts. As such, it’s provided without warranty and is not supported by ClientCare or available as part of any default package or installation of Insight.

You’re feel free to grab it and use it as you need to, though. And if you have any feedback or ideas for improvement upon it, feel free to let me know.

Here’s how it works:

First, unzip the provided zip file so the root install folder “Insight_Excel-2-Insight” sits on the root C:\ drive (there are file references/links that need to referenced in the same location).

Then, open the Excel spreadsheet “Excel-to-Insight - Draft v1.xlsm”. Follow the instructions on the first worksheet to ensure you’ve enabled macros in a way that this tool can do its job.

Finally, plug the data you want into the allowable cells (inside the red border area) in the second worksheet. Input the desired filename for your .vw and click the button at the top, and - presto! - you have a .vw file.

For the purposes of this demo, I filled in some random data in the tab as below. Then I put my desired output filename in cell D10, then clicked the big “Create” button in “Step 2″ at the top.

Once you have the .vw file, you can open it via the “Open” command in an Insight workspace, and/or copy into your user Work folder in Insight and open from within the Insight client. Here’s mine:

Now I can use this data as a starting point to compare to metrics from formulas within Insight - updating from my Insight dataset. I can also use these values as the benchmarks for formulas that will populate indicator (true/false or up/down arrow) cells for alerting.

The tool has instructions built in. And, yes, you can pre-write formulas in Excel as well - just use a leading single quote (’) at the start of the formula.

Feel free to grab this, use it, and enjoy the efficiency of quickly & easily bringing your Excel benchmark data into a worksheet in Insight.

Download the Excel to Insight tool from the Adobe Developer Connection. It’s available in the Developer Connection as “Excel to Adobe Insight Worksheet Tool.”

• Michael Halbrook
• 1 response
•  Channel Analytics

• Share on   Digg Reddit Facebook MySpace StumbleUpon Propeller Slashdot Newsvine ma.gnolia
• Bookmark at   del.icio.us Technorati Google Bookmarks Yahoo Bookmarks Yahoo! My Web Windows Live Furl