Excel Ramblings and integration with all the Google web services. http://ramblings.mcpher.com

Using Excel color ramps with google map overlays

2012-03-05T11:54:00.002-08:00

On the ramblings site, I have created a bunch of content covering color ramps and applying them to charts, tag clouds and other things.

Interestingly, exactly the same thing can be applied to Google Map overlays. Combining the material on adding map markers to google maps straight out of excel, here's how to create shapes using heatmap, or other color ramps as an overlay.

Firstly - create your data describing the circle size, and color to use. The size would be calculated according to some formula that scales each item to be plotted according to some demographic.

Secondly - create the marker color. We already have all the tools we need to generate html color codes as part of a color ramp. The formula for the markerColor column is simply

=rgbToHTMLHex(rampLibraryRGB("heatmap",MIN($F$2:$F$8),MAX($F$2:$F$8),F2))

Now plotting your data on google maps straight from Excel, will also generate an appropriately sized and colored circle overlays.

Converting excel.vba projects to google apps script.

2012-03-01T15:48:00.000-08:00

You are ready to move to the cloud, but what about all those legacy vba apps? Are they really going to influence your decision such that you have to stay where you are?

Moving to google docs

As an experiment, I am converting a very complex vba project to google apps script. As it turns out, it's not so hard, and with a little preparation, it's possible to create a set of libraries that abstracts the whole thing away from the platform, excel or google docs, so that even dual maintenance is feasible, and we will certainly have the tools to make future migrations a breeze.

You can follow along with the migration, or even help. See here for the migration of an excel roadmap per project to google apps script.

Color Ramps and topographic maps in Excel

2012-02-04T10:08:00.000-08:00

Color Ramps
In a previous post, I showed how to automate the creation of a heatmap in Excel. It occurred to me that this could be generalized to any color ramp. Here is an extensible color ramp library you can download and incorporate in your VBA/Excel procedures.

That allows us to plot something like this for some function (x,y)

and here is all that's needed

Public Sub colorRampVisualize()
Dim dsout As New cDataSet
createSurfaceChart _
dsout.populateData(wholeSheet("crampviz"), , "crampviz", True, , , True, "y/x"), _
"crampChart", xlSurfaceTopView, 200, "heatmaptowhite", False
End Sub

Topographic maps

Taking that a stage further, and automating the production of charts that use this library, you can start to create topographic maps, given a table of co-ordinates against elevations.

Here is an example, using randomly generated elevations for an imaginary landscape.

and here is code

Public Sub colorRampElevation()
Dim dsout As New cDataSet
'redo the random elevations
Application.Calculate
createSurfaceChart _
dsout.populateData(wholeSheet("elevate"), , "elevate", True, , , True, "lat/lon"), _
"top", , 200, "terrainnosea", False, , False

End Sub

Take a look on the ramblings web site to download these libraries and to see how this is done.

using a tagCloud to check your Outlook Mail

2012-01-30T10:01:00.000-08:00

tagCloud in Outlook

In a previous example, I showed how to create a tagCloud of various Excel data sources, with a downloadable example on the ramblings web site

It occurred to me it would be pretty useful if you could select some mails in Outlook and have a quick look at the main messages in the form of a tagCloud.

First select some mails in outlook, using whatever criteria you want.

then create a tagCloud, to see if it's worth reading them.

and well .. that's all there is to it. You can get the code to do this and read how to implement it on the ramblings site.

Create tag Clouds from twitter in Excel

2012-01-26T03:30:00.000-08:00

The tagCloud class
Today's post will demonstrate how to generate tagClouds from various sources in Excel. As an example we will use some tweets retrieved by the rest- Excel library

A 'Mitt Romney' type query generates this tagCloud

The VBA code to create this uses classes from the ramblings site: cTagCloud , cDataSet. and heatMap.

Public Sub testTag()
Dim ds As New cDataSet, dr As cDataRow, tg As New cTagCloud

tg.init , , 3, " "
With ds.populateData(wholeSheet("tweetsentimentdetails"), , , , , , True)
For Each dr In .Rows
tg.collect dr.Cell("text").toString
Next dr
End With
tg.results Sheets("tagout").Range("a1")
End Sub

A single Cell
The example above reports on a complete worksheet column. Here's how to do a single cell, and is in the spirit of many posts such as rest-excel-json-excel in one line of code

Newt Gingrich's wikipedia entry gives this

with this one liner.

Public Sub testTagSingleCell()
Dim tg As New cTagCloud
tg.init(, , 5, " ").collect( _
Sheets("singleTag").Range("a1").Value _
).results Sheets("singleTag").Range("b1")
End Sub

Multiple tags
Finally, here's how to consolidate multiple tags from multiple workbooks, this time comparing Obama and Romney sentiment on tax and charity.

and here is the code,

Public Sub testWorkbookTag()
Dim ds As cDataSet, dr As cDataRow, tg As cTagCloud, drin As cDataRow
Dim din As cDataSet

Set din = New cDataSet
din.populateData wholeSheet("tagBook"), , , , , , True

For Each drin In din.Rows
Set tg = New cTagCloud
tg.init , , 3, " "
Set ds = New cDataSet
With ds.populateData(wholeSheet(drin.Cell("sheet").toString), _
, , , , , True)
For Each dr In .Rows
tg.collect dr.Cell("text").toString
Next dr
tg.results drin.Cell("tags").where
End With
Next drin

End Sub

You can read more about this and download these and other examples at the ramblings site. Although the examples here are based on tweet data, you can of course use any Excel data.

How to track sentiment,tweets and stock price directly from Excel

2012-01-23T02:03:00.000-08:00

Combining multiple rest queries in a single Worksheet

In previous posts, I covered how to get tweetsentiments into excel and how to get Google Finance data into Excel. Today we'll look at how to build an application to pull all that together.

Random stocks

Starting with a table of random stocks, we first use the Google Finance Rest library entry to get the latest stock price.

Next we add tweetsentiments, using the ticker as the query.

Next we query topsy to see the volume of tweets about each company's stocks.

Finally we schedule this to run every now and again, and build up a history so that each company can be tracked.

For information on how to implement this in Excel, and to get a downloadable example, visit the ramblings site.

Creating heat maps with VBA/Excel

2012-01-07T03:42:00.000-08:00

Today's post is about how to create a palette to use for a heatmap, and how to apply it to either an Excel Table of values to be compared, or to create surface chart and apply the palette to the categories. A complete description of how this all works, along with the downloadable workbook and code can be found on the Excel Ramblings site.

The heatmap palette

A typical palette for a heatmap would look like this.

The tricky part is to calculate an appropriate RGB combination for the color based on the where the value lies between the min and max value of the other values in the table. The algorithm for this is in the downloadable example.

Using the palette on an Excel table
This example just applies the palette to values in an excel table. Something like this can be done using conditional formatting, but using the heatmap algorithm allows for easy automation in VBA.

Creating a surface chart to illustrate a heatmap.

Excel provides a nice chart that can be used for very effective heatmap presentation.

or this variant.

In each case. I have applied the heatmap template to the chart and automated its creation. As usual on this blog, the objective was to provide a capability to do the whole thing in one line of code, as below.


Public Sub heatMapExample()

Dim dsout As New cDataSet

createSurfaceChart _

dsout.populateData(wholeSheet("matrixout"), , "matrixout", True, , , True, "matrix"), _

"heatmap"

End Sub

For more details, and to download the full example please visit the ramblings site or contact me on our forum.

Executing SQL from Excel and Data Abstraction

2012-01-03T05:30:00.000-08:00

Abstracting an Excel data source.

