Excel Add In 2022 (Mr. Signals Playground)

Microsoft Excel Show & Tell
1

Overview

Original Post

Happy Friday!!

So, the TillerHQ staff drop the figurative gauntlet and I like a challenge. Plus, I need this to work and really like their budget worksheet.

I have been finished with this Add In as far as functionality for a couple of days, it still needs to be refined and given the Tiller brand if they like it, but Microsoft has a secret handshake on getting the Add In published to the Add In store. However, there is always a work around.

Things I have learned (nutshell edition):

  • Office Add Ins are nothing but websites that are formatted to fit the Add In specifications. (HTML and CSS).
  • The Excel API is the way to add coding to the Add In so you can interreact with the Excel functionality. (I used NODE JS, ie. Javascript)
  • I also learned Microsoft VS Code to create the shell of the Add In. (Office Add-ins with Visual Studio Code)
  • In the end, you have a website with all the functionality and a manifest file in XML format.

The live Add In we will be discussing is located here: Mr. Signal’s Fun with Tiller Budget (windows.net)

ScreenShot
ScreenShot1366×768 157 KB

  • :camera_flash:

Environment: I am using Windows 11 and Office 365. I have tested in both Chrome and Edge.

Installation

Might be some discussion here as it is not straightforward as installing it from the Microsoft Office store as you installed the Tiller Add In. Unfortunately, this has to be done every time until the Add In is in the Office store.

Temporary Instructions (Office.com)

Click on the Add In and follow the directions.

For the Add In to function correctly, you need to use the TillerHQ foundation budget workbook.

Necessary Pages

  • Transactions sheet for add in the transaction.
  • Balance History sheet for adding in the manual account.

Automated:

The Add In creates a sheet called Mr. Signals calculation sheet you can view this sheet to see if your transaction and account information lined up correctly to your headers. (I believe this is dynamic now but if you have issues let me know.)

Setup

There should be no setup just the adding of the Add In.

Usage

Adding the transaction is more refined then adding the account. Most of the extra stuff like transactionID etc are taken care of automatically.

The Add manual account just has all of the fields as of now, some will be removed and automated.

Permissions

The website can be copied (although I do not know if the web browser will hide any of the javascript in the code)

Notes

I should be able to update the code a lot faster now, but I will increment the version number and I will display the update on the main page)

FAQ

TBD

2

Is the Manual Account used to also update the balance of the account or just to put the account in the first time?

3

I am working on that now.

4

Hello,
My original file includes tag columns in the transactions tab. I added the add-in as described although I had to use the browser version of Excel to see the hexagon Office Add-in choice, was not visible in my desktop Excel app. Either way it loaded and added the Mr Signals tab. My question is should we see two rows of headers in the Mr Signals tab? Row 1 duplicates my Transactions tab up to Check number, excludes Full Description forward. Row 10 is a clone of my balance history tab header.

5

Had to read this twice as I was on my cell phone. In the desktop version, you still go to insert, but it may be a little icon instead of what is shown in the picture:
image

Thank you for letting me know the question, I was a little rushed trying to get this out there so I may not have been clear enough. On the Mr. Signals Tab there are indeed two headers. The first one at A1 and A2 are for adding a transaction. Once you add a transaction, if you look at the Mr. Signals Tab, you should see the transaction details under the correct header. (If not let me know and I will try and tweak the formulas). The second set of headers is for the add an account tab in the Add In. Again you should see the details under the second set of headers at A10 and A11.

The sheet will eventually be hidden, but I want people to have confidence that the data is going from the Add In inputs to the Mr Signals Tab and then being copied to the correct Tabs (Transactions or Balance History) and it will help with troubleshooting the formulas. I am still debating whether to use strictly Javascript and then insert or use the Excel formulas to copy and then insert. Long story short, inserting the values and formulas into the spreadsheet is fairly straightforward to code, but formating the cell is interesting. (Like making it a date, centering cells, etc…) I am working on the next version now which will allow you to update the account balance as well as fix the add a manual account.

