Author Topic: One sheet to rule them all...  (Read 271140 times)

letstalkaboutdune

  • 5 O'Clock Shadow
  • *
  • Posts: 16
  • Location: Tucson, AZ, USA
Re: One sheet to rule them all...
« Reply #200 on: January 19, 2018, 01:28:54 PM »
Thank you for this awesome spreadsheet! Starting in January should keep things nice and clean for me. I did make a couple of edits, like removing the Invest sheet (already tracked through Fidelity and Personal Capital), and automatically pulling the monthly Income and Expenses data into the NW tab. Finally, since my discretionary and excess expenses will change each month, I grouped the non-standard categories into a row called "Other" in the FI tab, and that is calculated with whatever's left after the standard categories.

px4shooter

  • Stubble
  • **
  • Posts: 140
Re: One sheet to rule them all...
« Reply #201 on: February 08, 2018, 09:40:52 PM »
Thanks for the sheet!!

Awesome calculations, but I am stuck. I still have 3 properties with mortgages. I cannot see how to calculate the other sheets into the main page. I duplicated the mortgage page and set them up, but just having calculation issues.

Any excel tips on this type of setup?

rosipov

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #202 on: April 01, 2018, 12:09:28 PM »
I'm a few years late to the party, but I started using the sheet a few months ago and I love it! Quick question though, where does tax refund come into the picture? Is there somewhere I can keep track of it?

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #203 on: April 07, 2018, 08:49:32 AM »
I just put it in one of the 6 different income entries on the month it comes in.  IE right below you paycheck.  It will get added into income and then calculated into you saving rate as I am sure you are investing it all right????

rosipov

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #204 on: April 07, 2018, 12:44:37 PM »
Oh, I should have probably thought of that - thank you! That makes perfect sense, I just put those as positive State Tax/Federal Tax item in the paystub section. And yes, I invest all of it :)

Now I just can't wait until the end of the year to see what's my real savings rate now that the tax refund is counted!

VanteBoll

  • 5 O'Clock Shadow
  • *
  • Posts: 14
Re: One sheet to rule them all...
« Reply #205 on: April 23, 2018, 05:01:40 PM »
Can anyone tell me why I am getting this error on FI tab (in the attachment)?

I've inserted values for each month and the mortgage tab, but that's it. Any ideas?

Thank you in advance.

PrairieBeardstache

  • 5 O'Clock Shadow
  • *
  • Posts: 65
Re: One sheet to rule them all...
« Reply #206 on: May 01, 2018, 01:20:39 PM »
I apologize if this has already been asked/answered elsewhere but: Has anyone turned this into a Ruby app or similar? And if so, a GitHub repo?

I know there are several software engineers here so I figured I'd ask before I went ahead and made this a side project (also, I'm not a great programmer, but good enough to create a self-hosted version of this).

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #207 on: May 06, 2018, 08:36:59 AM »
Can anyone tell me why I am getting this error on FI tab (in the attachment)?

I've inserted values for each month and the mortgage tab, but that's it. Any ideas?

Thank you in advance.

You need to fill out one row on the NW tab.  This is your "snapshot" in time at the end of each month that will stay with the sheet from year to year.  As you get more data in there the numbers will become more accurate.

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #208 on: May 09, 2018, 06:40:16 PM »
I apologize if this has already been asked/answered elsewhere but: Has anyone turned this into a Ruby app or similar? And if so, a GitHub repo?

I know there are several software engineers here so I figured I'd ask before I went ahead and made this a side project (also, I'm not a great programmer, but good enough to create a self-hosted version of this).

Interesting—whats your vision for the self-hosted version? Meaning, what would others get from it that they wouldn’t get now? Im curious what value you anticipate app-ifying it would create.


Sent from my iPhone using Tapatalk

PrairieBeardstache

  • 5 O'Clock Shadow
  • *
  • Posts: 65
Re: One sheet to rule them all...
« Reply #209 on: May 11, 2018, 10:17:35 AM »
I apologize if this has already been asked/answered elsewhere but: Has anyone turned this into a Ruby app or similar? And if so, a GitHub repo?

