Create Initial Google Sheets App

[mbcann01]

Overview

This will likely need to be split into multiple tasks at some point. I'm just getting started here. I think it would be cool to eventually build the budget app with R and Shiny. However, I think it might be easier to start with Google Sheets. Additionally, it might make it easier to manually adjust the data tables if everything is in Google Sheets.

Useful links

• 2024 Budget
• Budget app folder on Google Drive
• DB Schema
• Google Sheets GPT
• Google Sheets: Test Query Function

Sheet types

I think I may end up with at least 4 different sheet types (and maybe I end up keep some of these in separate workbooks).

• Development sheets: For testing and development notes.
• Data sheets: For storing data. For example, payees and transactions.
• UI sheets: The sheets I view and interact with. For example, dashboards and bill pay.
• Worker sheets: Sheets that bridge data sheets and UI sheets. I don't interact with them directly, but they pull things together.

Other questions/ideas

• I should also create a list here of what information each sheet contains at some point. Maybe add them as comments directly in the Google Sheet or in the DB schema.
• Instead of having to uncheck the "pending" boxes, I want to be able to download my transactions from Chase (or wherever) and have Google Sheets automatically check to see if the bill was paid or not.

Tasks

• [x] Move the example data tables from the R/Shiny budget app repo to Google drive.
• [x] Test importing transactions from USAA.
• [x] Copy categories and subcategories from Quicken.
• [x] Add all accounts from the 2024 budget to the Accounts sheet.
• [x] Create an initial DB schema.
• [ ] Improve the way I'm looking up closed date in Test Select Rows By Pay Period. Right now, it only checks to see if there is a closed date or not. It doesn't check to see if the account was closed on the date selected with the date selector, though. Tie the closed date (Accounts!C:C) to the date selected on Bill Pay.
• [ ] Move payee selection code from Test Select Rows By Pay Period to Budget App Prototype.
• [ ] Move on to working on pulling over the account balance (and other columns) after we have the list of payees for the selected year-month-period. I think we should do this in a separate Test file like we did with Test Select Rows By Pay Period.
• [ ] Figure out rules to categorize transaction categories.
• [ ] Add details (e.g., address, etc.) to the entity and accounts page.
• [ ] Create a log of all credit cards

[mbcann01]

2024-02-19

• Left off at trying to list the bills that are due on the Dev Bills Due sheet.

[mbcann01]

2024-02-21

• Working on the Dev Bills Due.
• Tested out importing some transitions from USAA.
• Added Quicken categories and subcategories to the Lists sheet.
• Added accounts to the accounts page.

[mbcann01]

2024-02-22

• Completed a first draft of the DB Schema
• Working on the Dev Bills Due sheet. Import one variable at a time.
  • Filling in the pay period stuff in the Accounts sheet so I can figure out the best way to make the appropriate rows appear.
  • Update schema when done.

[mbcann01]

2024-03-01

• Working on the Dev Bills Due sheet. Import one variable at a time.
  • Filling in the pay period stuff in the Accounts sheet so I can figure out the best way to make the appropriate rows appear.
  • Current thinking: Use the account_pay_period_manual column to set the pay period. Initially, I considered making this a calculated column based on due date. But, I sometimes like to shift bills between pay periods manually. The value in this column will help determine if an account will appear or not when a given month and pay period are selected on the Dev Bills Due sheet.
  • Update schema when done.

[mbcann01]

2024-03-03

• I left off working on the file called Test Select Rows By Pay Period.
• At this point, I can select rows based on the following criteria:
  • The bill was created on or before the year and month selected using the selector. In other words, if it's June and I want to see what the bills looked like in February, I would choose February in the month selector. A bill that I didn't start paying until March wouldn't show up.
  • The bill is active (i.e., doesn't have a closed date). Although, I'm probably doing this in too simplistic of a way. Right now, it only checks to see if there is a closed date or not. It doesn't check to see if the account was closed on the date selected with the date selector, though. I need to add that functionality.
  • The pay period selected.

Next:

• Tie the closed date (Accounts!C:C) to the date selected on Bill Pay.
• Move payee selection code from Test Select Rows By Pay Period to Budget App Prototype.
• Move on to working on pulling over the account balance (and other columns) after we have the list of payees for the selected year-month-period. I think we should do this in a separate Test file like we did with Test Select Rows By Pay Period.