In almost of all of the downloadable examples on the ramblings web site, I use a set of data abstraction classes to separate workbook data from its workbook location, and as a result, these classes now have a fair amount of capability built in such as Excel-jSon-Excel conversion, getting data from Google Docs and many other functions.

Most of you will already know about how to get data into Excel from an SQL database, whether through ODBC, MS Query, ADODB, the Excel toolbar and many other methods.

Getting data into excel using SQL can be kind of complicated, with all the confusion around connection strings and recordsets and so on, so I thought I could combine the ideas of creating a simple library class to connect to various sql data sources of the type implemented in the rest-Excel library, and the Excel regular expression library along with the data abstraction classes, so that they could be used to create really simple VBA functions.

As with previous posts such as Rest-Excel in one line of code and excel-Json in one line of code , I set myself the target of being able to execute SQL on a variety of data sources, populate Excel worksheets, and do various conversions such as to jSon, all in just one line of VBA code. You can download the resultant classes and examples and read in more detail on the ramblings web site, but here are some examples using the cADO library class.

Some examples

Copy the current worksheet to another sheet, using ADODB.

Sub testADOCopyCurrent()

Dim ado As New cADO

ado.init().execute

End Sub

Copy selected columns of filtered data from one sheet to another, using SQL/ADODB, then clean up the connections afterwards

Sub testADOCopySpecificFilterSpecifyColsAdd()

Dim ado As New cADO

ado.init(Range("testAdo!c1"), , False).execute _

("tweetsentiments", "value", "where value=1").kill

End Sub

Copy a worksheet, using SQL filtering, convert it to jSon, and serialize the jSon nicely formatted as per jSonLint.

Sub testADOCopySpecificFilterSpecifyColsjSon()

Dim ado As New cADO

Debug.Print ado.init(Range("testAdo!a1")).execute _

("tweetsentiments", , "where value=1").dSet.jObject.Serialize(True)

End Sub

Get data from an access database using sql, and populate a worksheet

Sub testADOaccess()

Dim ado As cADO

Set ado = New cADO

ado.init(Range("testadoaccess!a1"), _

"D:\googlechart\sqlTest.accdb").execute _

"dogOwners", "firstname,lastname", "where dogowner=true"

End Sub

Get data from a closed Excel workbook, populate a worksheet, and convert it to jSon, serializing the output nicely formatted for display.

Sub testADOclosed()

Dim ado As cADO

Set ado = New cADO

Debug.Print _

ado.init(Range("testadoclosed!a1"), _

"D:\googlechart\roadmapgenerator.xlsm").execute( _

"inputdata").dSet.jObject.Serialize(True)

End Sub

TweetSentiments now in the cRest Excel library

2011-12-20T07:02:00.000-08:00

Interpreting sentiment from phrases.

I came across http://tweetsentiments.com/ the other day, which is a service that tries to analyze the sentiment expressed in a phrase. The are using a technique called Support Vector machines. You can read about this implementation here http://www.csie.ntu.edu.tw/~cjlin/libsvm/

I wondered if this could be applied to phrases outside the world of twitter - say to data collected in a spreadsheet from some other medium.

In a previous entry, I described how to add new entries to the rest-Excel library. Here's another new library entry for tweetsentiments using their API. You can use this to get the data straight into Excel.

The input data is just a column of phrases that need their sentiment analyzed, and the output is positive, negative or neutral. Here are some random phrases I tried and got the results shown below.

The full code along with the other rest library examples can be downloaded in the cDataSet workbook, which is one of the projects on the ramblings website at http://ramblings.mcpher.com/Home/excelquirks/downloadlist

I'll be adding more to this library from time to time as I come across useful APIS. If you would like to see one implemented, tell me about it at the ramblings forum.

Here are the library entries added so far.

"google patents" "twitter" "google books by isbn" "yahoo geocode" "imdb by title" "itunes movie" "google finance" "whatthetrend" "tweetsentiments"

WhatTheTrend twitter top 10 added to Excel cRest library

2011-12-19T02:56:00.000-08:00

What's trending on twitter to Excel

In a previous entry, I described how to add new entries to the rest-Excel library.

Here's another new library entry. WhatTheTrend provide an API for twitter watching. You can use this to get the data straight into Excel.

Here's the code

Public Sub testWhatTheTrend()
generalReport restQuery _
("whatthetrend", "whatthetrend", , , , , , , , , True) _
, True
End Sub

Here's the populated sheet

WhatTheTrend data returned to an Excel Sheet
.

You could use this to automate collecting daily trending topics for example.The full code along with the other rest library examples can be downloaded in the cDataSet workbook, which is one of the projects on the ramblings website at http://ramblings.mcpher.com/Home/excelquirks/downloadlist

I'll be adding more to this library from time to time as I come across useful APIS. If you would like to see one implemented, tell me about it at the ramblings forum.

How to use the Excel Roadmapper

2011-12-15T09:25:00.000-08:00

Excel Roadmapper from simple table data

Some time ago, I showed how to develop a VBA roadmapper on the ramblings website. I've noticed that most of my visitors to that topic simply download the finished workbook

For those that want to do that, I've finally got round to producing some operational instructions for the roadmapper

Happy mapping.

Bruce.

Google Finance Added to crest Library

2011-12-12T05:34:00.000-08:00

Managing stock portfolio in Excel

In a previous entry, I described how to add new entries to the rest-Excel library. Up till now we've just looked at populating static data in a worksheet with data picked up from a call to site that can provide jSon response to a rest query.

You may want to just update part of a sheet that has some calculations in it, for example a stock portfolio. Here is the code to populate a sheet with data from Google Finance

Public Sub testGoogleFinance()
generalReport restQuery _
("googlefinance", "google finance", , "ticker", , , , , False) _
, True
End Sub

In this case, our sheet looks like this -

The input is the ticker column, the other columns with yellow headers are populated by the code given earlier.The columns with the blue header contain formulas based on the data returned by the rest query

In this case we needed to do 2 things differently than previous examples on this blog.

Instruct that the library does not blank out the sheet before populating (the false in the call arguments)
Leave a blank column between the data to be populated (the yellow), and that to be left alone (the blue), and also a blank row between the data and the total. This will ensure the formulas remain intact.

That's all there is to it. See rest to Excel library for downloads and more information

Comparing regEx and Search for sorting bulleted lists in Excel

2011-12-01T07:39:00.001-08:00

Sorting chapter/bullets numbers
Quite often you need to sort data that has some kind of chapter numbering, such as
1.1
1.1.2
2.1.1
2.12.2

A specialized form of this would be ipnumbers, for example
192.1.3.2
172.12.180.1

These do not lend themselves to sorting easily, so the obvious solution is to insert leading zeroes. The problem though is how to separate the individual components so as to be able to standardize their widths.

One way is to use the search function in Excel. Here is an example of turning such a form into a set of 4 digit numbers so they can be sorted (1.12.11 into 0001.0012.0011.). Let's say that the input 1.12.11 is in cell B15, the formula is very complex, and becomes exponentially more complex with more groups of numbers.

=TEXT(IFERROR(MID(B15,1,SEARCH(".",B15&",")-1),""),REPT("0",4)&".") & TEXT(IFERROR(MID(B15,SEARCH(".",B15 & ".")+1,SEARCH(".",B15 & ".",SEARCH(".",B15 & ".")+1)-SEARCH(".",B15 & ".")-1),""),REPT("0",4)&".")& TEXT(IFERROR(MID(B15,SEARCH(".",B15 & ".",SEARCH(".",B15 & ".")+1)+1,SEARCH(".",B15 & ".",SEARCH(".",B15 & ".",SEARCH(".",B15 & ".",SEARCH(".",B15 & ".")+1)+1)-SEARCH(".",B15 & ".")+1)-1),""),REPT("0",4)&".")