I know there are several software engineers here so I figured I'd ask before I went ahead and made this a side project (also, I'm not a great programmer, but good enough to create a self-hosted version of this).

Interesting—whats your vision for the self-hosted version? Meaning, what would others get from it that they wouldn’t get now? Im curious what value you anticipate app-ifying it would create.


Sent from my iPhone using Tapatalk

Connecting to third party APIs used for the basic input functions: Mint, Investment brokers, etc. (I'm unsure if they have public APIs)

In effect, like Mint, but for FIRE.  In short, I'm lazy when it comes to trivial tasks and I like to automate them away.

pseudoyams

  • 5 O'Clock Shadow
  • *
  • Posts: 21
Re: One sheet to rule them all...
« Reply #210 on: July 09, 2018, 10:57:56 AM »
Sorry if this has been asked before, but is it possible to accurately start this in the middle of the year.  Or, does it require all data for Jan-Dec?  I dont think I can get the wife's payroll info prior to last month.

Nm, I figured it out.  RTFM. ;)
« Last Edit: July 09, 2018, 04:15:16 PM by pseudoyams »

budgetryan

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: One sheet to rule them all...
« Reply #211 on: December 01, 2018, 10:17:21 AM »
First - this sheet looks fantastic. In the process of molding it to my preferences now!

These may be two dumb questions but..

1) Where does the investment tab tie back in to the rest of the spreadsheet (i.e., income, net worth, etc.)?
2) On the investment tab, how / where would I input "normal" stocks that I invest in (i.e., GM, Tesla, etc.)? I do this through Robinhood with money that is not tied to an IRA or 401K retirement account.

Thanks so much for your help!! Very much appreciated.

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #212 on: December 14, 2018, 03:32:58 PM »
First - this sheet looks fantastic. In the process of molding it to my preferences now!

These may be two dumb questions but..

1) Where does the investment tab tie back in to the rest of the spreadsheet (i.e., income, net worth, etc.)?

The investment tab doesn't really tie back into anything.  It is just a way for you track your investments at different accounts if you have that.  IE you might have a 401k at work, IRA from previous jobs and HSA.  You need to manually copy the value at the end of each month and enter it into you NW sheet.  You should have a column for each account there.  The NW sheet stays with you from year to year so you can see how your accounts grow.  Some people don't use the investment sheet and just go look at your account balance and enter that on the NW sheet at the end of the month.  Just think of of the investment sheet as a tracker and then the NW sheet is a snapshot of that investment sheet at the end of the month.  I find it convenient to use the investment sheet then once every several months log into Vanguard as your dividends and things like that will slightly change your holding amount of any particular stock / bond.


2) On the investment tab, how / where would I input "normal" stocks that I invest in (i.e., GM, Tesla, etc.)? I do this through Robinhood with money that is not tied to an IRA or 401K retirement account.

Just take one of the accounts on the investment sheet and make it Robinhood.  Rename the taxable one if you like.  You can then insert rows in between the middle of that account to how many rows you need for each stock.  Then you will have to cut and paste the formula's down from the first row to get everything to update right.  Then up top make sure the formula is correct for that account.  It needs to sum from the top row of that account to the bottom.  I believe if you insert a row in the middle it will adjust the formula up top for you.

I hope that helps.  Sorry it took a while to answer been really busy with work.


Thanks so much for your help!! Very much appreciated.

olorenshaw.s

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: One sheet to rule them all...
« Reply #213 on: February 18, 2019, 12:51:28 AM »
Hi everyone! A little late to the party here but it's fantastic to see such a wonderful tool for FI upon my return to the MMM world from a slight hiatus.. first time posting on the forum, however!

I'm having a couple of problems in setting up the spreadsheet and hoping I can get some help:

