Integrating Squirrel365 with Tableau

The Squirrel365 extension for Tableau seamlessly blends the secure, managed data environment of Tableau with the remarkable app-building capabilities of Squirrel365.

Whether you want to add what-if simulation to your dashboards and reports, create a complete Squirrel UI for your Tableau data, or anything in between – adding Squirrel to your existing Tableau dashboards opens up a whole new world of possibilities.

With the Squirrel365 extension, you can easily pass data from Tableau data sources and worksheets dynamically into an embedded Squirrel project. This provides you with the best of both worlds. Some of the key benefits include:

1. Use spreadsheet logic to build app-like functionality into your Tableau dashboards
2. Utilise the security model of your Tableau environment to control access to data and projects
3. Take advantage of visualisations not available in Tableau (you can even build your own)
4. Show or hide elements dynamically based on data
5. Add what-if analysis and scenarios
6. Writeback – capture information input within your Tableau dashboard and save it back to a database or online spreadsheet*

* may require a 3rd party service such as Zapier

Dynamic Data

When the data in Tableau changes, the embedded Squirrel projects are immediately updated therefore providing a seamless experience for users.

Getting started with the integration is simple. Firstly, specify in Squirrel which areas of the spreadsheet should be populated with data from Tableau. Then, simply retrieve the project ID from the Squirrel project manager and enter it into the Squirrel365 extension. This will allow you to map the data back to the Squirrel project.

This process provides seamless integration between the tools in a matter of seconds.

See it in Action

Watch the video below to see the Tableau extension in action:

Try it out

Right now the extension is available as .trex file that you need to load from your desktop. If you would like to try it out please get in touch. We hope to get it on the Tableau exchange soon to make it even easier to get started.

Not a Tableau user? Don’t despair you can still sign up for a Squirrel365 account and take advantage of all that Squirrel has to offer.

SAP Business Objects Web Intelligence users – There is a similar integration available for SAP Business Objects. The video below shows custom elements, created with Squirrel365, displaying data from the Webi report providing all the same benefits as the Tableau extension.

If you would like to try it out, sign up for a Squirrel account at squirrel365.io and start building your first integration for free.

Spreadsheets contain knowledge

I’ve often considered how, throughout the world, spreadsheets contain vast amounts of knowledge. Wrapped up in formulas and data is a wealth of curated information and specialist knowledge in the form of logic.

Recently, I came across another example of this in a tweet[1] shared by Dave Kellogg. It included a link to the equity calculator[2] by SaaSoptics.

[1]The original tweet from SaaSOptics :

Download our new Equity Calculator template that helps you understand what the impact to your #equity will be if you were to raise or sell today based on critical inputs around #SaaS company's performance & the cost of #capital. https://t.co/N2i3F6NSEK pic.twitter.com/HnUAkQhl6H

— SaaSOptics (@saasoptics) August 3, 2022

Salesforce’s extraordinary no-code, ROI sales enablement app

Recently I talked with Ziv Barzilay from Salesforce on the Limitless BI Podcast to discuss his remarkable sales enablement app. Here are a few highlights of his extraordinary story.

Q branch

Ziv works in “Q branch” at Salesforce (yes like in James Bond). As Ziv puts it, Q Branch is:

“an innovation team providing business apps for various groups, including sales engineers and account executives, using cutting-edge technology”

One of the cutting-edge technologies they use (and the one Ziv is responsible for) is Squirrel365.

Using Squirrel

You might know Squirrel as a tool which allows you to create interactive web content like ROI calculators and interactive financial models directly from spreadsheets. But Ziv has taken Squirrel to a whole new level. In his words what he has created is:

“a full-blown business application which provides a next-generation business value framework for our account executives”

In other words, a web-based tool allowing salespeople at Salesforce to articulate the value of Salesforce solutions to their prospects in a fully customized and engaging manner. You could say it is an ROI calculator on steroids, but that would dramatically understate its capabilities.

Business Results

But any business application is only as good as the results it generates and here too Ziv’s app comes into its own:

“we have about 3000 account executives using the tool, we just rolled it our a year ago and [in that time] we were able to touch more than ¼ billion dollars of annual contact value (ACV)”

Which is all the more impressive given the application was initially built using no code in under 2 months by one person.

The Application

The strength of the application is that is offers a guided path through the complex ROI calculations which means the sales team can be self-sufficient in using it with their customers and prospects.