See here for a detailed discussion on how this works.

A better way is to use a regEx approach. Although regEx is available in VBA, it is not directly usable as a spreadsheet function. The Excel RegEx library allows you to use a prebuilt library of regexes or to use your own directly in your spreadsheet. Here is the solution to the same problem using regEx. Let's say the regEx expression ([0-9]+).([0-9]+).([0-9]+) is in A15, and the value to be processed is in B15. The solution is

=TEXT(rxreplace(A15,B15,"$1"),REPT("0",4)&".") & TEXT(rxreplace(A15,B15,"$2"),REPT("0",4)&".") & TEXT(rxreplace(A15,B15,"$3"),REPT("0",4)&".")

In addition to being simpler to start with, complexity increase is linear as more sets of numbers are added.

See http://ramblings.mcpher.com/Home/excelquirks/regular-expressions for more examples of this.

Adding to the crest- Excel Library

2011-11-24T08:49:00.001-08:00

Following the post earlier on rest-jSon-Excel in one line of code , I found that you can do the research and add a new library entry in about 10 minutes.

Here is a query to populate an Excel sheet with all the movies a particular actor has every made, using the itunes rest API.

Public Sub testItunesMovie()
generalQuery "itunesmovie", "itunes movie", _
InputBox(prompt:="Enter your itunes movie search query (eg. actor name)", _
Title:="itunes API query:results to the itunesmovie worksheet")
End Sub

Here's the library entry

Case "itunes movie"
If abandonType(sEntry, qType, erSingleQuery) Then Exit Function
sUrl = "http://itunes.apple.com/search?entity=movie&media=movie&term="
sResults = "results"
bTreeSearch = False

And here's the result, using Peter Fonda as the query, so let's have your suggestions on other cool Apis.

Rest - jSon - Excel in one line of code

2011-11-24T02:05:00.001-08:00

Rest - jSon - Excel in one line of code

My recent post on how to Excel-jSon-Excel in one line of code, seems to have generated a lot of pageviews, so clearly there are a lot of you who are interested in excel-json-conversion.

It occurred to me though, that this post was kind of specialized, meaning that the data is expected to be in a table format - essentially an image of the Excel sheet.

Normally, the reason for jSon -> Excel conversion is because you need to deal with the results from a web service, each of which will have its own data structure, which isn't generally a two dimensional structure. For that type of problem, it is better to use the rest to excel library which I covered in this post.

In the spirit of Excel-jSon-Excel in one line of code, here is Rest-jSon-Excel in one line of code, using the IMDB database to populate details in an Excel table with details about a list of movies.

Public Sub testIMDBoneline()
Dim cr As New cRest
restQuery "imdb", , , "title", _
"http://www.imdbapi.com/?tomatoes=true&t=", , False
End Sub

Which will fill in a worksheet with selected details (you select by simply adding column names that match some of the fieldnames in the rest query response) about films, given their titles in column A, as below

To try this out you can download the cDataSet workbook at the ramblings site, or read more about the cRest Excel library here.

I have now added the IMBD query to the library, and its nicer calling method is simply this

Public Sub testIMDB()
generalDataSetQuery "imdb", "imdb by title", "title"
End Sub

If anyone has any libraries they have added to Excel Rest library, or that you would like me to add, please leave a comment or contact me on our forum.

rest library for Excel

2011-11-21T06:28:00.001-08:00

Eliminating repetitive coding of Excel web service integration

I find myself writing more and more web service to Excel integrations. Here are just a few examples of webservice-Excel integrations from the excel ramblings site. I thought it was about time to generalize this in the form of a new class cRest, and a library of known integrations.

As usual, jSon - Excel conversion and Excel data abstraction are used to make the whole thing straightforward. Here's an example of populating a worksheet named "tweets" from a supplied query using this new library and class.

Public Sub testTwitter()

generalTestQuery "tweets", "twitter", _
InputBox(prompt:="Enter your twitter search query", _
Title:="twitter API query:results to the tweets worksheet")

End Sub

And another using a datatable to generate a set of queries to Yahoo GeoCoding and fill in the table from the responses

Public Sub testYahooGeocode()
generalTestDataSetQuery "geocoding", "yahoo geocode", "input address"
End Sub

Take a look at the article on the Excel ramblings site and download the example workbook, contact me for more details on our forum, follow me on twitter, or follow this blog to get more detail on this topic as it is published.

Repository for VizMap generated applications

2011-11-15T02:54:00.001-08:00

What are generated VizMap applications
These are mapping applications integrating Google Maps, Google Earth, Google Visualizations and Twitter.

Here is an example screenshot.

They are data driven in the sense that the applications are generated from a dataset and a parameter sheet and need no coding. Normally they would be generated straight from Excel, but any application or manual process capable of producing the required jSon formatted framework description can also produce them.

You can read about them here.

Repository for examples
There has been a lot of interest in these vizmap applications to the extent that my hosting bandwidth quota has been exceeded. I have moved all the examples referenced on the ramblings site, in this blog and our forum to dropbox. If a link does not work, try http://dl.dropbox.com/u/14196218/Share/ followed by the filename, or just download the Excel application and generate the examples from there.

Twitter, Excel, Google Maps,Visualizations and Earth - all together

2011-11-11T13:03:00.001-08:00

Twitter, Google Visualizations, Google Maps, Google Earth, Excel - all together.

In various posts such as Flying around with Google Earth and The Royal Family and their homes as well as on the ramblings site I showed how to create data driven applications right out of Excel that leveraged visualizations, Google Earth and Google Maps.

The latest version of this, which can be downloaded from the ramblings site , now has Organization charts, and relevant tweets as options for displaying in infowindows or infobubbles. As before, if you prefer, these can be added without writing any javaScript, and generated straight out of Excel.

Here is an example application that I generated today - all about bankers.

Excel-jSon-Excel in one line of code

2011-10-30T07:18:00.000-07:00

Excel-jSon-Excel in one line of code

On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a webservice and populate and entire worksheet in one line of code.

These examples, and the classes they use can be downloaded from the ramblings site, and are in included in cDataSet.xlsm.

Here we go

Get a jSon response from a web service and populate a worksheet with the result.

Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject

dSet.populateJSON jo.deSerialize( _

cb.httpGET( _

"http://dl.dropbox.com/u/14196218/files/jSon1.html" _

) _

).Child("cDataSet"), Range("json1!$a$1")

create and print a jSon string from a worksheet.

   Dim dSet As New cDataSet

   Debug.Print dSet.populateData( _
       Range("jSon2!$a$1"), , , , , , True _
       ).jObject.Serialize(True)

The jSon string both created and consumed looks like this - or see it online

{

"cDataSet":[ {

"Customer":"Acme",

"Contact":"john",

"Total":"100",

"Country":"US"

},

{

"Customer":"Smiths",

"Contact":"fred",

"Total":"460",

"Country":"UK"

},

{

"Customer":"Jones",

"Contact":"joe",

"Total":"24",

"Country":"US"

},

{

"Customer":"Renault",

"Contact":"Marie",

"Total":"536",

"Country":"FRANCE"

},

{

"Customer":"Schneider",

"Contact":"Hans",

"Total":"1334",

"Country":"Germany"

}

]

}

The Excel sheet that gets created looks like this

Hopefully you will see how that works from some of the other jSon examples on the ramblings site, or you can ask me on the forum

Ramblings web site has a new look

2011-10-27T03:41:00.000-07:00

Ramblings web site has a new look

Just spent a little bit of time on Google Sites cleaning up the ramblings web site. I think navigation should be a little cleaner.

Flying around Google Earth Markers

2011-10-24T11:26:00.000-07:00

Flying around Google Earth Markers