1. My 'budgeted' costs don't seem to be flowing through to the monthly tabs (it is using 'Costs' in FI column B) - I have selected 'TRUE' against 'Use Budget?' in the FI tab
2. My adjusted income doesn't seem to be correct - as if it is adding my monthly taxes, rather than subtracting. I'm nervous to play with the formula, so there might be a trick here?

Obviously it's pretty key to get these two things correct, rather than having erroneous or incorrect figures... I can live with a few $ here or there, but adding taxes and having the default expenses  seems less than useful!

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #214 on: March 03, 2019, 02:11:24 PM »
The budget doesn't pull from the monthly tabs (I think it is left over from the original Sheet).  If you want to look at what budget would do compared to your real spending just set use budget to TRUE on the FI page and set your monthly budget there in column C.  You can switch back and forth by setting it to TRUE or FALSE to compare the two.

Did you enter you Federal, Social Security, Medicare etc. as as a negative?  It should end up looking like the accounting nomenclature.  If you enter -250 for Federal it will show it as ($250).

Retireatee1

  • Stubble
  • **
  • Posts: 209
  • Location: Fort Mill, SC
    • Retireator.org
Re: One sheet to rule them all...
« Reply #215 on: March 22, 2019, 10:06:49 AM »
Nice work.  I recently posted yet another "One Spreadsheet to Rule Them All" style thread here:

https://forum.mrmoneymustache.com/share-your-badassity/launch-of-the-2019-retireator!/

Your expense tab has more granularity, but the Retireator expense tab provides for a transition from the accumulation phase to the distribution phase.  I don't support HELOC's and student loans yet, but those can be roughly approximated.

My net worth calculation takes into account the tax obligation on before-tax savings based on the average tax rate, it doesn't appear you are doing that.

You have a lot of granularity on the investments which can be desirable, I went with basic investment type modeling including target date fund support.

Those month tabs are a bit brutal.

Your dashboard tab is very nice, I like it better than my simple graph interface.

The killer app on my tool is that it can run a binary search algorithm to forecast the exact FIRE date based on the simulation parameters.  You could plug your data into mine and have a go at that (Microsoft Excel for Windows required).





wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #216 on: March 30, 2019, 09:17:49 AM »
I only use a Chromebook or an older Chromebook running Linux so I can't give yours a spin.  I do have an old XP machine I keep around to run Tax software but only use it once a year.

ForeverPoor

  • 5 O'Clock Shadow
  • *
  • Posts: 36
Re: One sheet to rule them all...
« Reply #217 on: April 01, 2019, 01:05:30 AM »
Just checked out this spreadsheet and noticed something odd: when I accessed the sheet via OP's link, the dashboard tab appears to display fine. However, when I open it using Office 2016 or upload it to my own Google Drive, I get the following display error:



Any tips?

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #218 on: April 01, 2019, 04:59:30 PM »
Did you save it in Office first?  The gauge that it isn't displaying wasn't supported on tablets and phones for a while. 

Try going directly to the link in the original post and select File -> Make a copy.  That should save an original in you G drive.  You can then rename and edit from there.  See if that works.

ForeverPoor

  • 5 O'Clock Shadow
  • *
  • Posts: 36
Re: One sheet to rule them all...
« Reply #219 on: April 01, 2019, 06:56:46 PM »
Did you save it in Office first?  The gauge that it isn't displaying wasn't supported on tablets and phones for a while. 

Try going directly to the link in the original post and select File -> Make a copy.  That should save an original in you G drive.  You can then rename and edit from there.  See if that works.

I haven't saved it in Excel just yet. All I did was make a copy -> open locally using Excel on my own computer. Just repeated the process and saved a copy as .xlsx locally on my PC - opened it up and it's still having the same display problem.
« Last Edit: April 01, 2019, 07:01:13 PM by ForeverPoor »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #220 on: April 01, 2019, 07:28:28 PM »
I am going to guess the gauge just isn't supported in Excel.  I don't use Excel so I have no idea if that is right or not but for a several years I know the gauge didn't work on mobile Sheets.

ForeverPoor

  • 5 O'Clock Shadow
  • *
  • Posts: 36