The culmination of the app is a screen which gives a presentation quality “dashboard” explaining the ROI benefits of moving to the Salesforce cloud.

Try for Yourself

If that has piqued your interest then you can hear the full podcast at Limitless BI – Business Intelligence Podcast.

Or you can see Ziv demo an early version of the app at Webinar on demand – customer spotlight Salesforce – Squirrel365.

Or if you want to try using Squirrel for yourself then register for a free account here.

Digging Deeper into the Remarkable Excel Formula Bot

Earlier this week I wrote about the remarkable Excel AI formula bot which turns English descriptions into Excel expressions. For example:

Input: return the smallest non-zero value in a range

Output: =MIN(IF(A1:A10>0,A1:A10))

but in the last few days I have discovered it is even more remarkable than I first though, for two reasons.

1. it is based on a general Artificial Intelligence (AI) algorithm and not one tuned to Excel
2. the idea to create the bot itself was hatched in “conversation” with the same AI

General Excel AI (and SQL AI)

Initially, I presumed that David Bressler, the bot’s creator, had written both the front end UI and the Excel formula generation AI engine.

However, on digging deeper it turns out that it is the the general purpose GPT-3 engine at OpenAI.com which produces the formulas.

This is truly remarkable because this algorithm (to my knowledge) was never trained specifically on Excel. Nonetheless it generates sophisticated Excel expressions e.g. this screenshot is from the OpenAI playground.

It can also do the reverse and explain an existing formula:

But more than that, because it is general it extends to other areas, for example SQL:

And even “understands” the difference between different dialects of SQL:

(even thought I mis-spelled Postgres)

All of this is available in a straightforward API which the Excel Formula Bot calls to get its results.

If you want to play with the OpenAI playground, you can sign up for a free account at https://openai.com/api/.

Perhaps we should produce a connector for Squirrel365 !

An Excel AI Idea Generated by an AI “conversation”

I learned of the role of OpenAI in the Excel formula Bot from a post by its creator Davie Bressler. In the post David explains the genesis of the whole project. (It is mentioned on the front page of the Excel bot itself but in my excitement I must have missed that initially.)

Basically, David was in “conversation” with the OpenAI bot and it suggested the germ of the idea of  the Excel Formula Bot and provided an available domain name for it as well as the underpinning AI technology.

It all makes my head spin a little so perhaps better to read it in David’s own words.

No Code Strikes Again

So it turns out that the Excel Formula Bot is a no-code project pulled together in next to no time from a number underlying technologies through web based APIs.

A glimpse of the future and a story close to our hearts here at Squirrel365.

An AI Bot to write Excel formulas ???

Over the weekend I received the weekly newsletter from the chaps at Full Stack Modeller (it’s a quick and quirky read for anyone in the spreadsheet/modelling world, written alternately by Giles Male and Kenny Whitelaw-Jones, I thoroughly recommend it).

In this issue there was a short section about “Excel formula bot”, a free, AI-powered bot written by David Bressler that turns a plain English description into an appropriate Excel formula.

Kenny’s example was: “remove the last four characters from any text label” which returned him the formula =LEFT(A1,LEN(A1)-4)

Must be a lucky hit I thought. How wrong I was!

This bot is a truly remarkable (and slightly scary) piece of technology as you can see from my experiments below.

Experiments with the Excel Formula Bot

First I just checked a variant of Kenny’s example by asking “get the text between the dot and the comma in another piece of text” to which the reply was:

=LEFT(RIGHT(A1,FIND(“,”,A1)-FIND(“.”,A1)),FIND(“,”,A1)-FIND(“.”,A1))

which is correct but perhaps a little long winded, however a slight change in the request gives a better answer:

Input: get what is between the dot and the comma in a piece of text

Output: =MID(A1,FIND(“.”,A1)+1,FIND(“,”,A1)-FIND(“.”,A1)-1)

Then I remembered a post I had commented on last week about getting the smallest non-zero value in a range, so I typed “return the smallest non-zero value in a range” and got

=MIN(IF(A1:A10>0,A1:A10))

Which was at least as good as all the suggestions which had been made in the post.

Now I was hooked. I launched one of my favourites; “return the annual growth rate from two 5 year values”, to my surprise, it got this right too replying:

=((B2/A2)^(1/5))-1