Getting the formulas to work is one thing, but then making it look pretty (or have a great UI) in HTML and CSS is another issue. Could you send me your header titles in the Transactions Tab, and I will use that for the next iteration of testing?

6

Oh wow! Nice work…you have motivated me to take a look at writing my own Excel Add-in.

7

Hi

Was looking to add this on today with recent changes/updates to the Tiller excel sheet. Unfortunately, cannot find a way to manually add in your plug in.

Maybe its because I’m using Office on a mac?

8

That is a great question. I do not have a Mac to test on. I would assume office would function the same. Are you using office online or installed?

9

installed, when I go to manage add-ins, it directs me to the website though and only allows me to select from add-ins in the “store” ?

10

I was only able to install the add-in and use it from the on-line (office.com) version of Office. I got the same thing as you when I tried the installed version. I think it was explained that that was because the add in has not been approved by Microsoft?

11

@mr_signal I wanted to play around with this, but I’m unable to get it installed.

When I click on My Add-ins the link in the top right does not have the dropdown option to click “Upload” it just takes me to a web browser that displays add-ins I have installed for this user.

I should note it’s a work account so if yours is a personal M365 account that could be the difference. Perhaps work accounts don’t allow the upload or it’s a setting controlled by my admin.

Screen Shot 2022-09-10 at 10.17.16 AM
Screen Shot 2022-09-10 at 10.17.16 AM1476×312 51.4 KB

Edit: above is for the Desktop application. It looks like you have to do the upload from the browser based Excel Online. I’d recommend clarifying that in your install instructions.

Another Edit: I had to open the link and view the XML in my browser tab then save the contents of it as an .xml file on my computer to be able to upload. I’m on a Mac so I don’t have the option to just paste the URL into the file search per the instructions.

12

Testing this a bit, there seems to be a bug where the UI relies on the column order to be the order you’re presenting in the UI, which is not necessarily the case for all users. Some of the data fields I input were in the wrong columns for the Transactions sheet and the Balance History sheet.

This is an awesome first start, but I should note to anyone reviewing this that we will build these features into the Tiller Money Feeds add-in in the near-ish term. :wink:

13

Environment: I am using Windows 11 and Office 365. I have tested in both Chrome and Edge.

I thought I mentioned it here, but I guess it wasn’t clear enough that I am using a web browser to use the add-in.

The original pulls from the formulas from the accounts sheet. The new version soon to be uploaded does not. It pulls from Transactions and Balance history directly. It captures all columns from the sheets as long as the new columns are in the BalanceHistory and Transactions tables. I just need to write the insert piece function which can be used for both since it is modular. I have tried to get Microsoft to publish the add-in on my domain account but it has gone through all the hurdles except where they verify the business. I have sent them all the proof they ask for but it has become stagnant. I also just started a new job so I am now a little time contstrained. However, with the Google sheets you have the Tiller add-in and then you have the community Add-in. Do you have something like that for Excel so you can Beta test functionality like this? I will put in the time to get the coding working, but I do not have time to make it the prettiest, even table cells on CSS refresh etc… I just do not want to waste my time working on the functionality and it can’t get published.

14

Hello Heather,

If you still have time, I have uploaded the new code which pulls directly from transactions and balance history sheets. The Manual Account and Manual Transactions Tabs build from the pull. This way it should include all columns regardless of how they were inserted or edited.

There is a checkbox to display all fields, but I only display fields that are necessary to add the transaction or update the account balance. All other fields are pulled from the headers and people can insert whatever they are tracking in the extra fields.

The insert buttons are disabled for now until I do more testing on the insert function.

I am relying on Categories Column A to always be the categories column, but I also am working on fixing it so it doesn’t matter.

I am relying on column H of the Accounts sheet for the account information.

I rely on Transactions, BalanceHistory Tables continuing to be a table and only focus on what is in the table.

Do you see these as changing or are they safe assumptions?

If you have time, do you see any issues that may need to be addressed?

Sam