Re: One sheet to rule them all...
« Reply #221 on: April 02, 2019, 02:52:46 PM »
Yeah... I'll have to end up editing this in Google Sheets.

Vicster

  • 5 O'Clock Shadow
  • *
  • Posts: 52
  • Location: UK
Re: One sheet to rule them all...
« Reply #222 on: April 19, 2019, 12:16:06 PM »
Amazing sheet, I am in awe, thanks for creating it.  I'm also not having the budget pull to the months, there doesn't seem to be a formula in those boxes, I tried setting to True in appropriate box in FI sheet.   Using google sheets so not sure if that is problem or more likely 'picnic' (problem in chair, not in computer!)

Quote
The budget doesn't pull from the monthly tabs (I think it is left over from the original Sheet).  If you want to look at what budget would do compared to your real spending just set use budget to TRUE on the FI page and set your monthly budget there in column C.  You can switch back and forth by setting it to TRUE or FALSE to compare the two.

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #223 on: April 20, 2019, 07:12:57 AM »
The Use Budget feature only works on the FI sheet.  On the month tabs it is just a "visual" number so you can see where your real spending is compared to a budget on the month tabs.  On the FI sheet if you enter numbers in the budget column then set Use Budget to TRUE you should see the numbers change on your Years to Fire only in the FI sheet.

SimpleLifer

  • 5 O'Clock Shadow
  • *
  • Posts: 64
Re: One sheet to rule them all...
« Reply #224 on: June 04, 2019, 12:00:37 AM »
@IndyPendent and @wienerdog, and others who contributed to this sheet:

Thank you for offering this gift to the world!  I've been in my own bubble for YEARS, after giving up on finding something more comprehensive than Mint and Personal Capital, I set out to build my own spreadsheet...I had no idea tools like this were being shared!

Last month, I just happened to hit the lottery find MMM, and after reading nearly every post, I moved on to reading forums, and came across this sheet.  That was about 4 days ago.  I've officially cannibalized my hodgey-podgey sheet, and moved pretty much all my stuff over to my version of your sheet. 

Thank you just does not seem like enough.

Very grateful for your time, effort, and energy.  <3

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #225 on: June 07, 2019, 07:05:45 PM »
SimpleLifer, that’s quite a thank you! I’m really glad you’ve found it useful, and I’m grateful that weinerdog has spent so much time making it a better tool than it was when it started. (As well as responding to this thread much, much more frequently than I.)


SimpleLifer

  • 5 O'Clock Shadow
  • *
  • Posts: 64
Re: One sheet to rule them all...
« Reply #226 on: September 01, 2019, 12:37:15 PM »
I looked to see if this has been asked in the thread, and I couldn't find any discussion on this question.

Perhaps I'm just missing something really basic.  I'm having a hard time understanding how the Monthly Savings Rate is calculated, whether it's Pre-Tax or Post-Tax. 

Because I have pre-tax deductions like 401k, HSA, ESPP, etc., a lot of my savings are pre-tax but if I'm reading the formula correctly, pre-tax (tax-deferred) and post-tax (unallocated cash) are all in one bucket.

Questions are:

1. How is Monthly Savings Rate calculated:  Pre-Tax or Post-Tax?
2. On FI page, similar question...if I mark "Use All-Time" it looks like the cell is pulling data from the averages vs. actual from the monthly pages...would these calculations always be Pre-Tax or Post-Tax?

As a side note, this sheet has changed how I plan for my future financially, in so many ways.  I can't think you all enough for all the hard work that has gone into this.  THANK YOU!!!

savingmybest

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: One sheet to rule them all...
« Reply #227 on: December 15, 2019, 09:29:01 AM »
Hi! Thanks for this amazing spreadsheet, I am starting to get it set up for 2020. Question: In the FI tab, what does the "Costs" column mean, and how does it differ from "Budget"? I feel like they are the same thing but I also think I might be misunderstanding something. 

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #228 on: December 18, 2019, 10:19:00 AM »
Hi savingmybest, think of budget vs. costs as planned vs actual. The costs cells average up what actually happened throughout the year, whereas the budget cells are what you planned to have happened.