extraordinary! It gets even more useful if you ask the general question “return the annual growth rate from two values” which gives:

= ((Ending Value/Beginning Value)^(1/Number of years))-1

So then I thought, “what about Squirrel365 specific ones?”, for example in Squirrel it is often useful to be able to toggle a value in a cell, and sure enough:

Input: toggle a value

Output: =IF(A1=1,0,1)

What about VLOOKUP?

Finally I went to the old stalwart which everyone knows but can never remember; VLOOKUP:

Input: the value in the third column of a B1:F17 matching A1 in the first column

Output: =VLOOKUP(A1,B1:F17,3,FALSE)

But some people prefer INDEX and MATCH so I added “using index and match” to my request and sure enough I got:

Input: the value in the third column of a B1:F17 matching A1 in the first column using index and match

Output: =INDEX(B1:F17,MATCH(A1,B1:B17,0),3)

But what about XLOOKUP? The same is true, changing to “using XLOOKUP” gave:

Input: the value in the third column of a B1:F17 where the first column of B1:F17 matches A1 xlookup

Output: =XLOOKUP(A1,B1:F17,3)

Failing on Primes

In the short time I played with it, the only thing I could find to break it was to ask it about prime numbers:

Input: return the tenth prime number

Output: =PRIMENUMBER(10)

Input: is 2343 prime

Output: =ISPRIME(2343)

Which looked good until I realised that neither of these functions exist in Excel (or Google Sheets). Perhaps this is just the bot predicting a future release!

An AI Bot for a Complete Excel Profitability Model?

Primes notwithstanding, I was truly staggered (and as I said slightly scared) at what the bot could do.

How long before I could ask for “a model to predict 15 year profitability of our network in the face of the acquisition of one of our large customers by a competitor” and get a model with all appropriate drivers in it returned in a few seconds. Before seeing the Excel Formula Bot I would have been very sceptical, now I am not so sure. Perhaps AI is coming after our financial modelling jobs after all.

If you want to try it out and amaze/scare yourself, then you can find the bot at:

https://excelformulabot.com/

Squirrel Scenarios

We have received more positive feedback about the new scenario component in Squirrel version 1.11 than any other single feature I can remember. With Squirrel scenarios, designers can give their end-users the ability to save sets of inputs (i.e. scenarios) for later retrieval.

For the end users this means a simple, one-click method of saving their current thinking as they use a published model. Perhaps to save high/medium/low forecasts, or different configurations of a product or even different funding options for building a wood pellet factory (as we shall see later).

This eager reception is not surprising.  Simpler, more controlled and more engaging sharing of spreadsheets is a key use for Squirrel. Scenarios take this to the next level (certainly when compared to the same functionality in Excel).

(if you don’t know what Squirrel can do, there are a number of examples here. Remember each one of these is a just a different view of a spreadsheet)

Taking Scenarios to a New Level

Compared to the scenario manager In Excel, Squirrel scenarios are much simpler to use. Simpler for both for the spreadsheet creator and those viewing the resulting model.

Finding the scenario manager in Excel is the first issue. It is tucked away under “What-if Analysis” on the “Data” ribbon.

Once you find it, you have to work through a number of dialogs. Unfortunately, these mix the needs of the spreadsheet creator and those of the end-user.

First is the overall dialog. This has good options for the spreadsheet creator but rather too many for the end-user:

Next, one to define the cells to be saved. This is key for the spreadsheet creator but really the end-user should never see it:

Finally the values for each of these cells. Both creators and users will find this a a fairly basic way to interact with the model.

On the other hand, Squirrel splits these two activities so they are simpler for both parties.

First, the desinger simply drags the Squirrel scenario component into the project:

and then specifies the ranges to save. (Note: unlike in Excel, these ranges can be from different sheets in the workbook)

That is it, that is all the desinger needs to do !

In the published project the end-user adjusts the values using interactive controls (sliders etc.). Then clicking on the Squirrel scenrios’s “save” button provides an easy interface to save their current state:

The loading process is just as easy:

Much soother for both creators and end users.

Getting Started With Squirrel

Are you still sharing your spreadsheets the old-fashioned way? Perhaps scenarios will give you another reason to switch to Squirrel.

Get a free account for Squirrel (complete with scenarios) by clicking on the “Get Started For Free” button in the middle of our home page.