In last weeks blog entry, I showed how to generate a Google Earth or Google Maps based application out of Excel. These applications now have the capability to fly around each marked entry in Google Earth.

The info balloons now have a 'Fly Around' button which will zoom closer and rotate around the selected object. Rotation is stopped by just clicking the marker again. Control of animation is in the excel parameter sheet

Here's a cut from a fly around animation in progress - try it out , read about it and download the complete workbook at the ramblings site, see how to create the framework for this data driven application if you do not use Excel or contact me on our forum.

Another VizMap generated appplication - the British Royal Family and their homes

2011-10-22T10:53:00.000-07:00

Another VizMap generated appplication - the British Royal Family and their homes

In the Excel ramblings site, I cover how to generate Google Earth and Google Maps Application straight out of Excel, without needing to write any javascript.

These are data driven, so all you have to do is to set up an excel table for data and parameters that describe the application. Here is one I generated today showing the British Royal family, their homes, and their relationships. I'll add a bit more data to this over time, but you should get the idea.

It's very easy to do this with your own data. Just download the googlemapping.xlsm workbook and go.

Try the Google Earth Version

Try the Google Maps Version

Changing the markers on Google Earth to something in your data

2011-10-20T10:34:00.000-07:00

Changing the markers on Google Earth to something in your data

Yesterday I introduced the capability to generate google maps and google earth applications straight from Excel.

Linkin Park Marker on Red Square from Google Earth

While creating an Earth version of the concerts/venues example I came across this rather nice rendering of Red Square. Since we can change the marker in google earth straight from our data, it plotted Linkin Park right there in front of the cathedral.

A single click on the marker brings up an information balloon and a double will now fly to the next location.

Take a look at the write up or download the workbook on the Excel Ramblings Site and give us feedback on our forum.

Google Visualizations on Google Map infoWindows and Google Earth Balloons

2011-10-19T09:22:00.000-07:00

Google Visualizations on Google Map infoWindows and Google Earth Balloons

A few weeks ago I wrote about generating data driven mapping applications straight out of Excel, with no javaScript required (by you anyway). It has generated quite a bit of interest on the ramblings web site.

I wondered if I could take the idea a little further and do the same thing using google Earth as the provider. I've just released that today. You can download the workbook - googleMapping.xlsm, but here are a couple of links to the generated applications.

The only difference when generating the application from Excel is to choose the provider.

The Earth version gives the opportunity to fly between locations, and looks beautiful.

Google Earth Version.

Here's the map version

Google Maps Version.

Take a look at the detail behind this on the ramblings site. or ask questions on our forum.

Bruce

Creating Data Driven Mapping applications

2011-10-07T04:24:00.000-07:00

Creating Data Driven Mapping applications
In a previous post I looked at the the various mapping packages and how they compared. Having concluded that Google Maps was the winner, I started to work on an Excel application that could generate the necessary javaScript to Map Excel data and create tabbed Info Windows with embedded Google Visualization. This is now completed and can be downloaded and read about on the Excel Ramblings Site

Open up Google Maps and Vizualizations to Excel users
It occurred to me that Google Maps and Google Visualizations were pretty much out of reach to those who did not know javaScript, or have much web experience, even though they might quite adept at VBA and have some interesting data that could benefit from these tools.

VizMap application
The purpose of this application then is to take Excel Data and some parameters and create an html file to map and visualize that data. In the example you'll find 3 demo applications that have been created this way. You can also run them directly below

Non-Excel users

Since the javaScript component of this is taking a jSon formatted framework definition, as well as jSon data, clearly this input can be created manually or indeed from some web service, so this application may be of some interest to non- Excel users also. All that is needed is to write 2 functions that will return the framework (what the application needs to look like and how to present data), and the data that needs to be displayed.

More on this on future blog entries. I look forward to your comments on this blog or at our forum.

Comparing Bing, Ovi, Yahoo and Google Maps

2011-09-09T08:01:00.000-07:00

Geocoding Excel

While looking at how to connect Excel to Google Maps, I implemented a capability in vba for each of the main Apis for geoCoding capability through a simple http: request. You can read more about this comparison on the Excel Ramblings Site. I've also now implemented the same thing to plot Excel Data directly to Google Maps, Ovi Maps, Bing Maps and Yahoo! Maps (although Yahoo maps is about to close down I understand).