Glad you're finding it to be valuable!

savingmybest

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: One sheet to rule them all...
« Reply #229 on: December 20, 2019, 04:22:15 PM »
That's the tip I needed, thank you!

savingmybest

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: One sheet to rule them all...
« Reply #230 on: January 01, 2020, 12:22:28 PM »
Got another question. I don't have a mortgage right now, I am a renter. How should I represent this in the Mortgage tab? I'm not sure if zero-ing out everything is messing up the spreadsheet.

Andere

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: One sheet to rule them all...
« Reply #231 on: January 03, 2020, 05:50:14 PM »
I figure it's worth coming back and showing what I've done with the sheet after some years of use. I was originally planning on just showing my changes and showing bug-fixes but they became too numerous to track. I've done a lot of automation and there's much less work to do each month.

https://docs.google.com/spreadsheets/d/1Xj50VYo4Ji_9c2K_4PCVfjuWThHXO1OSw89npUiPf8s/edit?usp=sharing

I hope some of you find this helpful.

TooMuchGlass

  • 5 O'Clock Shadow
  • *
  • Posts: 41
Re: One sheet to rule them all...
« Reply #232 on: January 06, 2020, 01:31:50 PM »
Hello all,

The sheet looks amazing, and I'd like to set it up for 1/1/2020. How do I go about switching the start date on the NW page without screwing with the formulas?

Andere

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: One sheet to rule them all...
« Reply #233 on: January 06, 2020, 03:55:48 PM »
For the version in the original post, you have to update the dates manually. You can do this by setting up the first do and then selecting all the dates you updated. The blue border will have a small blue box in the corner that you can drag down to auto-update the boxes relative to the ones you updated.

TooMuchGlass

  • 5 O'Clock Shadow
  • *
  • Posts: 41
Re: One sheet to rule them all...
« Reply #234 on: January 08, 2020, 07:30:16 AM »
@Andere, thanks for the guidance there.

If I update them manually, will the numbers dump in from the month sheets, or will I need to add them in manually?

Also, I'm considering using your sheets instead, actually, because it seems a bit easier to navigate.


Andere

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: One sheet to rule them all...
« Reply #235 on: January 08, 2020, 08:35:43 AM »
In both the original sheet and my sheet, there is an expectation that you will manually update the Net Worth sheet. While the dates in my version auto-update from the start, you still have to manually make the step of preserving a snapshot of your networth every month.

TooMuchGlass

  • 5 O'Clock Shadow
  • *
  • Posts: 41
Re: One sheet to rule them all...
« Reply #236 on: January 08, 2020, 11:05:12 AM »
there is an expectation that you will manually update the Net Worth sheet.

I see that now on Instruction #6. Sorry about that. I saw the instruction not to change colored cells, saw that the date cells in the NW tab are colored, and immediately assumed I was missing something.

I don't plan on importing from Mint, but doing it manually instead, so it seems pretty straight forward from here! It's nice to know this thread is still active with knowledgable people to help, too. Thanks again for answering my very obvious question in a very nice way.


savingmybest

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: One sheet to rule them all...
« Reply #237 on: January 14, 2020, 08:20:44 PM »
@Andere , I've been using your sheet and I really like it. Had a question, though. I'm trying to use it to track my expenses manually, but I can't seem to add new expense categories without breaking the ones you already set. I have been trying add new categories in the Values sheet in column B, "Associated Mint Fields," but that doesn't seem to work. Is that where I should be doing it? If not, how/where should I be making those instead? Thanks!

Andere

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: One sheet to rule them all...
« Reply #238 on: January 14, 2020, 09:15:33 PM »
@savingmybest, please show me the line you have that's not working, both the A-column title and the B-column comma-separated list that's failing.