For inspiration, look at the wood pellet example referred to above. The folks from Future Metrics have created many Squirrel projects to highlight their expertise in the wood pellet market. The latest one looks at funding options for building a wood pellet factory and includes a scenarios capability. You can find it here. You can also fins a complete catalog of their public Squirrel project by scrolling down their home page

What is IBIS?

Last week some of the Squirrel team attended the Infosol IBIS event in Arizona. IBIS is the leading independent BusinessObjects education and knowledge event.  We were there both to hear how people are using Squirrel and to update them with all our latest product news.

Many BusinessObjects customers use Squirrel as a replacement for the missing dashboarding in the BOBJ BI Platform. Others use Squirrel stand alone or with direct access to their databases through the InfoSol InfoBurst product.

IBIS, it is a great opportunity to see BI organisations showcase their work, impart their knowledge, and share their experiences. With many talks to choose from each day, there was never a dull moment and always an opportunity to learn new things.

Squirrel Case Studies at InfoSol IBIS

There were many fascinating case studies, including:

• USPI – improving Healthcare Operations using BusinessObjects and Squirrel365
• Hain Celestial – Improved Processing and Visibility to Warehouse Shipments Using BusinessObjects and Squirrel365

These IBIS sessions were notable for their operational nature. Usually dashboards stop at providing data on historic trends but these were different. They also provide near-real time information to drive operational decision-making in the USPI medical facilities and Hain’s warehouses.

In fact, these dashboards were great examples of “BI Dashboard Apps” as discussed by Squirrel’s own Donald MacCormick in his keynote “BI Dashboard Apps – The next big thing in BI”.

In terms of standalone Squirrel dashboards, the standout examples were from Bill Straus, CEO of Future Metrics. BIll presented twice during the conference:

• How Dashboards are Helping International Trade in Renewable Fuel that Replaces Coal in Power Generation
• How Dashboards are Helping Replace Hydrocarbons with Carbohydrates in the Power Generation Sector

