Are you a Python, R, or F# Data Analyst using Snowflake + Excel for rapid prototypes, calculation explanations, or a scratch pad?
Optimal(xl) is an Excel add-in that parallelizes your queries for dazzling speed-ups; generates smartly managed Excel tables from in-cell SQL; transforms Excel ranges into SQL variables; and helps make your workbooks robust and debuggable with no lock‑in.
Copy SQL from Snowflake's Web UI into a cell.
Press {Ctrl + Shift + Q}.
An Excel table will fill in directly below with the results.
You don't need to fully qualify your table names, or fiddle with connection strings or warehouse names. We'll resolve those details for you and add them, as structured metadata, with a cell comment in the same cell as your SQL.
Don't worry about inadvertently bringing in too many rows or columns or hanging Excel. We'll automatically limit your query to the free space available on your sheet, and let you know if we had to truncate the data.
| select dt, mktpnl, alpha, pnl from perf where sym = 'SNOW' and dt between '2020-03-01' and '2020-12-31' order by dt | ||
To parameterize your SQL with range references, you have two alternatives:
1. Enter SQL into a cell, as text, and use a $ prefix to reference named ranges or structured references:
select sym, spxt_6m from betas
where sym in $Table1[tickers]
Optimal(xl) will resolve and SQL-format $-prefixed range variables. If your SQL statements are parameterized with the results of other queries, we'll ensure that the queries are executed in the proper order of precedence.
2. Use plain old formulas like this:
="select sym, spxt_6m from betas
where sym in ('" & TEXTJOIN("','",
TRUE,UNIQUE(Table1[tickers])) & "')"
Unlike Power Query and VBA, optimal(xl) can run your Excel-based queries in parallel across all the CPUs in your Snowflake warehouse.
If you're accustomed to Excel queries running one at a time, the speed-up from simultaneous executions will knock your socks off.
And if your in-cell SQL is parameterized with results from other queries, optimal(xl) builds a directed acyclic graph just like Excel's formula calculation chain and topologically sorts and parallelizes your queries into the optimal execution plan for leveraging Snowflake CPUs.
A side benefit of this parallelization is that you can simplify on‑sheet formulas by pushing more logic into many small queries.
For example, where in Pandas or dplyr you might apply a groupby + map operation to a single SQL resultset, in optimal(xl) one query can return the set of groupby values and many dependent queries can then return resultsets for each group.
Optimal(xl) was designed to be an Excel-based complement to Snowflake's Web UI.
Copy parameterized SQL back and forth between the Web UI and Excel cells without editing a thing.
Simply match the session variables used to parameterize your Web UI queries to Excel named ranges or structured references.
That lets you use Snowflake's SQL editor rather than writing & editing within the Excel cell itself.
For example, suppose you have this snippet in Snowflake's Web UI:
set (sym, qtr0) = ('MSFT', '2010Q1')
select qtr, tot_return from perf_q
where sym = $sym and qtr >= $qtr0
order by qtrYou can copy this 'select' statement directly into an Excel cell, verbatim, simply by setting Excel named ranges that correspond to the SQL variables 'sym' and 'qtr0' and entering 'MSFT' and '2010Q1' into those cells.
| MSFT | 2010Q1 | |
| select qtr, tot_return from perf_q where sym = $sym and qtr >= $qtr0 order by qtr | ||
Keep your in-cell SQL short and sweet — you don't need to fully qualify table names or select a default database. Because Snowflake provides elaborate metadata for your warehouses and tables, we can usually infer details like warehouse, database and schema straight from your SQL. We'll add that information to a cell comment in the same cell as your SQL.
And because we have our own client-side SQL parser, we know that when you change
select sales, ...
to
select sum(sales), ...
you're still dealing with the same column. If you made this change in Power Query, all your formulas with 'sales' structured references would #REF!.
There's no need to download the Snowflake ODBC driver or set up a Data Source Name (DSN). No dependencies to install; no local admin required; it doesn't matter if your Excel is 32-bit or 64-bit.
It's just an xlam file.
optimalxl.xlamOpen optimalxl.xlam, use the optimal(xl) menu to add your Snowflake credentials to encrypted environment variables, and you are good-to-go. (With the Enterprise plan Snowflake credentials are handled automatically.)
If you work in a locked-down corporate environment that blocks accessing xlam files from the web and email, we'll give you simple instructions for building optimalxl.xlam yourself, from scratch, in less than thirty seconds.
Data Analysts, especially those who are working on experiments or ad hoc analyses, sometimes prefer to import source data into Snowflake as TEXT or VARIANT columns rather than deploying validation and type conversion code to their firm's ETL pipeline.
Optimal(xl) helps by parsing textual date/time, number, percent and currency columns and landing them on-sheet formula-ready.
Unlike Excel's notoriously aggressive auto-formatter that mangles your data and makes nonsensical conversions, optimal(xl) uses a hyper-conservative, 'no surprises' approach. It checks all the data in your column to ensure that a uniform and sensible text-to-type conversion exists.
It won't turn sept1 or 8‑9 into dates, lose leading zeros, truncate large integers into scientific notation nor misapply the decimal separator from Excel's regional settings. Optimal(xl) will also prevent Excel from applying any of its own infamous data conversions to your textual columns.
By recognizing more than 12000 date/time patterns, 40 ISO currency codes & symbols, and many number-style permutations, optimal(xl) lets you spend more time on analysis and less on data cleaning.
Power Query is great for automating data cleaning steps and for building no-code query logic that may be difficult for you to express in plain SQL.
Optimal(xl) is centered around in-cell SQL statements and speed; it simplifies building parameterized in-cell SQL and pulls the data for those queries into Excel much faster than Power Query can.
If you are coming to Excel with SQL in hand, and do your data cleaning / ETL with a different tool, optimal(xl) was built for you.
Because optimal(xl) runs your Excel queries in parallel across all the CPUs in your Snowflake warehouse, and Power Query runs them one at a time, it's no surprise that data refreshes with optimal(xl) are astonishingly faster.
The more queries you have, the greater the difference between how fast optimal(xl) refreshes your data versus Power Query.
One customer reduced their workbook's 'refresh-all' time, for 45 Snowflake queries, from one minute to less than 2 seconds (both timed with the same active warehouse).
What if you only want to refresh a single query, repeatedly, and therefore can't benefit from optimal(xl)'s massive concurrency advantage? We still blow Power Query away.
With smart connection pooling and extensive profiling & tuning of the code that parses Snowflake response bytes and formats & writes them to Excel ranges, our in-cell SQL edit & run feedback loop is as fast as the Snowflake Web UI itself.
With optimal(xl) there are no menus nor slow-loading, multi-step wizard forms to edit your SQL. Simply modify the in-cell text or formula, press {Ctrl + Shift + Q} and your Excel table is refreshed.
If you've tried to parameterize Power Query SQL with cell references (or the results of precedent queries), you've likely spent a lot of time on StackOverflow.
With optimal(xl), there's nothing to learn. You can either build your in-cell SQL with familiar string concatenation formulas that reference input cells or simply copy SQL which references session variables directly from the Snowflake Web UI — and then match those session variables to Excel named ranges.
We know what it's like to share a Snowflake-connected workbook with a teammate and then spend half a day troubleshooting when it doesn't work on their machine.
That's why we've designed optimal(xl) to produce workbooks that have fewer dependencies than even Excel's own built-in tools like Power Query or VBA.
For example, with Power Query or VBA, for a teammate to refresh your workbook's Snowflake data they'll have to: 1) download and install Snowflake's ODBC driver; and 2) set up a DSN (Data Source Name) which requires manually entering eight fields of connection information (user name, password, server, warehouse, etc...). Snowflake has a six minute video detailing these steps.
Every time you change your Excel model to use a different (or additional) warehouse, or default database, your teammates have to repeat step #2.
With optimal(xl), you can simply give your teammate a copy of your optimalxl.xlam file. That's it. Because of our no lock‑in pledge, they do not need an optimal(xl) subscription to use your Excel model.
If your teammate is on our Enterprise plan, Snowflake credentials are handled automatically. If they're on the Pro plan (or no plan), the first time they refresh Snowflake data they'll be prompted for their Snowflake username and password — other than that, the data refresh will just work.
If necessary, there's even an easy workaround for teammates without Snowflake credentials to refresh the data.
And if you change your model's default database — or add a query that uses a different warehouse — your teammates can open the new version of your workbook and run the queries without any re-configuration on their end.
Like Power Query, optimal(xl) lands your data on-sheet as an Excel Table so you can use auto-sizing structured references in formulas that refer to columns in the table:
=SUM(Table1[pnl])
Unlike Power Query, optimal(xl) carefully manages your table to ensure that changes to your SQL don't unnecessarily disrupt those formulas or allow them to silently reference the wrong data.
For example, suppose you have this in-cell SQL in an Excel model, select alpha, ..., and decide to give the alpha column a more descriptive alias: select alpha as alpha_vs_spxt, ...
With Power Query, formulas in your model that referenced the alpha column will break and become #REF!'s.
With optimal(xl) they'll correctly re-reference to the alpha_vs_spxt column.
Likewise, if you change select pnl, ... to select pnl/1e6, ... or add aggregation, select sum(pnl), ..., Power Query will #REF! your formulas. Optimal(xl) won't — it's smarter than that.
Some SQL changes should #REF! your formulas. Suppose you change select pnl/aum, ... to select mktval/aum, .... With Power Query, existing pnl/aum formulas won't error out — they'll now point to mktval/aum data. That's dangerous. Optimal(xl) recognizes this and will #REF! references to the old pnl/aum column to prevent silent errors from creeping into your model.
And, if you add a column to your SQL and are not careful to use column aliases, e.g., if you change select pnl/aum, ... to select mktval/aum, pnl/aum, ..., Power Query will direct existing pnl/aum formulas to the new mktval/aum column.
Optimal(xl) is safer than that. It will ensure that formulas referring to the pnl/aum column are preserved.
If you've used Power Query you've likely encountered the dreaded Formula.Firewall error or incessant data privacy warnings — and spent time trying to permanently disable them.
If you're like some of our customers, you made it all stop by turning to VBA and embedding your plaintext Snowflake credentials therein. 😱
We defer to Snowflake's permissioning system to mediate your access to data. If your queries run in Snowflake's Web UI, they'll run in Excel with optimal(xl).
With our Enterprise plan your Snowflake credentials automatically resolve from your Windows login. With our Pro plan (or no plan) your Snowflake user name and password are kept as encrypted environment variables on your local machine.
We make it easy to secure your Snowflake credentials — other than that we stay out of your way.
If you use large or zero-padded integers for IDs in your Snowflake tables, Power Query will mangle your data when it brings it into Excel.
For example, if you have a large integer ID in Snowflake like this:
| 123456789123456789 |
Power Query will bring that ID into Excel as this:
| 123456789123456000 |
and will display the value in scientific notation:
| 1.23E+17 |
so you may not even realize that the data has been mangled. (This happens because Excel can store only 15 significant digits in a number.)
Yes, you can use the Power Query UI to designate this column as 'Text' and prevent the conversion when the data first lands on-sheet. But this sets a booby-trap. If you or a teammate inadvertently double-clicks a cell with a text-formatted large integer ID, or if you put the cell into edit mode with the {F2} key and then exit with the {Enter} key, your ID will become mangled despite Power Query treating it as text.
Geneticists know this problem well. When they use Power Query to query a column that contains gene abbreviations for 'septin 1' or 'membrane associated ring-CH-type finger 1':
| SEPT1 |
| MARCH1 |
Simply double-clicking the cell will destroy the data by auto-converting it to a date:
| 1-Sep |
| 1-Mar |
This will disrupt any lookup formulas, COUNTIF's or pivot table groupings that refer to the column of gene IDs. This problem became so acute that in 2020 geneticists gave these two genes new abbreviations, SEPTIN1 & MARCHF1, to prevent Excel from auto-converting them to dates.
With optimal(xl), 'SEPT1' and 'MARCH1' will not auto-convert to dates. Even after a data refresh, double-clicking the cell will still not cause a conversion. Our Snowflake data processor detects when Excel will apply an auto-conversion and prevents it by prefixing the datum with Excel's special text-designation character, the mighty apostrophe.
And because the apostrophe prefix is ignored by formulas, you don't have to think about it. Formulas like =COUNTIF(A1:A95,"SEPT1") will work just as you expect — you don't need to add an apostrophe prefix to the SEPT1 constraint.
How good is optimal(xl) at preventing Excel's data mangling? It's perfect. Because we roundtrip Snowflake data through Excel's own data rendering engine before landing it on-sheet, we always know exactly what Excel will try to do to your data.
With Power Query, if you receive an error while refreshing data there are many troubleshooting rocks to look under: your Data Source Name configuration; your privacy settings; the Power Query cache (try clearing it); the query may be incompatible with your current version of Excel; an M code error; a SQL syntax error.
With optimal(xl), simply inspect the cell that has your SQL. A comment in that same cell will hold all the connection metadata — except username & password — needed for executing your SQL via Snowflake's REST API.
If you have a question about your query, or need to resolve an error, the answer will be found within that single cell.
Optimal(xl) was created in response to difficulties we observed Data Analysts having with other Excel-based software. We frequently heard users of tools like PyXLL, xlwings, VSTO, COM Add-ins, etc., lament:
Centered around a novel tech stack, optimal(xl) uses best-in-class Cloudflare Workers serverless deployment architecture; an F# parallelizable directed acyclic graph for executing in-cell queries and their precedents; native calls to the Excel C API to avoid fragile and slow COM Interop — all orchestrated with will-work-till-the-end-of-time VBA glue code.
open FSharp.NativeInterop
/// from here: https://github.com/fsharp/fsharp/blob/master/src/fsharp/FSharp.Core/prim-types.fs
let inline retype<'T,'U> (x: 'T) : 'U = (# "" x: 'U #)
let inline stackalloc<'a when 'a: unmanaged> (length: int): Span<'a> =
let p = NativePtr.stackalloc<'a> length |> NativePtr.toVoidPtr
Span<'a>(p, length)
[<RequireQualifiedAccess>]
module private UoM =
type [<Measure>] RowIndex
type [<Measure>] ColIndex
type RowIndex = int<UoM.RowIndex>
type ColIndex = int<UoM.ColIndex>
module RowIndex =
let inline create (i: int) =
if i < 0 then invalidArg (nameof i) "Row subscript must be >= zero"
LanguagePrimitives.Int32WithMeasure<UoM.RowIndex> i
The top data and analytics firms in the world provide remarkably lazy Excel add-ins.
They think, "Our customers who really need performance will use a different API anyway, not our Excel interface."
We understand that the difference between a two-second data refresh and a twenty-second one is the difference between a workbook that is usefully interactive and one that is not.
That's why we didn't stop at simply parallelizing your queries. We've spent hundreds of hours profiling and tuning our code to minimize memory allocations, CPU cache misses and branch mispredictions when preparing Snowflake calls and formatting resultsets for Excel.
We built optimal(xl) from the ground up to auto-update, on your say-so, without needing an Excel restart.
It gets better as you use it — but we're careful not to make UI changes that force you to relearn what you already know.
And there's no need to go get a coffee. We push updates with Cloudflare's high-performance global network which is milliseconds away from virtually every Internet user.
Our core technology is battle-hardened by years of use in the trenches of hedge funds and exemplifies the hard-earned best practices developed by an Excel software group at Goldman Sachs (of which our co-founder was a member).
This is not your average, pain-in-the-neck Excel add-in from your market data or risk software vendor. Fortified by thousands of unit tests and millions of property-based tests across dozens of Snowflake-connected Excel models and four Excel versions, optimal(xl) uses a starkly different approach to Excel programming than most software companies.
Optimal(xl) is not like many Excel add-ins that degrade Excel's overall performance and usability.
These misbehaving add-ins often use hacks to circumvent Excel's limitations and execute code in the background, greatly impairing Excel's baseline snappiness.
Optimal(xl) does none of that. When you are not explicitly using optimal(xl)'s features, our software is doing nothing at all.
| Things we don't do |
|---|
| No custom worksheet functions |
| No reacting to application-level events |
| No code executed on calculations |
| No code executed when selected cell changes |
| No perpetual OnTime calls |
| No heartbeat pings |
| No background logging |
| No file watching |
'Copyright (c) 2024 optimal(xl) (www.optimalxl.com; [email protected]) {v=2024.12.16.0; a=c3b36f93; u=e3b1e0aac539} |
To ensure transparency, trust, and security, the optimal(xl) add-in code is not protected, hidden or obscured.
In fact, if you work in an environment where you can't access Excel add-in files from the web and email, we'll show you how to copy VBA code from our website and build optimalxl.xlam yourself, starting from a new workbook, in less than thirty seconds.
With optimal(xl), you won't have to format the same market_value_usd column as dollar millions every time it lands on‑sheet.
1000000 → USD 1.0
Simply format a particular Snowflake column once, in one workbook, and thereafter we'll remember and apply the format for you whenever you query that column.
For example, if you have a Snowflake column, fx_mult_to_usd, that you format in Excel with four decimal places, 0.8130, optimal(xl) will remember this format and apply it whenever you query that same column in any model. This works for conditional formatting as well.
Optimal(xl) can 'see through' column aliases. If you have previously formatted a beta_vs_sx5t_6m column, when you query:
select max(beta_vs_sx5t_6m) as beta ...
your column of Excel data will have the same number format.
With our Enterprise plan you can prescribe firm-wide formatting conventions by encoding custom Excel formats into Snowflake column comments:
<xlformat>#,##0.00_);[Red](#,##0.00)</xlformat>
Snowflake can represent a larger range of numbers and dates than Excel can, so what happens when you have Snowflake values that can't be represented in Excel?
This question becomes especially relevant if your ETL systems use 64-bit integers as IDs or eschew nulls in favor of special values like 'NaN', 'inf' or 0001-01-01.
Rather than a one-size-fits-all approach, optimal(xl) implements custom logic for every Snowflake data type to protect you from surprises and silent conversions while also making your on-sheet data as ready-to-use as possible.
For example, when optimal(xl) encounters a 64-bit integer that is too large to losslessly represent in Excel, it will render the entire column as apostrophe-prefixed text so there are never strings nor truncated values lurking within your numbers or IDs.
Likewise, optimal(xl) holds the integer part of Snowflake FLOAT values to be sacrosanct and won't allow it to be rounded or truncated. But it will tolerate a loss of precision in the decimal part so that Snowflake data with more decimals than Excel can handle still land on-sheet as numbers.
Special Snowflake FLOAT values are rendered as familiar Excel errors so that your formulas won't inadvertently ignore them like they might if they were represented as strings. E.g.,
'NaN' → #NUM!
'±inf' → #DIV/0!
And FLOAT values beyond Excel's numerical limits, ±9.99999999999999E+307, hit your sheet as #VALUE! errors to prevent their presence from being overlooked.
It's easy to customize the default behavior by-query, by-workbook or globally via the optimal(xl) menu:
≡optimal(xl)∟Settings∟Data type defaults
For example, if you have a model where formulas referencing Snowflake FLOAT data require that 'NaN' values hit the sheet as blank cells, this rule can be added — as structured metadata — to the in-cell comment that accompanies your in-cell SQL. That way, teammates who use your model will get the correct behavior regardless of their own global settings.
Read about DATETIME defaults here.
Of all the Excel horror stories you've heard, London Whale, Reinhart and Rogoff, etc, you've never heard one like this: "The Value-at-Risk cell was a #REF! but we did the trade anyway."
Optimal(xl) is designed to help eliminate silent errors and produce noisy errors. For example, change your query from
select operating_income, ...
to
select ebitda, ...
and formulas referencing the removed operating_income column will error out.
| Operating Income | #REF! |
Sometimes a #REF! is a good thing.
Snowflake lets you add explanatory comments to any object, including individual columns. Optimal(xl) takes advantage of this by adding Snowflake column comments as in-cell comments to your on-sheet column headers.
For example, if you have a Snowflake column, alpha_vs_peers_30d, the in-cell comment pulled from Snowflake could elaborate with:
This alpha calc covers the last 30 trading days and uses 180-trading-day betas with a 0.5 floor and 1.3 ceiling. The benchmark basket of peers uses total returns and excludes the symbol for which alpha is being calculated. To see the basket of peers used for each symbol, go to http://intranet:90/perf/alpha/peers
Many of our customers find this feature helpful for both their teammates and future selves. It can be especially useful for describing data lineage and preventing perpetual incoming "Where does this number come from?" emails. It can also save you from having to contemplate long — and often still ambiguous — column aliases.
For calculated columns that have multiple input columns, e.g.,
select (ev_est / ebitda_est) as ev_to_ebitda_est, ...
optimal(xl) will list the column comments for all the inputs.
Data Analysts who use Excel as a secondary tool often like to illustrate computations — after all, the computations themselves are just as easy to perform with primary tools like Pandas or dplyr.
A simple way to make computations self-describing is to embed on-sheet formulas in your Snowflake SQL.
For example, the on-sheet data table generated from this SQL:
select sym, dt, pnl_px_bod,
pnl_buys, pnl_sells,
SUM(pnl_px_bod, pnl_buys, pnl_sells)
as pnl_total ...
select sym, dt, pnl_px_bod, pnl_buys, pnl_sells,
SUM(pnl_px_bod, pnl_buys, pnl_sells)
as pnl_total ...
select sym, dt, pnl_px_bod, pnl_buys, pnl_sells,
SUM(pnl_px_bod, pnl_buys, pnl_sells) as pnl_total ...
may cause your model's users to question what subcategories of pnl the pnl_total field includes.
In contrast, by using an on-sheet SUM formula with structured references like this:
select sym, dt, pnl_px_bod,
pnl_buys, pnl_sells,
'=SUM([@pnl_px_bod],[@pnl_buys],
[@pnl_sells])'
as pnl_total ...
select sym, dt, pnl_px_bod, pnl_buys, pnl_sells,
'=SUM([@pnl_px_bod],[@pnl_buys],[@pnl_sells])'
as pnl_total ...
select sym, dt, pnl_px_bod, pnl_buys, pnl_sells,
'=SUM([@pnl_px_bod],[@pnl_buys],[@pnl_sells])' as pnl_total ...
the pnl_total column will explain itself. And you can signal this to teammates by using your optimal(xl) settings to give columns with SQL-embedded formulas special formatting.
If you don't like structured references you can use RC-style formulas like this:
select px, qty,
'=RC[-1]*RC[-2]' as market_value,
'=RC[-1]/Sheet1!aum' as '% aum'...
But don't try this with Power Query. It won't work.
Because optimal(xl)'s underlying technology emerged from a hedge fund where billions of dollars of trading decisions flowed through its source code, there's an emphasis on defensive patterns and safety.
For example, Snowflake query refreshes start by clearing existing data and adding a single row of #NA's to all target ranges. This prevents a common issue when pulling data into Excel where query errors or incautious VBA code can cause some parts of your model to be updated while others are not.
Data Analysts can develop Excel models on smaller Snowflake warehouses and scale up when deploying to portfolio managers or the trading desk.
See massive performance and usability gains with zero changes to the Excel workbook itself.
With a mid-size warehouse, optimal(xl) can execute 128 queries at the same time. Are many small queries an anti-pattern? Not with optimal(xl) + Snowflake.
| Snowflake warehouse size | # of Excel queries run concurrently |
|---|---|
| X-Small | 8 |
| Small | 16 |
| Medium | 32 |
| Large | 64 |
| X-Large | 128 |
| 2X-Large | 256 |
| 3X-Large | 512 |
| 4X-Large | 1024 |
| 5X-Large | 2048 |
| 6X-Large | 4096 |
| These specs are approximate and may vary slightly depending on your platform choice (AWS, Azure, GCP). | |
As a Data Analyst, there's no better way to answer "This doesn't look right" than with an Excel workbook.
For data teams debugging ETL processes or explaining data lineage or computations to downstream users, an elaboration in Excel is worth a thousand explanatory emails.
Use Excel to provide a high degree of visibility into frequently-questioned aspects of your analytic systems.
With:
For data exploration, proof of concepts, or high-impact, fast-turnaround models, Excel lets you understand the dependency structure that is implied by computations. Thirty-nine years after its birth, it remains a great general-purpose fabric for idea expression and computation.
I personally believe that Visual Basic did more for programming than Object-Oriented Languages did.
— Linus Torvalds, creator of Linux & Git
Why would anyone, in 2024, use VBA in the first place?
Yes, the VBA language and IDE are comically obsolete, but it remains the fastest, simplest, most dependency-free and debuggable way to automate Excel.
We often see Data Analysts who are using Excel as a secondary tool start with Power Query and then turn to VBA for the following reasons:
We also see Data Analysts come to VBA after trying xll-based or COM Interop-based tools and realizing they made a deal with the devil: in exchange for using a modern language and IDE, they gave up debuggability, deployability and performance.
With optimal(xl), you'll get faster, parallelizable queries and simple SQL parameterization without leaving the Excel sheet.
And when you need the power and flexibility that comes with full programmatic control over your model, optimal(xl) has a simple, workbook-embedded VBA API that lets you lightly edit generated VBA code rather than starting from scratch.
The result: embrace powerful, time-saving scripting while also making a quicker escape back to PyCharm/RStudio.
Public Sub RefreshData() |
More API info:
You can use optimal(xl)'s menu to embed the VBA API into your workbook:
This adds several plain old VBA modules and classes to your workbook as well as sample code for common use cases like running a specific subset of your queries.
This workbook-embedded VBA code gives you programmatic access to all optimal(xl)'s advantages, i.e., parallel query execution; safely handled Snowflake credentials; removing your model's dependency on both Snowflake's ODBC driver and local machine-based Data Source Name (DSN) configurations.
It has no fragile type-library references, no compile-time dependencies, and no custom worksheet functions. That means your teammates can always open workbooks containing the API without having VBA references blow up or having on-sheet formulas resolve to errors.
And just like the optimal(xl) add-in itself, our no lock‑in pledge means models that use the VBA API will work in perpetuity, for subscribers and non-subscribers alike, so long as you have optimalxl.xlam open and/or installed as an add-in.
Optimal(xl) has a simple solution for keeping customer data secure: we don't hold any in the first place.
Your Snowflake credentials are stored locally as encrypted environment variables (or placed behind Windows Authentication or Okta with Enterprise plans); your optimalxl_settings.xml file, which holds your preferences and favorited queries, can be stored in your own GitHub repo or Snowflake table — if you use multiple computers, we'll sync your settings from these locations.
Logs generated by our software are stored on your own machine and don't leave your machine — unless we need the logs for troubleshooting, in which case we have to ask you to send them to us. (They are generated into an Excel sheet so you can see exactly what you are sending.)
If we want to know how you're using our software, we'll call and ask you.
Often Data Analysts who are trialing Snowflake or building proof of concepts want to share a single Snowflake account so they can give Snowflake-connected workbooks to colleagues without setting up Snowflake credentials for those users.
We've seen CTOs palpitate when they realized Data Analysts were accomplishing this by embedding plaintext credentials within VBA.
Optimal(xl) gives you a safer way to share Snowflake credentials among your team by leveraging already-existing mechanisms such as network file share access or PostgreSQL/SqlServer access.
Send snowflake_demo.xlsx to your team and let them experience zero-friction data refreshes; and if the model is inadvertently emailed externally, your Snowflake credentials remain safe.
We have immense gratitude for our customers' investment in our technology.
That's why we've taken the standard SaaS playbook:
How do you build your product to retain users? Create accruing benefits and mounting losses as a user engages.
and designed our software to only do the first part.
We don't hold your Excel models hostage to a ongoing subscription. Cancel at any time and you'll still be able to refresh your workbook's Snowflake data. Give your model to a teammate — plus your copy of optimalxl.xlam — and they'll be able to refresh the data as well.
If optimal(xl) doesn't detect an active subscription, it will fall back to Power Query-like, one-at-a-time, queries (but much faster). In other words, without an active subscription your queries will still run, just not in parallel across all the CPUs in your Snowflake warehouse.
This is also true for models you build using the free trial. They will continue to work even if you don't continue on to a monthly or annual subscription.
If you have teammates who want to view your model but don't need to refresh Snowflake data themselves, they don't even need optimalxl.xlam; just give them your workbook. We don't add any dependencies, nor links, nor custom worksheet functions — optimal(xl) is carefully crafted to operate on your workbooks, not become enmeshed in them.
What if we change our minds, or sell our company to a private equity firm that reneges on this pledge? Because optimal(xl) is not SaaS — it's old-fashioned desktop software — 100% of its code runs on your own machine. By simply adding the directory containing optimalxl.xlam to source control or a scheduled backup, you'll preserve a copy of our software which will work in perpetuity even if future product updates reverse our 'no lock-in' design. You can also disable product updates altogether or refuse them when notified of a pending update.
Lateral Thinking with Withered Technology — Gunpei Yokoi, designer of Nintendo's Game Boy
Co-founded by two brothers, one a Goldman Sachs veteran who went on to build software for the legendary Market Wizards trader Bruce Kovner and Tiger Global; the other a former professional hockey player and software engineer for Silicon Valley tech standout Pinterest, we build fast, intuitive & no‑lock‑in Excel tools for Data Analysts.
per user, per month, USD (save 20% by paying annually)
Pro:$95/mo
Need to refresh twenty queries faster than Power Query can refresh one? Pro is for you. Our free 30-day trial includes our no lock‑in pledge: Excel models built with the free trial will continue to work even if you don't continue on to a paid plan.
Enterprise:$495/mo
Snowflake credentials are placed behind Windows Authentication or Okta; streamlined deployment for teams; tagged queries to assess how specific Excel models utilize Snowflake resources; faster support; and more...