These lists should be comma-separated, with no spacing such as Column A Value (the title):
Code: [Select]
Totally Legitimate Purchases and the Column B value should be the drop-down category:
Code: [Select]
beanie babies,fancy hats. This should add "beanie babies" and "fancy hats" to the category drop downs.

savingmybest

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: One sheet to rule them all...
« Reply #239 on: January 15, 2020, 08:20:28 PM »
@savingmybest, please show me the line you have that's not working, both the A-column title and the B-column comma-separated list that's failing.

These lists should be comma-separated, with no spacing such as Column A Value (the title):
Code: [Select]
Totally Legitimate Purchases and the Column B value should be the drop-down category:
Code: [Select]
beanie babies,fancy hats. This should add "beanie babies" and "fancy hats" to the category drop downs.

Just DMed you, that might make it easier for us to troubleshoot without clogging up this thread. Thank you for the response!

barliss2

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #240 on: February 11, 2020, 10:40:51 PM »
@Andere thanks for adding this revised spreadsheet. I'm finding it way more intuitive. My one issue is really on me, but wondering if there is a fix. I use Personal Capital, not Mint and find my CSV file gets imported differently, where the amounts end up in a different section--making the whole thing very inefficient. Is there a way to fix the headers so that when I import mine it follows the Personal Capital pattern, rather than the Mint pattern?

Andere

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: One sheet to rule them all...
« Reply #241 on: February 12, 2020, 08:41:36 AM »
@Andere thanks for adding this revised spreadsheet. I'm finding it way more intuitive. My one issue is really on me, but wondering if there is a fix. I use Personal Capital, not Mint and find my CSV file gets imported differently, where the amounts end up in a different section--making the whole thing very inefficient. Is there a way to fix the headers so that when I import mine it follows the Personal Capital pattern, rather than the Mint pattern?

You will have to modify each individual category parser to use a different column. Currently the formula for any given category looks like this:
Code: [Select]
=SUMPRODUCT(SUMIF($G$88:$G, SPLIT(VLOOKUP($B17, SpendingCategories, 2, FALSE), ","), $K$88:$K))
And you will need to modify $K$88:$K to be about a different column besides K. And $G$88:$G to be another column. G is the category. K is the money. Unfortunately you will need to make this change for every category row on every month. If you were to modify this to look at row H for price and row A for category it would look like this.

Code: [Select]
=SUMPRODUCT(SUMIF($A$88:$A, SPLIT(VLOOKUP($B17, SpendingCategories, 2, FALSE), ","), $H$88:$H))

barliss2

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #242 on: February 12, 2020, 08:50:50 AM »
@Andere thank you. Would I only have to do that on the Mint tab or in other tabs?

Andere

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: One sheet to rule them all...
« Reply #243 on: February 12, 2020, 09:20:14 AM »
@Andere thank you. Would I only have to do that on the Mint tab or in other tabs?

Any tab you're using to derive the spending data. If you're pasting into January's table, that's you will have to modify all months. If you're using the mint tab and then pasting over the formulas in each month, then you only need to modify the mint tab.

fireseeker

  • 5 O'Clock Shadow
  • *
  • Posts: 13
  • Age: 42
  • Location: Richardson, TX
Re: One sheet to rule them all...
« Reply #244 on: February 28, 2020, 10:54:42 AM »
I love this sheet. It replaces at least five separate spreadsheets we had going for various aspects of financial tracking. Thank you!

ciderwave

  • 5 O'Clock Shadow
  • *
  • Posts: 4
Re: One sheet to rule them all...
« Reply #245 on: April 07, 2020, 01:50:43 AM »
Update: Realized that I didn't credit the good folks at YNAB for the code here as it's not mine.  Details and better directions on their site: https://support.youneedabudget.com/t/k9rxc9/using-google-apps-script-with-the-api

Replying with, I think, my first post in MMM forums to share how I use this sheet with YNAB's API to automatically import category spend.  I didn't see anything about this in search, but can delete this is if's a dupe.

