Why implement a countdown timer When performing a task that will take some time it is useful to show how long it has taken, and how long to go. In this section we will implement […]
We had managed to generate a completed puzzle from scratch in the previous section, following the first step of creating a viable solver. The next step is to take the completed puzzle and remove cells from it till […]
Planning Solving according to strategies is pretty straightforward. Generating puzzles is more complex and exercises your comfort with recursion. In general terms generating a puzzle follows these steps Create an empty grid Use brute force […]
Recap In the introduction to the Roadmapping project we created a rudimentary procedure and class that read in our data and created the basic structure we will need to produce generalized road-maps from simple tabular data. The next step […]
As I’ve said before you can use Sumproduct() for many things that it probably wasnt designed for. Various array functions could be used to do the same thing, but trying to do it all in sumproduct is […]
All about performance analysis, tips and tricks for getting the best out of both Apps Script and Excel. […]
When you click on an infobox, use a quicklink or select a category on a generated map the zoom level will change (if you have enabled it in your excel parameters.). Click on an infobox or use a quicklink Here […]
What is the infoBox An infoBox is an enhanced infoWindow. Details can be found here . I will be converting the Google Maps modules on this site to use the infoBox rather than the native Google Maps […]
javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Earth Api You will find tons of content on Google Earth , most of which is described […]
javaScript: creating google visualization Data Views This relates to Data Driven Mapping applications and the Javascript ‘HowTo’ section Google Visualization dataViews As mentioned in Using Google Visualization DataTables a master google DataTable is created for all data in […]
VizMap: the framework javascript This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]
VizMap: the vba to generate vizMap application This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example […]
VizMap: the common vba for setting up parameter sheets This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
Tailoring the look of your VizMap application For this example, I had creates a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) is downloadable from aboveand the example Parameter WorkSheet is called VenuesParameters. […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
This relates to Data Driven Mapping applications […]
VizMap: the Viz Html parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and is used to define the filename of the to be generated application. It also contains customization […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
If you have a business account for Google Mapping, you’ll need to provide your client id and crypto key. These things are meant to be kept private, so I did not provide a mechanism for […]
Plotting markers on on a Google Earth This article will cover how to create a basic KML file so that your Excel data can be mapped on Google Earth. There is a more advanced implementation […]
Recap We’ve read our data, and set up the parameters. The data structures are working according to our debug reports, and we have dipped our toe into recursion. Now to pull it all together and […]
A common Excel task is to categorize values into bands. You can use Frequency or some pivot table capabilities to report on that, but lets say that you want to add a categorization characteristic to […]
What can you learn here? Interrupt comments Change shape characteristics Add timestamp Modifying the behavior of Cell Comment Processing Download now This article shows some techniques to interrupt the processing of cell comments in Excel […]
Given a start point, a distance and a heading, it is possible to work out the resulting latitude and longitude. See the maths behind it here. See Radians Versus Degrees for the utility functions we […]
A radian is the ratio of an arc’s length to its radius. See the wiki diagram below Radians tend to be used in most trig formulas related to distance calculation, so we are definitely going […]
javaScript: Click events in Google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Events in Google Earth I deal with event handling in Google earth in Using Google Earth but dealing […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
javaScript: creating tabbed content This relates to Data Driven Mapping applications and the javaScript ‘howTo’ section. For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]
VizMap: the element parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and defines the dictionary fields required for each tab on a markers infoWindow and how to handle their […]
VizMap: the tabs parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and defines the dictionary fields required for using as replacements for the standard placemark icon. For this example I […]
VizMap: the measures parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and defines the dictionary fields required for charting and how to aggregate their contents. For this example I had […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
VizMap: the vba to join master to transactional data This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]
Tailoring the look of your VizMap application Info Window look and feel There are a number of things you can tailor to change the behavior and look of your generated application. VizMap Element positioning allows you […]
Overlaying polyLines to make flight paths on google markers This article will cover how to add flight paths to Google Maps. For a quickstart, see Step by Step Guides Try it out first ? If you […]
Navigating using quicklinks and categories Try it out first ? For a summary step by step explanation see How to add quickLinks to a Map If you just want a to try it, then you can […]
Sorting collections is something you might need to do. In this example, we’ll use the cMyClass object we created in Get Started Snippets to demonstrate sorting a collection of objects and how to use recursion to sort the object’s […]
This is a blog post of which the excerpt is below. Click on the title to read the full post. Read the post Visualization of Sites to Tags with d3.js
Quite often Sumproduct is used in cases where a Pivot table just doesn’t cut it. Here’s how to make Sumproduct respect filters in a data table. Pick a column in your data table Lets use […]
This time we are going to use SumProduct and Search together to check for near matches in data. Search() and Sumproduct() Combining Search() and Sumproduct() will give us the option of matching partial strings. This opens up a whole new set of […]
I pretty much started here. Since the objective was to migrate from a VBA project to a Google Apps script version of the same, my approach was to write the skeleton, and emulate the underlying […]
Note that this article uses the Apps Script UI service, which has since been deprecated. I leave it here for interest. The equivalent of inserting a shape in Google Docs is to insert a drawing, […]
Put addresses on a map as markers We’ve already looked at how to geoCode addresses using various APIs and how to create maps with Google Maps from Excel data. Now let’s look at using Bing Maps to put […]
Put addresses on a map as markers We’ve already looked at how to geoCode addresses using various APIs and how to create maps with Google Maps from Excel data. Now let’s look at using Bing Maps to put […]
Put addresses on a map as markers We’ve already looked at how to geoCode addresses using various APIs and how to create maps with Google Maps from Excel data. Now let’s look at using Yahoo Maps to put […]
We’ve already looked at how to geoCode addresses using various APIs. Now lets look at using Google Maps to put markers on a Map using an Excel worksheet as input. As usual you can find the […]
GeoCode Excel Data with Bing Maps If you haven’t read about Bing rules on doing this, please do. Since we have done this in Google Maps, I thought it would be worthwhile to see how it’s done […]
In a previous section , I explained how to create javascript to create a Google Motion Chart directly from your from Excel data, and optionally embed it in your workbook. Since it is of course […]
Normally I’d want to use the Rest to Excel library to get data into Excel, but sometimes you have to resort to web scraping. My preferred route is to do something in scraperwiki, then use a rest query […]
Named ranges – the opportunities for adaptation and reuse that referring to ranges of data conceptually rather than by cell references really changes your approach to how you use Excel. You immediately start by assuming […]
Treeview traversal and recursion In Rest Results Explorer i showed how to Create a treeview from json using the treeView control. Objects like the treeview are generally dealt with recursively – see Getting started with recursion and Getting to Grips with recursion for […]
What can you learn here? Use Google Books API Convert jSon Tailor columns and output Getting data about a book using the ISBN number. Get it now In this section we will look at how […]
In Hiding data in Excel Objects I introduced the concept of hiding data in shapes by encoding it into jSon, then hiding the serialized result in a property of a hidden shape. One of the […]
In Delegating xml to json conversion to GAS I showed how you could get Google Apps Script to convert XML to JSON by posting the XML and getting back the JSON response – using VBA to illustrate. […]
Interacting with APIs This site uses a lot of calls to Google APIs in order to connect Excel to web applications. There are a number of custom classes used everywhere such as Data Manipulation Classes and Excel […]
What can you learn here? sing cRest Advanced capabilities Building new libraries Accessing the cRest class directly get it now Up till now we’ve been looking at the modules in the Rest to Excel library as the integration […]
What can you learn here? Using cDataSet Using cRest Using cJobject Putting the Rest response on the sheets get it now Now that we have the tools to generate existing or adhoc rest queries, let’s look at how the data […]
The rest excel library uses cdataset to manage the data it retrieves from queries. In Excel, it is often useful to use tables. You can easily create an Excel table from any cDataSet. See How to make a table from […]
JSON data can go many levels down. Whereas the cJobject is designed to mimic javaScript’s capability to deal with this, eventually you will want to populate a two dimensional table in Excel with some data. Consider this […]
On his oUseful blog, Tony Hirst showed how to use Open refine plus various APIs to research which candidates were standing for election in the wards in which they live. This blog has unfortunately disappeared therefore […]
In Getting data out of scraperWiki I showed how to get data into Excel or Google Docs from scraper wiki public data. I thought i may as well start an occasional directory of interesting scrapes I came across. Of course these […]
What is scraper wiki Scraper wiki is an environment which allows you to put some structure around data published in web pages. In addition, it has an sqlLite datastore through which many users of scraperWiki make […]
What can you learn here? Using cRest Multiple sources Combining Putting together multiple queries on same sheet get it now So far we’ve been considering Rest queries to be discrete. In this section we are going […]
What can you learn here? Library of Rest Calls One off queries Argument structure Running one off queries using the cRest class get it now Now that we have a generalized mechanism to easily populate […]
What can you learn here? Library of Rest Calls How to add new entries How to query from data Adding more content to the rest library get it now Now that we have a generalized mechanism […]
What can you learn here? Populate a treeview do REST queries interactively explore the results How to get rest API results into a treeview get it now In Create a treeview from json we looked at how to […]
In javaScript and other languages, creating json representations of an object is a breeze. Something like JSON.stringify (theObject) will do it for you. Aside from liberating data from your application, it makes debugging so much […]
As covered in jSon there are many ways to get jSon data into excel. You may be tempted to try to use Excel JSON conversion or get data from google docs. These techniques are for very specific use cases – […]
Since this is a ticker collection application we want this to run every now and again. At first I thought about using excel to schedule itself, but too many things generally go wrong with that, […]
Getting data from the bitcoin API The bitcoin public api can provide different types of exchange data for a number of venues. The endpoint for each combination is different, for example these are all valid endpoints […]
Making a ticker dashboard for all venues Next we’ll create a dashboard – most recent ticker data for each venue on one page that looks like this. The dashboard is described in this piece of […]
Setting up the workbook layout and format Here we are going to create a workbook from the JSON manifest. The relevant section is below, and can be found at manifest.child(“setup”). Each type of data is identifed […]
Storing other objects in cJobject If cJobject was just about getting stuff in and out of JSON, then we’d stop here, but you’ll find that I use it in pretty much all the VBA examples […]
Iteration As per the examples in cJobject deep dive the most common navigation of the cJobject is by iteration through the children, or by direct access as covered in How to access children. However there are a few […]
Children As mentioned in the introduction, each cJobject is either a key/value pair or is the parent of some more cJobjects. Accessing the children of a cJobject is straightforward, and as close to the JavaScript as […]
Children shortcuts As covered in How to access children, cJobect children are roughly equivalent to JavaScript objects’ children. However the syntax of VBA means that we have to be a little more verbose than JavaScript. To […]
Geocode Excel Data with Yahoo Placemaker If you haven’t read about Yahoo rules on doing this, please do. Since we have done this in Google Maps, and in Bing Maps, I thought it would be worthwhile to see […]
There are still many APIS that only respond in XML. Although Excel has XML native it’s still a pain to deal with, and I really wanted to abstract the data format from the processing of […]
This is real interesting mashup using an new Rest to Excel library entry for the urbarama API. This is a repository of interesting architectural interests. The API is quite interesting in that you can you supply a max […]
In Rest to Excel library there are plenty of examples of populating sheets from rest queries. Usually you simply name the columns to match the data you want to extract and you are good to go. However, you […]
Why would you want to? If you want to learn about the cJobject and jSon with VBA you may want to start with How to use cJobject First off I talk about ‘hiding data’. What […]
If you want to embed or publish Excel data to web pages, there are many solutions. Excel 2013 does fancy things with skydrive to share data, and there are plenty of activeX plugins and so […]
More color schemes The purpose of this app is to select related colors that appear in a predefined scheme (for example pantone matching system etc). That means I’m always looking for more color schemes. If […]
In Color scheme explorer the main color processing scripts are hosted in Google Apps Script. There’s particular no reason to do this, except that it means that I can ensure I use the same algorithms in JavaScript as I do in GAS. Building […]
Deprecated Parse.com has now become parseplatform.org and i will be investigating it at a later stage. I am however leaving the page as if as you may still find it useful. Contact me if you […]
Overlaying shapes on markers Try it out first ? If you just want a to try it, then you can download an example application (googleMapping.xlsm) and come back here later. First you should take a look at Adding markers […]
Following on from Coloring maps and other shapes, here’s a bit of fun that takes the tables of Pantone colors of the year dealt with in Selection of pantone ramps, and applies them to a […]
Alternative schemes showed Ramping color swatches using the Pantone spring 2013 collection colors as the ramp milestones. So far we’ve been laying out a linear ramp. How about a circular one? Luckily How to create a heatmap doughnut […]
When I was writing the Color Fiesta section, I noticed a tweet about something called the Pantone Spring 2013 color collection. I had no idea how pantone worked, so I went off to find out if it […]
Lets say that we want to create a circular color ramp, as used in How to create a heatmap doughnut chart, as in the example shown on the right This is actually 2 steps, using […]
A doughnut chart is sometimes used to show relative category shares, like this. Doughnut (and pie) charts have a bad press. It is generally difficult to see relativity between categories, and there is usually a […]
In Ramping color swatches I showed how you count transition smoothly between colors by manipulation of the red, green and blue components of the source and target colors. I used some dulux paint color names to […]
In Looking up color table I showed how to look up colors from a growing table of color models, in order to create color swatches. These show distinct colors with a hard stop between them, like this […]
Manipulating strings in Excel. As you will know, Excel very quickly runs of out steam as you try to do any kind of string manipulation with cells. Even very basic requirements cause you reach for […]
Here’s how the promise framework is implemented. This is kind of extreme VBA and Excel is very unforgiving. If you are playing around with this, I advise saving often. cDeferred versus cPromise These two classes […]
You can use the Windows API to call setTimer() to do something a little like setTimeout in javaScript. However there are some serious gymnastics to be able to have multiple timers going at the same […]
I posted something on how to do exponential backoff using jQuery promises a while ago. At the time, I never thought I would be implementing that in VBA because of all the asynchroncity and callbacks needed, […]
Here’s where it gets really interesting. Let’s say that we need to get some large number of spreadsheets from Google Docs. We want to do it asynchronously, and we also know that google docs will […]
In Promises in VBA I introduced how to use promises in VBA to orchestrate asynchronous activities. One of the things that you can do in VBA asynchronously is to get data from a workbook or a database […]
From Excel 2003, you could use Tables. A cDataSet is somewhat like a table in that is structured data, expecting headings etc. When you use bigCommit() in cDataSet, it wipes out the existing Excel data […]
Hopefully you will have read How to use cDataSet to see how to abstract data from your sheet and taken a look at the cDataSet Model to see which methods and properties are available. I […]
Using cookies straight from Excel Get it now Why would you want to do this? You know what cookies are, and think of them as being a mechanism for web sites to store information about […]
Strategies The first step is to put together a basic solver. In Sudoku, there are 3 Regions – Row, Column and Box, but you probably know that already if you are reading this. Other terminology […]
This problem is often used to show that statistics are sometimes counter intuitive. If you try to imagine how many people you would need to have a 50% chance of two people sharing the same birthday, you’d […]
Nowadays structured references are the preferred way to access table data in Excel and VBA. Although most of the examples you may have looked at so far have talked about ranges, you can also specify […]
Hadley Wickham has added some data management capabilities to R which are pretty useful for getting data ready for tabulation or visualization. One of these is melt(). Melt can do lots of things, but one that caught my […]
What can you learn here? ramp color scales using in surface charts brighten/darken Creating Charts with colorRamps Create a heatmap in Excel showed how to make charts using the heatmap color ramp. Now that we have […]
VizMap: the generated javaScript This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]
VizMap: Javascript howTos This relates to Data Driven Mapping applications Firstly – I am not an expert in javaScript, but in the accelerated learning process to discover what was necessary to create this application generator, there […]
javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Maps Api You will find tons of content on Google Maps, most of which is described […]
What can you learn here? Library of ADODB connections Populate from databases Use SQL on closed workbooks A library to populate Excel tables with SQL query results get it now Using SQL to populate Excel […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
What can you learn here? Select worksheets to copy Choose selected columns cDataSets filtering capability Copying partial tables between sheets Get the cDataset.xlm from here By now you are probably familiar with the cDataSets classes. Here […]
What can you learn here? Optimization and profiling filling ranges using VBA using match to partition data Another approach to Array formulas and large Ranges I previously covered a VBA solution for a problem that could […]
What can you learn here? Optimization and profiling Filling ranges using VBA Array formulas I previously covered a VBA solution for a problem that could have been solved using array formulas. However, it really wasn’t the best […]
What can you learn here? array formulas VBA using VBA profiler using worksheet profiler Are Array formulas viable with large ranges On the Excel Blackbelts forum, someone asked a very interesting question about how to analyze relationships between […]
What is bit twiddling Sometimes you need to do many logical operations at once. For example, consider the problem where you want to find which numbers exist in both of two strings, which are stored […]
Let’s take the concept of named ranges a little further. Its true that the TABLE capabilities in later versions of excel give you much more opportunity to organize your data than before, but if we […]
Organizing ranges for reuse – minimizing maintenance of data ranges […]
This is my favorite function – you can do so much with it. What I like about most though, it is that it seems to have been added to excel as a kind of afterthought – its […]
Using array formulas rather than sumproduct We looked at the use of sumproduct() previously. Lets repeat that using array formulas instead. Array formulas will give you more solution options than sumproduct() can – anyway you will see that sumproduct() is just a special case of an […]
GeoCode addresses with Google Maps API If you haven’t read about Bing rules on doing this, please do. In this section, we will look at what Google responds to a request for an address geoMapping. Using […]
You’ll probably now be familiar with cJobject and want to do some fancier things. Here are some examples. You can download a vanilla cJobject workbook from the data access section of the downloads page, or construct your own with […]
If you already write Apps Script add-ons, you’ll know that debugging can be a little challenging, and although you can use the developer tools in chrome on a running add-on, breakpoints don’t persist between sessions […]
Creating heatmaps An easy way to create heatmaps is to use conditional formatting in your spreadsheet. However, we are going to look at creating heatmaps as an Excel Chart, and also to automatically create a […]
The mathematics of color is a complex and interesting topic. Here you’ll find Google Apps Script, VBA and javascript examples of color manipulation including finding the closest match to a given color in a defined color scheme, and the infamous heatmap doughnut chart. There are lots of references to color on this site, and it seems to be a subject second only to jSon in popularity […]
Separating coding from defining I think of 3 different roles in workbook app definition Consume – Use the apps and consume or maintain the data in it, and provide process, informational and operational needs Define […]
What can you learn here? Library of Rest Calls Populate excel tables Add your own A library to populate Excel tables with rest query results get it now I found myself writing similar code to […]
JSON and Excel using VBA classes that emulate JavaScript objects, parsing and stringifying JSON and dealing with data from external services. Includes a data driven REST-EXCEL library, and implementation of Google oAuth2 authentication […]
Freeing Excel Data through JSON There have been a number of topics on this site dealing with the serialization of JSON, for example to create input to google visualizations, Hiding data in Excel Objects , Integrating Excel with […]
Now we know how about Hiding data in Excel Objects, Excel JSON conversion and Dynamic Forms let’s combine all that as an enhancement to the Roadmap Generation project. The objective is to embed traceability data in the generated roadmap shapes so […]
Implement a popup form at the mouse position get it now Now that we know How to position a userform on the mouse, here’s a simple example of an implementation that uses such a form to […]
What can you learn here? Create roadmaps Presentation quality Learn roadmap options User Guide to Roadmapper – Get it now Elsewhere on this site, I take you through the development of a roadmapper in Excel. Looking at […]
What can you learn here? Get started with cJobject Short code snippets Abbreviated explanations Quick examples (cDataset.xlsm) This is intended to be a fasttrack to get started with the cJobject that are used throughout the examples on […]
What can you learn here? Make a tag cloud cJobject as ‘on demand’ the cTagCloud class Making a tag Cloud in Excel – Get it now Although most of the discussion about How to use cJobject is […]
What can you learn here? ramp color scales gradient between colors Use a ramp library Generalized Color Ramps If you read A tagCloud in Outlook, How to create a tag cloud or Create a heatmap in Excel you would have […]
There are a few items on this site that cover color ramps and other color manipulation topics. I’ve built up a few snippets on color management in VBA so I thought I should centralize them for easy […]
In Playing around with colors in VBA I introduced the concept of a color table. This is just a list of a name and a matching hex color code in a big excel table. I have been […]
Let’s say you have a color, and you want to find the closest match in defined color scheme set. The first problem is defining what closest means. It’s not a simple problem to solve. There […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available for directly from O’Reilly or Amazon. Many applications have their own names for colors in their palettes. This example […]
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 […]
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 […]
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 […]
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 […]
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 […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines