Announcing Tiller's 2022 Microsoft Excel Builders Challenge

[randy]

Originally published at: https://www.tillerhq.com/announcing-tillers-2022-microsoft-excel-builders-challenge/

Tiller is excited to announce our first-ever Microsoft Excel Builders Challenge. We begin accepting your amazing workflows, visualizations, templates, and helpers starting… right… now!

The Excel Builders Challenge aims to inspire the creation of new spreadsheet-based solutions that empower people with greater understanding, confidence, and control of their financial lives. The challenge also showcases the creativity and skills of Excel spreadsheet enthusiasts across the United States.

Prizes include merchandise and swag, annual Tiller subscriptions, gift cards, and cash rewards.

About the 2022 Microsoft Excel Builders Challenge

If you remember Tiller’s previous Builders Challenges, this one is a bit different. Rather than accepting entries and reviewing them at the end of a submission period, we will now award prizes within 5 business days of builder submissions.

Prize level will be determined based on the quality, ambition, and value of your solution.

Simplified Schedule Kicks off Today

The Excel Builders Challenge kicks off today, and we are now accepting submissions. Submissions are open until October 1, 2022.

The sooner you share your Tiller-powered Excel creation, the sooner you can claim your prize. We can’t wait to see what you build! Prize levels, awarded at Tiller’s discretion, include:

• Tiller t-shirt
• $25 gift card
• 1-year Tiller subscription extension
• $100 gift card
• $500
• $1,000

Judging Criteria

Prizes will be awarded by Tiller’s judging panel based on:

• Value of concept to the personal-finance community
• Value & quality of Tiller integration
• Uniqueness of concept vision
• User experience & usability

Examples of Compelling Solutions

Solutions could include a new worksheet that can be inserted into the Tiller Foundation Template, a workflow that uses the Foundation Template to accomplish something valuable, or a stand-alone workbook that leverages our feeds. Solutions should:

• Empower people with greater understanding and control of their financial situation.
• Solve specific personal finance riddles
• Showcase what’s possible in a spreadsheet (specifically Excel for this challenge)
• Function reliably & performantly with clean design and formulas
• Include helpful documentation (video is a plus!)

Looking for inspiration? Check out what’s been built already with Tiller and Google Sheets. The Show and Tell here in the Community has workflows and solutions shared by novices and experts, and the Solutions Gallery here has best-in-class examples of highly polished Community Solutions.

Requirements for Prize Consideration

• Your solution must enhance the value Tiller’s transaction and/or balance data feeds
• Your solution must be built for Microsoft Excel
• Your solution must be fully compatible with Tiller’s worksheet & column conventions, including the Excel tables in the Transactions, Categories and Balance History sheets (download the latest version of the Tiller Foundation template to see how these tables are implemented)
• Solution must be posted as a new topic in the Tiller Community’s Excel / Show & Tell category with the tag “builder-challenge” between 4/25/22 and 6/6/22
• Your solution must identify and provide a workflow, solution or visualization to a common personal finance challenge. The topic must contain basic descriptive and instructional content for a user to understand the purpose of, be able to install, and use your solution.
• Your solution must use Excel’s structured references to core data columns so that it can be installed in other users’ sheets and properly reference essential data.
• Your solution cannot use scripts
• Your solution does not integrate or use 3rd-party apps
• You, the builder, are based in the U.S. and over the age of 18
• Though you, the builder, will maintain ownership, copyright, and any other kinds of rights to the content, you grant Tiller an unlimited, non-exclusive license to use the resulting content, and/or portions of the content or derivatives of the content, in any way and for any purpose as Tiller solely determines
• Only the first submission per contributor will be considered for prizes. If you’d like to submit additional entries for prize consideration, DM @randy in the Tiller Community.

Reply below if you have questions.

Award-Winning User Submissions

• Transaction Tracker by @yossiea - $100 award
• Waterfall Chart by @jpfieber - $200 award
• Add Manual Transaction workflow by @mr_signal - $200 award
• Account Reconciliation by @jpfieber - $100 award
• Budget Plan by @jpfieber - $1,000 award

[GregSweats]

Hey @randy , will data modelling in Power Query count as scripting? It’s built-in to Excel 2016+, and available as an add-on apparently for 2010 Professional Plus (my stomach churned at the old “Pro”, “Pro Plus”, “Home”, … MS Product Naming schemes, MS improving that, “Windows 10” and “Windows 10 Pro” is perfect ).

*Source: www (dot) howtoexcel (dot) org how-to-install-power-query

I made a proof-of-concept PowerBI Connector for Moodle, to analyze my son’s Grade’s in near real-time, with far more info available than the school login website shows. Really wrapped my head around Power Query.

This morning, after starting a competition entry, in hopes of winning or impressing the Tiller Team, (yes, all ~16 of you, according to LinkedIn), I stumbled on a familiar looking Power Query editor window in Excel by accident. I believe I was messing with Named Ranges. Anyways, BLEW MY MIND

I am working on a Data Importer, and future Exporter (pre-defined CSV formats, Quicken QIF, Quickbooks QFX/OFX, Moneydance, Moneyspire, ledger-cli ( of data sovereignty in my mind), etc…).

Biggest thing, that actually finally led me to Tiller, is that www.EQBank.ca DOES NOT OFFER AN OFX DOWNLOAD, or a standard CSV. Their date format is ‘02 FEB 22’ or something like that. Quicken won’t import, Quickbooks won’t, Moneydance, Moneyspire, but I think PocketSmith might have (my pick after Tiller at the moment).

Anywho, ummm, man. I have been at this all day, plus finished wall trim in my garage

Right… so that led me to reconstructing the Tiller Transactions Worksheet into a a Power Query Table, along with an ImportSource sheet/table, and a column mapping sheet/table/query that I am still working out issues with, but still a week to go.

You can see my entries #WIP repo at

github
/gsteve3/tiller-challenge-2022

There is some sensitive account info in there that I will scrub, but it’s a semi-dummy account so no biggie.

Anyways, my mind was blown again when I realized there was no need for an IMPORT button, since the mappings were all being done on the fly in Excel Power Query transformations, merges, Table.GroupBy (to de-dupe entries), etc…

Am I onto something here?

Before this challenge, I found Tiller, in my ~3 weeks of using it now, limiting compared to the several other options I have reviewed, some listed in my previous sentences, is ANALYTICS and a MOBILE APP.

I was going to use Microsoft Power BI to create some beautiful, mobile friendly (portrait and landscape custom), themeable, Power BI Dashboards that could be live refreshed thanks to Microsoft 365 and OneDrive.

Wow, okay, anyway…

TLDR;

Can Excel’s Power Query be used as part of this challenge? Is it considered scripting? Is it good? Can I haz job yet?

[randy]

Hey Greg! I love how you are viewing these platforms as toolboxes and putting them to work for you. That is the superpower of working out of a spreadsheet ecosystem.

Some bullet thoughts…

• Wasn’t familiar with ledger-cli. First thought: cool! Second thought: of course something like that exists…
• Power Query 1/2. We are still getting our heads around this tool. It looks amazing… but most of our team are on Macs and it seems to be only partly supported; I believe pre-built queries will execute on a Mac but there is no supported editor to modify them (correct me if I’m wrong…).
• Power Query 2/2. As for using it in the contest generally, it’s fine. I don’t love that the support isn’t fully cross-platform, but it certainly doesn’t present the complicating security and maintenance issues that scripts do. So… go for it.
• I’m not sure what you mean specifically by “analytics”. We have quite a few Google Sheets templates under the “analysis” umbrella. There is room to port and improve those concepts for Excel. But perhaps you mean something else?
• Mobile-friendly solutions that don’t require scripts or 3rd-party tools are great.
• We’re excited to encourage and support Excel builders, so the contest deadline will likely push out. Look for an official announcement in that respect soon-ish.

Hope this helps. Let me know if I didn’t answer your questions.
We are excited by your enthusiasm.
Randy

[yossiea]

One other area in Excel to look at that is in the pipeline (it’s currently out in the beta channel) is the use of Lambda function and the ability to add custom formulas, etc. This IMO would be a way to showcase what Tiller can do.