Code block at the bottom goes in to the script editor(Tools -> Script Editor).  I then have
Code: [Select]
=get_ynab_categories_for_month("API-key","budgetURL",$DATE) spit out the month, category name, and activity in 3 columns and
Code: [Select]
=SUMIF(K$16:K,A15,L$16:L)*-1 look up the amount for each category.  You can generate an API token for YNAB at https://app.youneedabudget.com/settings/developer and your budgetURL should be the string in between "https://app.youneedabudget.com/" and "budget" in the address bar of your browser.

This has made it so that when I go to close out each months entry, all the spend is already populated.  Not sure how many YNAB users are around here, but it's saved me time and thought others might get a kick out of it. Enjoy!


Code: [Select]
function fetch_ynab_data(accessToken, path){
  const url = "https://api.youneedabudget.com/v1/" + path;
  const options = {
    "headers": {
      "Authorization": "Bearer " + accessToken
    }
  };
  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText()).data;
  return data;
}

function get_ynab_categories_for_month(accessToken, budgetId, month) {
  const budget_month = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/months/" + month).month;

  const columns = ["Month", "Name", "Activity"];
  const rows = [];

  // Add the categories
  for (var category_idx = 0; category_idx < budget_month.categories.length; category_idx++) {
    var category = budget_month.categories[category_idx];
    var name = category.name;
    var id = category.id;
    // Calculate currency amounts from mulliunits
    var budgeted = category.budgeted / 1000.0;
    var activity = category.activity / 1000.0;
    var balance = category.balance / 1000.0;
    rows.push([month, name, activity]);
  }

  return [columns].concat(rows);
}
« Last Edit: May 18, 2020, 01:23:27 AM by oneslypig »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #246 on: April 24, 2020, 06:54:32 AM »
Replying with, I think, my first post in MMM forums to share how I use this sheet with YNAB's API to automatically import category spend.  I didn't see anything about this in search, but can delete this is if's a dupe.


Nice is there a way to turn it on or off?  If there was we might include it in the original spreadsheet.  I still use Mint but this looks more simple.  I suppose if I had time I could probably do the same thing with Mint.  I looked at Sheets scripting early on but it never went anywhere.

ciderwave

  • 5 O'Clock Shadow
  • *
  • Posts: 4
Re: One sheet to rule them all...
« Reply #247 on: May 18, 2020, 01:42:36 AM »
Nice is there a way to turn it on or off?  If there was we might include it in the original spreadsheet.  I still use Mint but this looks more simple.  I suppose if I had time I could probably do the same thing with Mint.  I looked at Sheets scripting early on but it never went anywhere.

The code is effectively inert until provided with a valid API key and budget url, so the code in the script editor should be reasonably safe to keep there(though it may need updates if/when some API calls change).

The biggest question for merging back in to the master template would probably be around implementation in the actual sheet.  Once placed, inserting the date, API token, and url are pretty straightforward. 

For placement, I've modified the month template quite a bit and have my "actual" column populated with SUMIF commands against budget categories for activity amounts.  That would obviously be disruptive for anyone who doesn't use YNAB(example of my implementation here: https://imgur.com/neQNGSY

lava890

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: One sheet to rule them all...
« Reply #248 on: June 02, 2020, 11:07:41 AM »
I figure it's worth coming back and showing what I've done with the sheet after some years of use. I was originally planning on just showing my changes and showing bug-fixes but they became too numerous to track. I've done a lot of automation and there's much less work to do each month.

https://docs.google.com/spreadsheets/d/1Xj50VYo4Ji_9c2K_4PCVfjuWThHXO1OSw89npUiPf8s/edit?usp=sharing

I hope some of you find this helpful.

Thanks so much for posting this. I believe I found an error you may want to correct. On the "FI" tab, the formula on rows 85-94, from column C through M, are incorrect. They weren't dragged down from row 84.

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #249 on: March 26, 2022, 07:34:56 PM »
Really curious to hear if/how people are actually using this sheet as a monthly budgeting too.

I do not. I use it to track the results of monthly budgeting, but I use Mint for the transaction tracking.