These fascinating sessions explained how Future Metrics is helping reduce our collective carbon footprint by championing a move from coal to sustainable wood pellets. Included in the presentation is some of their suite of 15 Squirrel dashboard calculators (all built “no code” from spreadsheets). They use these on their website to share and communicate their industry expertise (you can see all their dashboards at https://www.futuremetrics.info/).

Hackathon

Continuing the environmental theme, InfoSol IBIS featured a Squirrel hackathon. Here attendees were invited to build a Squirrel project based on a spreadsheet to calculate personal carbon footprints.

Neither of the two winners had used Squirrel before the conference but both quickly took to the tool and produced great looking interactive carbon footprint calculators. The younger winner (who was only 13) left the conference with a plan to hold a similar hackathon for her fellow pupils back at school.

On a lighter note, David Behr from USPI presented a session on:

• Building an online jewellery store mobile application for my 11-year-old daughter with Squirrel365

Which featured a Squirrel app he created which his daughter uses to sell bracelets she makes online. (Even school children have a side hustle these days)

Astronomy at InfoSol IBIS

However, for many of us, the highlight of the Infosol IBIS was an evening of star gazing. Mark Johnston aka AZAstroGuy (https://www.azastroguy.com/) brought along his telescope and his vast  knowledge of all things extra-terrestrial and treated us to two hours of wonder. We saw everything from binary star systems to whirlpool galaxies all projected on the big screen from his telescope. A truly remarkable evening.

Some photos from the night depicting the Milky Way! Yes – the sky was that clear!

Quick set-up property sheets

Version 1.12 sees the introduction of the Quick Set-up tab in the property panel, making it easier to configure and style components. This new tab contains the basic properties required to get a component working and looking good.

Use the new accent colour, pre-canned styles and text styles to set up your component in double-quick time.
Clicking on one of the “See All Properties” buttons will jump straight to the appropriate section in the All Properties tab. Here you can further customise the look or behaviour of the component.

Another enhancement is the introduction of the RECENT COLORS list in the color picker. This makes it easy to ensure you are consistent with colors.

If you make a change in All Properties, that is not compatible with the selected style on the Quick Set-up tab, you’ll see an icon indicating that the style has been overridden. Click on this icon to re-apply the style:

Learn more about Quick Set-up Properties

Object Browser

The object browser has had an overhaul to add some key features and make it easier for us to make more changes in a future release. Things you will notice are:

Component Search

It is now possible to search for components in the object browser. Search by name or component type. E.g. entering “label” will find all components with “label” in their name and all Text Label components.

 

Right-click menu

Right click on items in the object browser to bring up a context menu of actions.

Re-ordering of Components

The new object browser follows the common standard of listing the components from front to back in terms of their depth order on the canvas (opposite to previous versions). This means that:

1. Items higher up the list will sit on top of those lower down
2. New items (which are at the front) are now added at the top of the object browser list

Number abbreviation for charts

Abbreviate large numbers in charts by checking the ‘Abbreviate large numbers’ option. This will automatically abbreviate and round to the nearest thousands(k), millions(M) or billions(B). For example, 314,159,265 would be abbreviated to 314M.

More fonts to choose from

Squirrel’s list of fonts has been extended to provide more choice. We’ve added the following google fonts:

Extending Squirrel with Add-ons

You can browse the new Marketplace (https://marketplace.squirrel365.io) for components and functions. At the time of writing the marketplace provides access to a range of add-on components and functions to enhance the capabilities of Squirrel.

Along with the marketplace, we have also introduced the new add-on Development framework. This allows members of the Squirrel community to create add-on components and functions. Details of the framework can be found at https://developer.squirrel365.io.

Add-on Components and Functions

Not only are add-on components and functions now easier to find through the Marketplace, but they are also easier to use inside Squirrel. Once you have subscribed to an add-on in the Marketplace it appears alongside the built-in functions and components in the Squirrel designer.

And now the property panel for add-ons has the same look and feel as those for built-in Squirrel components and functions:

That’s all the exciting new stuff in v1.12.

Why Microsoft built a calculator with Squirrel365 to explain the sustainability of their products

What do you do when you have a great story to tell about your products’ sustainability credentials, which is backed up by solid data, but that data fills many sheets of an Excel workbook and will quickly induce drowsiness in your customer if you try to scroll through it?

This was the situation Gareth Cosker from the Microsoft Surface team in the UK was facing, as he explained in his recent session with us at the Global Excel Summit:

“The challenge is that hardware and everything that goes with it isn’t a simple kind of thing, data is a massive problem. If you are going to present to a customer and you’re going to become a trusted advisor and you’re going to be able to show transparency across the marketplace, it’s not just surface devices that you need to get the data for, you also need to get that for every other Windows 10 device out there, every other competitor within the marketplace and then formulate and visualise that in such a way that the customer can then take that information and make the right choices when it comes to sustainability within their device selection.”

But not only did he have to tame the data, he also needed to present it in the flow of his presentation, i.e. in PowerPoint:

“If you’re presenting in PowerPoint then surely you have to look at software that can integrate into PowerPoint, because you want to keep the flow going, you want to keep that customer engagement”

To solve these issues he turned to Squirrel365:

“what I found from working with Squirrel team is that there is very simple solution to this … that integrates all of that data via Excel into PowerPoint that gives a visual representation … that allows you to make these scenarios clear in front of the customer and have a an impact on them and their understanding of what you offer in terms of the sustainability”

Using Squirrel, Gareth created a sustainability calculator, in the form of a visual app. It allowed him to replicate a customer’s device landscape and interactively show the sustainability impact of various alternative choices, based on data in Excel and all without leaving PowerPoint. This meant that Gareth and the customer could have a real-time discussion about the options:

“in a sales cycle you don’t want them to come back to you a day later and say, oh, actually can you show me what it looks like on a laptop. You want to be able to say let’s do this now. Then I can pull up the different device ranges and the different devices and say, you have a play and tell me what you’re thinking”

All of this was built from an existing Excel workbook using Squirrel using nothing but ordinary spreadsheet logic, with the final result embedded interactively in a PowerPoint presentation with two or three clicks.

If you would like to hear more of Gareth’s story and see his solution in action then the recording of the session is below.

If you want to see what you can do with your spreadsheet knowledge and Squirrel then register for a free account at https://squirrel365.io

Easily create web-based apps for Ninox with Squirrel and no-code

Squirrel provides a highly productive, no-code environment to allow non-technical users to create fully fledged web apps which connect to Ninox databases. The two short videos below show:

1. A full functionality CRM example build in Squirrel on top of a Ninox database
2. A “from-scratch” explanation of how to create a simple read/write Squirrel project on a

If you would like to try Squirrel for yourself, you can sign up for a free account here.