[GregSweats]

Hey @yossiea , I was not familiar with Lambda in Excel, cool!

I am so Power Query focused right now, I may be off base here, but have you seen what Custom Power Query Functions can do?

I just used a custom function to create a Natural Key, akine to a slug in my website world/anyone who’s used WordPress. Transaction.slug (may rename to transactionKey) is composed of the Transaction’s Date, Amount, Description. Probably should include account, and whatever else, but either way, it guarantees that when all special characters are stripped out, and those keys are combined together, to form a unique transactionKey (I will rename it, nicer for docs).

Having that naturally formed (all organic?) transactionKey means any transaction, from any source, has a unique identifier, not tied to the CSV it was imported from, or bank downloaded from, or a random GUID, or incremental number. Nope. Unique key generated by the data, to avoid duplication.

I feel like a marketer pushing my links all over these forums, sorry, but if you checkout the main .xlsx (NOT the macro one, need to delete that), you can go to Data → Queries & Connections → Right Click on createSlug then click Edit. Then click on Advanced Editor in the Home Tab of the Top Ribbon. The code for that is at the end of this post.

Rest of this gets gross cody, but does something like I outline below give you the functionality you are hoping to see with Lambda?

You can download and look at all the code in Tiller-Foundation-Template.xlsx (.xlsx ! Not the one ending with xlsm) from GitHub - gsteve3/tiller-challenge-2022: Entry for Tiller’s 2022 MS Excel Challenge; Hopefully better than resume for Tiller Sr. Engineering Job 😱

The code for adding and setting the custom slug (soon to be transactionKey) is within ImportSourceNormalized:

// near the end of ImportSourceNormalized
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "slug", each createSlug([Date], [Amount], [Description]))

Then the code is also used here, to cover-my-butt in case somehow transactions get into the table without a key. That happens in TransactionsAfterImportDistinctSlug (names all change/refine quickly, overly verbose on purpose initially):

// Replace NULL transactionKeys (slugs) with values from `createSlug(...)` function
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"slug", "originalSlug"}}),
    CreateSlugIfNull = Table.AddColumn(
        #"Renamed Columns",
        "slug",
        each if ([originalSlug] = null)
            then createSlug([Date], [Amount], [Description])
        else
            [originalSlug]
    ),
    #"Removed Columns" = Table.RemoveColumns(CreateSlugIfNull,{"originalSlug"}),

Gross, gross code below, but here is the function:

// function CreateSlug...

let
    CreateSlug = (date, amount, description) => Text.Combine(
        Text.Split(
            Text.Combine({
            Date.ToText(
                date,
                [Format="yyyyMMdd"]
            ),
            Text.Replace(
                Text.Replace(Number.ToText(
                    amount,
                    "F2"
                ), "-", "N"),
                ".",
                "-"
            ),
            Text.Lower(
                Text.Middle(
        //            Text.Remove([Description], "Transfer,from,to, "),
                    Text.Replace(
                        Text.Clean(
                            description
                        ),
                    " ", "-"),
                    0,
                    48
                )
            )
        },
        "-")
    , "-"), 
    "-"
)
in CreateSlug

[GregSweats]

Thanks for replying @randy, glad to hear there’s some excitement! Plus that sharing ledger-cli may lead to something…I really don’t know what…data privacy? File format longevity? But, cool none-the-less! It still suffers from a similar shortcoming of Tiller or any Sheets/Notion style, build-it-yourself solution, in that entering transactions on the fly, or using mobile, is tough. Going to get there though! I am a dog who has found a tasty bone.

I’ll check into those Mac restrictions. I actually have a Mac VM I can fire up to test it with even, might be a day or two though.

I will completely risk losing this competition due to Power Query. Not really in it for the contest as much as the chance to get your attention and hopefully an interview for that Sr. Engineering job

Okay, time to sleep, thank you!

Oh, if I didn’t mention it… I just made some cool diagrams…like this one…

GitHub - gsteve3/tiller-challenge-2022: Entry for Tiller’s 2022 MS Excel...