Overview
Always make a backup of your essential files, like a budget. Try the install on the copy and if it works as advertised, then go live with the update or revert if there is an issue.
Environment: I am running Windows 10 with O365 installed and using the Desktop Excel.
I like my budget to track manual transactions and instead of entering them manually into the Transactions sheet. I created an Excel sheet for automating adding manual transactions to the Transactions sheet, like we can already do in Google Sheets. Spoiler: This is a key feature needed to track the current balances of your accounts while waiting for the transactions to clear. (Coming soon to Excel).
Note to Tiller: Do not hate me, but this uses an Excel add-in (old school), which is another name for a Macro, but it is not embedded in your Tiller budget workbook so you can keep the Tiller Feeds workbooks as a Macro free Excel extension.
How did you come up with the idea for your workflow?
I wanted it to make my life easier and since Excel is modular, why not. Plus, a lot of people are asking for it in the community chat. Ok, challenges: Tiller, from what I understand, want a community-based budget spreadsheet that the community can edit, share, and create new functionality while they manage the core sheets and connectivity to the financial world. (I am glad they can do that as it is extremely helpful). Additionally, the spreadsheet should be dynamic and use the built-in formulas to add functionality to the spreadsheet. Challenge 1: The Excel template is saved in .XLSX file extension which by design disables any VBA (Visual Basic) Macros. So not helpful to provide a Macro to embed in Tillers provided template as everyone would have to save the file as an .XLSM (which allows Marcos). Challenge 2. No scripting, so external programs are a no go.
Solution: Create a Macro through VBA and save it as an Add-In with the file extension .XLAM which provides the functionality to the Tiller template without changing the template, so it maintains version control.
Installation
Here is the installation video if you get stuck.
YouTube installation directions
Installation: (Probably for moderately experienced Excel users but should be straightforward if you follow the directions).
Go to my One Drive Share: Mr. Signals One Drive Share
- Right Click on each of the Excel files and choose Download, or download the Zip file.
- Save them to the file folder of your choice.
-
Open your linked Tiller Feeds workbook in Excel (the file that already contains your transactions).
-
Open the workbook you just downloaded in Excel (the one containing the Add Manual Transaction template).
-
Right-click on the Add Manual Transaction sheet in the workbook you just downloaded and click “Move or Copy”.
-
In the “To book” dropdown, select your linked Tiller Feeds workbook.
-
Leave the “Create a copy” checkbox unchecked.
-
In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.
-
The new worksheet should appear in your Tiller Feeds workbook with the name “Add Manual Transaction”.
-
Navigate to “Data / Edit Links…” in the menu.
-
Select the link to the downloaded workbook in the box.
-
Click the “Change Source…” button.
-
Navigate to the active workbook (i.e., select your personal spreadsheet).
-
Navigate to the Add Manual Transaction sheet to Setup Data validation Rules (The data validation rules do not copy so they must be created.)
-
Select the Data Menu in the toolbar.
a. Select the Type, Account, or Category input Cell.
b. Select the Data Validation button.
OR 
c.Select List from the dropdown menu Allow:
d.Insert the Source from the Rules below.
Images of the Data Validation
e. Repeat for the other two Input Cells.
- Rules:
a. Transaction Type: Deposit, Withdrawal
b. Account: =Accounts!$H$2:$H$80
c. Category: =Categories!$A$2:$A$55 - Probably a good time to save your workbook.
At this point, your Add Manual Transaction Sheet should be linked to your local workbook’s data.
Setup (The Magic)
-
Install the download Excel Add-In: (This will install the Add-In to your Excel environment).
|a.|Go to the File menu in Excel.
|—|—|
|b.|Select Options in the lower left corner.
|c.|Select Add-ins on the left-hand side.
|d.|Select Excel Add-ins in the dropdown box Manage, and then select Go button.
|e.|Choose the Browse Button.
|f.|Browse to the location you saved the downloaded Add-In.
|g.|Select the file and click the Open button.
|h.|The new Add-In should show up in the Add-Ins window.
|i.|Check the box for the new Add-in if not already and hit the Ok button. -
Add a Ribbon button so that you can run the Add-in.
a. Right click on the Ribbon bar up top and select Customize the Ribbon.
b. bIn the Choose commands from dropdown select Macros.
c. Select Enter_Manual_Transaction
d. Select the Data Tab and click new group.
e. Select the group you just created and rename. (Pick a name and picture that you want)
f. Then click the Add >> button.
g. Finally, click the Ok button and you should see your new button on the Data tab.
Usage
Enter the transaction details in the correct cells. Select the button you created in the ribbon bar. The transaction should be inserted at the top of your Transactions sheet, the Add Manual Transaction cells should be cleared, and you are ready to insert another transaction if desired.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Yes!!! Please provide feedback on any issues you encounter and/or suggestions on how to make improvements!
Notes
Anything else you’d like people to know?
Environment Windows 10 with O365 using Desktop Excel
FAQ
TBD