Comparing Mapping capabilities
I have already covered geoMapping, where the winners were Google (despite its complex response format) and Yahoo PlaceMaker. Scores for geoCoding Google 7/10, Yahoo 7/10, Ovi 0/10 (I couldn't find its REST interface URL anywhere) and Bing a pathetic 2/10. Bing also sends back a bunch of junk in its REST response in addition to the data you've asked for.

Ease of implementation
The easiest to implement was Google Maps. The code is clean and basic things you might want to do like pop up an info window onClick and show a pushpin label when mouseOver (and get rid of it again on mouseLeave) were all built in. In fact there was basically no code to write - just get the data in the right format and go.
Google - 9/10

Yahoo Maps! was almost as simple, very much mirroring the Google approach.
Yahoo! - 8/10

Ovi Maps was more of a chore with more coding needed for infoWindow and pushPin event handling, but it does have a very rich API and the capability to do many cool things. The documentation for the API (yes I needed to look it up for Ovi Maps) seems to have been written by an alien for aliens and is somehow boring and esoteric at the same time. They do provide a playground which is a strange hybrid of menu driven and code based as if they haven't really decided who their audience is. Nevertheless, it was pretty easy to figure out once you got past the weirdness.
Ovi - 6/10

Bing is of course different, like all things Microsoft. It expects you to be very hands on in a number of ways such as dealing with infoBox and pushPin events, and if you want to use html to layout the interior of an infoBox (as opposed to using the preset title, description etc), then things like a pointer on the box and other various other properties dont seem to work. Their playground is pretty good though, and their documentation is patchy - for example they have decided to call their mouseLeave event mouseOut and but strangely it is referred to as mouseLeave in the documentation - so you can imagine how much time wasting that caused.
Bing - 5/10

Richness of capability
For the purposes of this article I only tested a specific number of capabilities, and as a constraint i wanted to keep the input items to be plotted in exactly the same format - so this is a pretty unscientific score based only on what I noticed while implementing this test.
Google - 9/10, Yahoo - 6/10 , Ovi - 9/10 , Bing 7/10

Media quality
This is subjective of course, but just considering the standard views then both Ovi and Bing have beautiful rendering, followed by Yahoo and Google which is looking a bit jaded now. Bing is let down by the quality of its pushpins and info boxes, but the map quality is excellent.
Google - 5/10, Yahoo - 5/10 , Ovi - 9/10 , Bing 8/10

Restrictions
All of these have terms and conditions of course, and some of them need a key. Ovi said it needed a key but worked without one anyway, Yahoo needs a key that was easy to get, and Bing needs a Key that was 64 characters long and has an 11 page PowerPoint and a video to tell you how simple it is to get one. Got it in the end I guess. Google doesn't need a key, but cuts you off after you exceed a rather small daily number of geoCode requests.
Google - 5/10, Yahoo - 6/10 , Ovi - 9/10 , Bing 5/10

Overall Scores

Google Maps - 35/50
Ovi Maps - 33/50 (May have won if it had a REST geocode capability)
Yahoo! Placemaker + Yahoo! Maps - 32/50 (but remember it's closing down)
Bing Maps - 27/10

So Google wins through simplicity, Microsoft Fails by trying too hard and by giving something a stupid name, Yahoo gives up and Ovi (or is Nokia again) has a great solution but loses it's way through branding and communication. Sound familiar?

You can download the final Excel sheet that tests all this and judge for yourself and read the full story at Excel Ramblings. Please leave any questions, comments, feedback on this blog or through our forum.

Bruce

Mapping Data straight out of Excel

2011-09-06T02:04:00.000-07:00

Excel and Google Maps.
Following my previous posts comparing the geoCoding capabilities of Yahoo, Bing and Google Maps I have now implemented Adding markers to google Maps straight from an Excel Table. As usual this is fully functional Excel application you can download and use.

Please take a look and give us some feedback on the excel ramblings forum

Next I will take a look at how easy it is to implement the same thing on google Maps, Bing and Yahoo.

OVI maps
I did want to include OVI maps in this comparison, but I could not find the REST Url for geoCoding. Anybody know it? Either it is very well hidden or it doesn't exist.

Egghead Cafe Article
I quite often contribute articles to http://www.eggheadcafe.com/. For those of you who were interested in the article on comparison of geoCoding providers on Excel Ramblings Site, you may want to take a look at this one on the same theme on Egghead Cafe.

Bruce

Geocoding from Excel: Yahoo -vs- Bing -vs- Google

2011-09-03T04:24:00.001-07:00

I have already looked at geocoding with Bing Maps -vs- Google Maps and Google Maps is a clear winner. Yahoo Placemaker is pretty good at first glance even though it claims not to be a geocoder. Note that I am just using the basic REST URI query in all these examples - since the whole point to access directly from Excel. Comparing capabilities under Javascript would be a whole different ballgame.

Yahoo require you to get a Key to use Placemaker which they call and Application ID. In this downloadable example, I am using my key. If you use this code for anything, please get your own and follow the Yahoo rules on usage. Google do not require you to get a key, but limit your usage to a small number of queries daily.
Yahoo was as good as or better than Google in dealing with ambiguity.
The Yahoo response is clean and does not contain a bunch of unnecessary waffle.
The Yahoo response is even simpler than the Bing one, and easier to deal with and decode. It is so similar to Bing that I was able to use the same modules to deal with both, so implementing Placeholder was about a 30 minute job, mainly to find out what the field names were.
Yahoo provides less detail than both Google and Bing, but most of what you would need is there. It does not provide a 'cleaned up, full address' field though, which the other 2 do. On the other hand, this can easily be constructed from other fields.
Its confusing that Yahoo Placeholder is not actually Yahoo Maps. Yahoo Maps is actually OVI maps, which I haven't tried .. yet.

Go here for the full article and downloadable examples of geoCoding straight from Excel.

Bruce

Google Maps versus Bing Maps

2011-09-02T08:16:00.000-07:00

In a previous post I covered how you could use the Google Maps Api to complete and parse addresses for subsequent mapping directly from Excel.

In the name of fairness, I implemented the same thing using Bing Maps.

These restful apis work in pretty much the same way, Here are the main differences.

Microsoft require you to get a Key to use Bing. In this downloadable example, I am using my key. If you use this code for anything, please get your own and follow the Microsoft rules on usage. Google do not require you to get a key, but limit your usage to a small number of queries daily.
Bing is less able to deal with amibuity. In the set of 10 random addresses, Bing was unable to identify half of them. Google got them all.
There is a whole bunch of unnecessary stuff about copyright, and other junk that is returned from each query to Bing. What a waste of space and traffic.
Bing provides less detail than Google, but the results are a lot easier to deal with.
Bing is fussy about input - for example I needed to clean the input addresses to get rid of non printable characters and so on. Google doesn't care about that.
Bing Maps has a stupid name.

There you have it. You can download the example which has both versions implemented here and judge for yourself.

All feedback and questions are welcome at our forum.

Bruce

Retrieving data from Google Books

2011-08-31T03:55:00.000-07:00

There is a simple API to retrieve google books data. There was an interesting question on our forum yesterday asking about how to quickly populate a spreadsheet with book data based on the ISBN number for a school library project.

Using this API and the cDataSet classes it was very straightforward to put together a quick solution to this.

See the jSon data returned by this example to see the kind of information returned by the API. The idea is that given an ISBN number, simply naming additional columns the same as any fields known by the API will cause theses columns to be filled out.

You can download the example (isbnExample tab) in the cDataSets.xlsm package.

The code is below

Option Explicit
Const cISBNGoogleBooks = "https://www.googleapis.com/books/v1/volumes?q=isbn:"
Const cISBNColumnHeading = "isbn"

Public Sub isbnExample()
Dim dSet As cDataSet
' get ISBN book data
' load to a dataset
Set dSet = New cDataSet
With dSet
' create a dataset from the isbn worksheet
.populateData wholeSheet("isbn"), , "isbn", , , , True

If .Where Is Nothing Then
MsgBox ("No data to process")
Else
'check we have the isbn column present
If .HeadingRow.Validate(True, cISBNColumnHeading) Then
' if there were any updates then commit them
If processISBN(dSet) > 0 Then
.bigCommit
End If

End If
End If
End With
Set dSet = Nothing

End Sub

Private Function processISBN(ds As cDataSet) As Long

Dim dSet As cDataSet, cb As cBrowser, dr As cDataRow
Dim jo As cJobject, job As cJobject, n As Long
Dim sWire As String
' gets a book details by isbn number
Set cb = New cBrowser
Set jo = New cJobject
n = 0
For Each dr In ds.Rows
With dr.Cell(cISBNColumnHeading)
sWire = cb.httpGET(cISBNGoogleBooks & .toString)
Set job = jo.deSerialize(sWire)
If Not job.isValid Then
MsgBox ("Badly formed jSon returned for ISBN" & .toString & "-" _
& sWire)

ElseIf Not job.ChildExists("error") Is Nothing Then
MsgBox ("Google books refuses to co-operate for ISBN " _
& .toString _
& "-" & job.Child("error").Serialize)

ElseIf job.ChildExists("Items") Is Nothing _
Or job.ChildExists("totalItems") Is Nothing Then
MsgBox ("Could find no data for ISBN " _
& .toString _
& "-" & job.Serialize)

ElseIf job.Child("totalItems").Value <> 1 Then
MsgBox ("Multiple entries for " _
& .toString _
& "-" & job.Child("totalItems").Serialize)

Else
' fill in this row
n = n + rowProcess(dr, job.Child("Items"))
End If
End With
Next dr
processISBN = n
End Function

Private Function rowProcess(dr As cDataRow, job As cJobject) As Long
Dim hc As cCell, n As Long, jo As cJobject, jom As cJobject
n = 0
For Each hc In dr.Parent.Headings
' any headings that are present in the dataset
' and also in the returned json get populated
With hc
If .toString <> cISBNColumnHeading Then
Set jo = job.find(.toString)
If Not jo Is Nothing Then
With dr.Cell(.Column)
' if multiple then include the array separated by commas
If jo.isArrayRoot Then
.Value = vbNullString
If jo.hasChildren Then
For Each jom In jo.Children
If .toString <> vbNullString Then
.Value = .Value & ","
End If
.Value = .Value & jom.toString
Next jom
End If
Else
.Value = jo.Value
End If
End With
n = n + 1
End If
End If
End With
Next hc
rowProcess = n
End Function

How to use cDataSet

2011-08-23T02:31:00.001-07:00

Excel Data Manipulation Classes

I notice from Google Analytics the the cDataSet pages are by far the most popular on the site. These classes are used throughout all the projects on the site, and are also the most popular download.

In order to provide a quickstart, I've created a summary of some of the capabilities of these objects, and how to use them.

Please use our forum to ask questions, provide feedback or generally contribute to the discussion.

Bruce.

Getting data from Google Maps to Excel

2011-08-15T20:47:00.000-07:00

Mapping visualization layers from Excel

The Google Maps API is a wonderful thing, allowing you to convert an unstructured and incomplete address and get back a whole bunch of structured and mapping data.

Google is clear that it is only to be used for addresses to be placed on a map (not to be used to 'clean' a list of addresses for example), and in fact there is quota on your use to make sure that you do not use it for this purpose.

However this new project is about mapping summary and transaction data on google maps, so I need to use the Maps API to summarize at country, state and other levels.

Downloadable example

The first phase in this can be seen here, with a downloadable example showing how to interact and decode the response from the Google Maps API.

Getting Started Series

2011-08-02T03:04:00.001-07:00

Looking at Google Analytics Data for my site, I notice that there are 2 distinct populations. Those that look and read through a project article (5-10 minutes) and those that stay a few seconds on each page they visit. There doesn't seem to be much of a 'middle ground' - meaning those that just spend a minute or two. This is telling me that a large number of visitors are looking for something quickly but are not finding it.

The original purpose of the site was to 'record' some of the projects I have been working on as they were going along, and to document the problems and solutions as they came along. However, Im not catering for the majority of the increasing number of visitors, since I'm guessing they are just looking for a quick answer to a small problem.

Snippets

I've decided to create a section of the site that is devoted to short snippets - quick answers to common problems - to cater for these casual visitors, and hope they will eventually stay a little longer to take a look at some of the more substantial topics covered.

The snippets will be initially a 'Getting Started Series' which I've kicked off today, but later I'll move to 'Difficult Snippets', for those harder to find solutions.

I generate content for these new areas I'll be keeping an eye on the effect on the Analytics and report back here. If you are looking for any particular topic to get priority then feel free to post your request or comments on the forum.

Dynamic forms and jSon

2011-08-01T06:03:00.000-07:00

Following previous developments on hiding data in excel objects, Excel jSon conversion and dynamic forms I figured it was time to combine all these threads, and have implemented all that as an integrated solution as part of the roadmap project .

Embedding traceability

Having generated a roadmap consisting of Excel shapes from simple data as above, I wondered how to make the shapes 'clickable' - meaning that you could click on a shape and bring up a form that could be edited, make changes to the form and write the changed data back to the original data cell.

As usual there was plenty of Excel and VBA oddness to overcome, but you can read about and download the finished product from here and provide feedback or ask questions here.

Bruce

Creating a Date Scale in Excel

2011-07-22T13:34:00.001-07:00

Sometimes you have a range of dates and you need to come up with a scale that makes sense for the dates you are trying to represent in a timeline. For example if your dates range across a year or so, you may want to break the scale into months, 2 or 3 years, quarters would be better and perhaps weeks would be best if the scale is a few months.

I came across this problem when working on a roadmap generator for Excel, and later with a port to javascript to embed it in a Google Gadget.

Once you have decided on a scale, you also need to figure out how to line up the scale at the end of the month, quarter or whatever you have chosen. Below is some useful VBA code to do all that, or you can refer to the javascript version here.

Calculating the end of the 'period'

Here is a function that will return various pieces of information a range such as the effective start date, effective finish date, number of ticks and so on given a start date and a finish date. You may find the calculations for end of quarter etc helpful, since they took me a little while to get right.

Private Function limitofScale(scaleType As String, sd As Date, fd As Date, edge As edgeTick) As Variant
Dim dLastDayOfFinishScale As Date, dFirstDayOfStartScale As Variant
Dim ss As String, sf As String, ticks As Single

Select Case Trim(LCase(scaleType))
Case "weeks"
dFirstDayOfStartScale = sd
dLastDayOfFinishScale = fd + 7 - (Weekday(fd) Mod 7)
ss = Format(sd, "dd-mmm-yy")
sf = Format(fd, "dd-mmm-yy")
ticks = (dLastDayOfFinishScale - dFirstDayOfStartScale + 1) / 7

Case "months"
' 1st day of start month
dFirstDayOfStartScale = DateSerial(Year(sd), Month(sd), 1)
' last of finish month
dLastDayOfFinishScale = DateSerial(Year(fd), Month(fd) + 1, 1) - 1
ss = Format(sd, "mmm-yyyy")
sf = Format(fd, "mmm-yyyy")
ticks = (dLastDayOfFinishScale - dFirstDayOfStartScale + 1) / 30

Case "quarters"
dFirstDayOfStartScale = DateSerial(Year(sd), Month(sd) - ((Month(sd) - 1) Mod 3), 1)
dLastDayOfFinishScale = DateSerial(Year(fd), Month(fd) + 3 - ((Month(fd) - 1) Mod 3), 1) - 1
ss = "Q" & CStr(1 + Int((Month(sd) - 1) / 3)) & Format(sd, "yyyy")
sf = "Q" & CStr(1 + Int((Month(fd) - 1) / 3)) & Format(fd, "yyyy")
ticks = (dLastDayOfFinishScale - dFirstDayOfStartScale + 1) / 90

Case "halfyears"
dFirstDayOfStartScale = DateSerial(Year(sd), Month(sd) - ((Month(sd) - 1) Mod 6), 1)
dLastDayOfFinishScale = DateSerial(Year(fd), Month(fd) + 6 - ((Month(fd) - 1) Mod 6), 1) - 1
ss = "H" & CStr(1 + Int((Month(sd) - 1) / 6)) & Format(sd, "yyyy")
sf = "H" & CStr(1 + Int((Month(fd) - 1) / 6)) & Format(fd, "yyyy")
ticks = (dLastDayOfFinishScale - dFirstDayOfStartScale + 1) / 183

Case "years"
dFirstDayOfStartScale = DateSerial(Year(sd), 1, 1)
dLastDayOfFinishScale = DateSerial(Year(fd) + 1, 1, 1) - 1
ss = Format(sd, "yyyy")
sf = Format(fd, "yyyy")
ticks = (dLastDayOfFinishScale - dFirstDayOfStartScale + 1) / 365

Case Else
MsgBox "Invalid scale choice " & scaleType
Exit Function

End Select
Select Case edge
Case etStart
limitofScale = dFirstDayOfStartScale
Case etFinish
limitofScale = dLastDayOfFinishScale
Case etFinishString
limitofScale = sf
Case etStartString
limitofScale = ss
Case etEstimatedTicks
limitofScale = ticks

Case Else
Debug.Assert False
End Select

End Function

Finding the most appropriate scale

In this case we are looking for the 'best' scale to use given a start and finish date and a maximum number of axis ticks.

Private Function AutoScale() As String
Dim ticks As Single, tickDiff As Single
Dim idealticks As Single, sBest As String, s As String
Debug.Assert pscType = sctframe

idealticks = maxticks * 0.5
tickDiff = maxticks + 1

s = "weeks"
ticks = limitofScale(s, Activate, deActivate, etEstimatedTicks)
If Abs(idealticks - ticks) < tickDiff Then
sBest = s
tickDiff = Abs(idealticks - ticks)
End If

s = "months"
ticks = limitofScale(s, Activate, deActivate, etEstimatedTicks)
If Abs(idealticks - ticks) < tickDiff Then
sBest = s
tickDiff = Abs(idealticks - ticks)
End If

s = "quarters"
ticks = limitofScale(s, Activate, deActivate, etEstimatedTicks)
If Abs(idealticks - ticks) < tickDiff Then
sBest = s
tickDiff = Abs(idealticks - ticks)
End If

s = "halfyears"
ticks = limitofScale(s, Activate, deActivate, etEstimatedTicks)
If Abs(idealticks - ticks) < tickDiff Then
sBest = s
tickDiff = Abs(idealticks - ticks)
End If

s = "years"
ticks = limitofScale(s, Activate, deActivate, etEstimatedTicks)
If Abs(idealticks - ticks) < tickDiff Then
sBest = s
tickDiff = Abs(idealticks - ticks)
End If

If tickDiff > maxticks Then
MsgBox "Couldnt find a feasible automatic scale to use for roadmap " & ID
End If

AutoScale = sBest
End Function

Getting data from Google Docs to Excel

2011-07-13T06:53:00.000-07:00

In various posts I have covered ways to get data in and out of Excel and other PC based systems. The latest capability I have looked at is being able to get data live into Excel from Google Docs.

Google uses the 'Wire Protocol' to exchange data between gadgets, Google Docs etc. Read here for an implementation of Wire Protocol for Excel to get data from Google Docs into Excel.

As usual you can download all you need to implement this capability in your own workbooks.

Bruce

Creating JSON using data abstraction classes

2011-06-14T14:07:00.000-07:00

Yesterday I posted an update to the data abstraction classes for Excel.

I realized that there is also a method to convert that data into a JSON string. The following code would convert a worksheet into JSON, and can be downloaded here.

Option Explicit
Public Sub mainExample()
    Dim dSet As cDataSet

    Set dSet = New cDataSet
    With dSet
        .populateData Range("data!$a$1"), , , , , , True

        If .Where Is Nothing Then
            MsgBox ("No data to process")
        Else
            MsgBox .jSonObject
        End If
    End With
End Sub

Abstracting Excel Data

2011-06-13T18:21:00.000-07:00

Classes for Excel Data Abstraction
In a number of articles and topics on my website i have referred to a collection of classes that I generally use to abstract Excel physical location from the data contents. I finally got round to writing these up, and posted the details on Egghead Cafe.

As usual the associated downloads are available here.

A formula to show diminishing returns

2011-06-04T03:01:00.000-07:00

Diminishing returns for more effort
You will be very familiar with the law of diminishing returns, where you get a lot of payback for your initial efforts (sometimes called 'low hanging fruit'), then you have to work harder for smaller rewards. An example for IT professionals might be a server or application consolidation project. Hacking and Stacking produces good early $ savings, but as you go through the project, you need to work hard to get less reward.- then the '80/20' rule comes in to play.

Expressing as a formula
In this kind of scenario its hard to come up with a 'finger in the air' estimate of what the savings could be when embarking on such a project, since the savings are not going to be linear. I was looking for a simple formula that could be generally applied to such projects and came up with this.

=( SIGN(A2) * $E$1 * (SQRT(2 * (SIGN(A2) * A2 / $E$1) + 1) - 1) )

where A2 is the value to be tweaked, and E1 is the 'aggressivity' of the diminuation of the result. For a VB version of this and some examples and downloads, please take a look at this article.

Populating Excel with data matched from Outlook Global Address List

2011-05-20T04:26:00.000-07:00

I came across a problem today, where I wanted to create a Google Intensity Map based on a selection of people in my Outlook Global Address List. I thought it would be kind of easy, since I already have a tool to take Excel data and make the actual visualization. But I quickly got bogged down at 'get data from the GAL' part. I guess I could have gone to Access and used the outlook connector to do a query and so on, but I wanted something right there, in Excel so I could create an integrated solution.

Looking around at the Outlook object model, it became pretty clear that's is messed up, with fields coming from all kinds of places, and property schemas in all kinds of formats, with no clear and definitive reference documentation.

I decided to put a layer of abstraction on all that, so that future outlook type things could go a bit smoother. Here is an example with some custom classes that look up and populate Excel tables from matched data in an Outlook Address List.

Now i need to move on to integrating this into a google visualization.

Bruce

So how about using an IMG tag to track Analytics

2011-04-18T08:59:00.000-07:00

Analytics with no Javascript anyone?

My last post covered how to inject Google Analytics into pages where it was not really possible to introduce javascript. by using a gadget. But what about the case where you cant insert a gadget, or you want some more granularity? It occurred to me that a creatively constructed IMG tag with some parameters (most web site creators allow you to link to images at least) defining what to track could probably go off and do the necessary tracking without the calling web page knowing it was doing anything other than serving an image.

Watch this space for a solution.

Bruce

A generic Google Analytics gadget to track any page

2011-04-18T06:54:00.000-07:00

A generic analytics gadget.

Following on from my last post on adding Google Analytics Codes to track usage of your gadget, it occurred to me that you could create a gadget whose sole purpose in life was to track usage of a particular page. This could be useful in cases where you dont have access to the source code, or when you want to easily track usage of a Google Document for example.

The solution turned out to be very simple, with a small adaptation from the information the previous post. I have added it to my iGoogle page, so i can now track my own usage and visits- I travel a lot so I can even track my own travels like this through Google Analytics.

Step by step instructions are here along with the code and you can include the gadget from here

http://pepadagvroadmapper.googlecode.com/svn/trunk/analytics/genericAnalytics.xml

Bruce

Adding google analytics to your custom gadget

2011-04-14T07:52:00.000-07:00

What you need

It took me a while to find out how to do this, but it's really simple.Implementing this means that you can track usage of your gadget, whether embedded in a web page, or in a Google doc.

A Google analytics account
the XML source of the gadet, for example.

What you need to do

Add a new domain to your google analytics account. Just make one up - in my case I used roadmapper.com. It doesn't need to exist, and you dont need to own it. It just needs to look like a real domain.
Remember the UA-xxxxx-n code that Analytics generates for that domain.
Think of an imaginary page address - this is what views will get recorded against in your analytics report. Again - it doesn't need to exist.
Inject this into your in your XML code

var ga = new _IG_GA('UA-10017268-2');

ga.reportPageview('/view/pepadaroadmap')

And this in your XML

Require feature="setprefs" /
Require feature="com.google.gadgets.analytics" /

You are good to go. Shortly afterwards analytics will start reporting on accesses to you gadget, under the domain and page you settled on.

See roadmapper gadget for details

Bruce

Creating Excel Ramblings group on Google Groups

2011-04-14T07:17:00.000-07:00

Google Groups

Since I get a lot of correspondence from people who have downloaded stuff, I thought it might be useful to create a group with this tagline.

'This group is for those with Excel or VBA tips, advice, general conversation or questions. In particular we are very keen to hear about crossing over from Excel to Google Docs, Google visualizations and otherwise internet enabling your excel data.'

Lets see how that goes. Creating the group was very straightforward, but it's kind of disappointing that it doesn't seem possible to introduce google analytics to track usage. If anyone knows how to, please let me know.

Bruce

Roadmapper now hosted on google Code

2011-04-08T06:41:00.000-07:00

Using google code to host projects

I moved the gadget and associated code to googlecode.com hoping to host it there. If you have never done this, it is somewhat complex. It can be found here. Some of the issues I came across were

Projects hosted there are expected to be open source. The most difficult task was selecting which open source license to use. In the end I decided to use Mozilla.
I hadn't been using subversion. Google code expects either subversion or mercurial for source code management. After a false start with Mercurial (which I cant now remove from Eclipse, I settled on subversion. That works ok - but was not easy to set up, mainly due to my greenness.
I'm using tortoisesvn to manage the source code loading to the google repository. That was a nice find to avoid using the command line subversion approach.
Once all was setup, I hit a problem - google code serves files as plain text MIME. That meant that my style sheets were not being treated as text/css and therefore were not being executed. Luckily I found this write up on how to change that behavior using turtle SVN.

Bruce

Implemented Roadmapper as a Google Gadget

2011-03-21T13:48:00.000-07:00

Roadmapper update

I finally got round to implementing the roadmapper as a google visualization gadget. All the source code and gadget package is available here along with the write up, so I wont repeat it here.

Bruce

Implementing a roadmapper as Google Visualization gadget

2011-03-16T08:29:00.000-07:00

Google Gadgets
This is the standard way to do charts in Google Docs, as well as being easily embedabble in web pages. In a previous post I started thinking about the migration path to Google Docs from Excel, and wondered if the scripting capability in Google Docs would give me what I need. Looking a bit more into that, I realized that gadgets give the possibility of becoming platform independent (if you consider javascript & html & using the google jsapi to be platform independent), especially since I have a tool to format Excel data as if it came from Google Docs. This means that the Google Docs scripting can be largely avoided, and we can stick to regular javascript and web presentation.

Implementing Excel Roadmapper as gadget
To see how this would work out, I took a roadmapper tool written in VB and started to port it to Javascript, with a view to making a gadget out of it. I decided to rely as much as possible on CSS for formatting and layout, as opposed to porting the parameter driven approach from Excel. It has gone fairly smoothly so far (although I haven't ported the complete functionality), with most time being spent on messing around with style sheets. The results can be tried out here, and the source downloaded from here.

The next step is to wrap that up in a gadget.Let's see how that works out.

Migrating to Google Docs

2011-03-05T03:43:00.000-08:00

Ive been using Google Docs as well as Excel for a while now, and certainly Excel is more comfortable a user experience. However it's clear that Google Docs offer a great opportunity to offer services and tools that are not practical to offer in a locked in Excel environment. The Microsoft equivalent of Google Docs at the moment seems like an 'also ran'.

Scripting on Google Docs
Now that Google Docs has scripting, suddenly this becomes a much more interesting platform, especially since the scripting language is essentially JavaScript.

Using Google Gadgets
The ease of using google gadgets in Google Docs, as well as using them in regular web sites with data sourced from Google Docs makes this combination pretty unbeatable. The issue here really is how to leverage existing Excel capabilities to make the transition either smooth or even unnecessary.

Google Visualizations
I am a great fan of these, and it is also rather straightforward to make your own visualizations, which of course can become Google Gadgets and therefore accessible within Google Docs, or on the web. Some of my work on integrating Google Motion Charts and other visualizations to Excel demonstrate how to easily create them on the fly from Excel data. Clearly, focusing on capabilities that can be built into visualizations gives a straight path that can merge Google Docs, Web presentation and original Excel data and capabilities.

Project thoughts
I have an Architecture Model of a typical enterprise that takes a look at a stated IT strategy and compares it to program portfolio, roadmaps and application landscape to measure and recommend changes to priorities. Right now the majority of this tool is in Excel, and I have been considering redoing it to make it self tailorable for any enterprise. It seems to me this would be a good opportunity to redo the visualization and chart analysis piece of this so I could abstract it from the data, which could come from either Excel or GoogleDocs.

More on this later.

Bruce

Using Cookies from inside Excel

2011-02-22T07:57:00.000-08:00

I've been looking at how to use cookies to pass data between excel sessions and of course outside Excel. Having implemented a method of hiding data inside cells and Excel shapes, I figured this would be a nice next step.

After a few missteps, I eventually got it to work. See the cookies tab of Hiding data in Excel Objects and JSON serialization

Essentially, these are the steps in VBA

Generate an .html file that can read or write cookies, and generate the data required to be stored as part of the javascript.
Create a cookie into which the contents of the cookie are populated by the generated javascript
Read the appropriate element of the document to find the contents of the cookie.

In the cookie I store JSON formatted data. This means that it carries its own definition and can be used directly by other Javascripts.

I hit a few snags along the way that were related to being able to run local scripts with active content, but i came across something called 'mark of the web', which seems to trick the browsers I've tried into being able to run it. I'll be writing this up on web site shortly so you can read all about it there

Adding Callouts to Roadmaps

2011-02-11T03:38:00.000-08:00

Roadmap Project Recap

Some time ago I started to play around with a method to make Roadmaps from Excel data. Since working through the development of it on my Excel Ramblings site and in this article on Egghead Cafe, I had a fair amount of feedback on things people would like to see added.

Adding Callouts

One of the key things was for additional comment space. To do that I've added a whole bunch of new parameters to control creation of and placement of parameters. The idea is that you can select a callout shape to be associated with the main roadmap shape. Various parameters affect the placement of this callout relative to the main shape and both shapes are plotted.

Other callout positioning options are

Make the main shape itself a callout, set the callout shape to 'None' and fix the duration and start date so it appears where you want it to on the roadmap. Mess around with the gap and height to get the look and feel and positioning you want.
You could make the main shape 'None', and include an associated callout shape. Since you can assign a start date, duration, gap, height etc to any shape, including 'None', the associated callout would be positioned and sized as if 'None was to be plotted, but of course only the callout would be visible.

Please check it out and let me have your feedback on this blog, or by email. You can download the latest RoadmapGenerator.xlsm from the downloads area

Using Cookies from inside Excel

2011-02-08T01:58:00.000-08:00

Can we use cookies inside Excel

This is a follow on from my thoughts around using JSON both to store data inside Excel shapes and cells, and also to pick up data from external sources. It occurred to me that perhaps I could use a webbrowser object to collect and store cookie data.

It's now on my list of things to figure out how to do.

JSON deserializer

2011-02-07T16:15:00.000-08:00

Completing the JSON deserializer/serializer

I was missing being able to deal with arrays and could only deal with strings in the first version of this, but have finally figured out how to deal with this. By introducing a parent object for array members I was able to treat arrays the same way as a group of key value pairs. This means that a JSON string as follows

{ "hiddendata": {
   "lastaccess": {
   "username": [
   "k47",
   99
   ],
   "startedat": "2/7/2011 11:55:13 PM",
   "finishedat": "2/7/2011 11:46:55 PM"
   },
   "summary": {
   "timeopen": "474474",
   "countopen": "9"
   }
   }
}

translates into a cJobject that looks like this

hiddendata
hiddendata.lastaccess
hiddendata.lastaccess.username
hiddendata.lastaccess.username._array
hiddendata.lastaccess.username._array.1 fhk647
hiddendata.lastaccess.username._array.2 99
hiddendata.lastaccess.startedat 2/7/2011 11:55:13 PM
hiddendata.lastaccess.finishedat 2/7/2011 11:46:55 PM
hiddendata.summary
hiddendata.summary.timeopen 474474
hiddendata.summary.countopen 9

and can be accessed in various ways such as the below

set cj = new cObject
cj.deserialize(JSONstring)
With cj.child("lastaccess")
   .child("username._array").children(1).asString
   .child("username").child("_array").child("2").asLong
   .child("startedat").asDate
   .child("finishedat").Value
end With
With cj.child("summary")
   .child("timeopen").value
   .child("countopen").value
end With

I haven't bothered with date decoding for now. Next step is to test all this with a whole bunch of JSON examples

Topics under consideration

2011-02-07T01:38:00.001-08:00

What next

Thinking about what to work on next for the Excel Ramblings site, I wondered how I could pull together the work on JSON along with google visualization serialization to open up access to web service capabilities that Excel really doesnt get to easily. Looking at an article by Pete Bromberg on eggheadcafe, it struck me that i could process responses like this and populate Excel Sheets with some structured data taken from sources like otter, or alternatively to stream data live from google docs into Excel - indeed reverse engineering google table data into Excel. This would just mean providing a form where the available fields in the feed can be selected for inclusion in the query, after which it's just a case of deserializing and formatting in Excel.

Projects under construction/consideration

These are the items I am working on are thinking about

Finish the JSON deserializer to handle arrays & data types other than just string.
Provide a means to import arbritary data, as defined by a JSON data feed into Excel
Import google docs data into Excel directly using a dataurl mechanism set up in google docs
Improve the roadmap project with callouts and clickable shape manipulation

What Else

Please let me have ideas for how these should develop at excel@mcpher.com

bruce