Excel for Personal Finance Accounting ?

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
User avatar
SpartanFan
Posts: 275
Joined: Tue Aug 07, 2007 10:49 am
Location: Michigan - MSU

Excel for Personal Finance Accounting ?

Post by SpartanFan » Sun Jan 21, 2018 7:16 am

I have been a Quicken user since the old MS-DOS days but now that Quicken has gone to a annual fee based upgrade model (which I refuse to upgrade to) I would like to find a replacement to reconcile and maintain our deposit and credit card accounts that is Excel based. The simpler the better.

Any suggestions ?
"There's a crack in everything, that's how the light gets in" - Leonard Cohen

AntsOnTheMarch
Posts: 610
Joined: Mon May 29, 2017 5:47 pm

Re: Excel for Personal Finance Accounting ?

Post by AntsOnTheMarch » Sun Jan 21, 2018 7:46 am

SpartanFan wrote:
Sun Jan 21, 2018 7:16 am
I have been a Quicken user since the old MS-DOS days but now that Quicken has gone to a annual fee based upgrade model (which I refuse to upgrade to) I would like to find a replacement to reconcile and maintain our deposit and credit card accounts that is Excel based. The simpler the better.

Any suggestions ?
How familiar are you with excel or other spreadsheet programs? They’d do the job but there is a learning curve. You don’t just start entering numbers and it’s all done for you. You can get a template and work off that but chances are you’ll eventually run into things you want to change. Over almost 3 decades I went from quicken, to a custom database, to a collection of spreadsheets—which is where I stand now. It was definitely a Goldilocks situation for me.

Quicken was great for a while but I realized that my data was essentially held hostage and they would do to me exactly what’s panned out (rent seeking, subscription crapware), so I dumped it. The custom database (FileMaker Pro) was amazing but took a lot time to develop and changes were complicated and time consuming to make. Also, the software is not cheap and although I didn’t update to every new version, it got pricey.

For the last 5 years I’ve been using Apple numbers spreadsheet which is free, works perfectly within the apple ecosystem, and does everything I need in a spreadsheet. I prefer using my iPad for 95% of my personal computer activity and numbers works beautifully on the iPad (better than even on the Mac, imho—but a small percentage of features can only be accessed on the Mac).

The most popular spreadsheet options are excel, open office and libre office (open source freeware / shareware / donationware) and google sheets (free—which means you are the product). Any of the spreadsheet options can handle the job.

rkhusky
Posts: 5697
Joined: Thu Aug 18, 2011 8:09 pm

Re: Excel for Personal Finance Accounting ?

Post by rkhusky » Sun Jan 21, 2018 7:59 am

I'm sure you could find an Excel-based (or other spreadsheet-based) personal finance package on the web, but you could also try GnuCash, which is a free accounting package. It has a few quirks and is missing a couple of the reports that I liked in Quicken, but I've been using it for the last year and it's been fine. I was able to transfer my Quicken data, although I just transferred a year's worth into my working file. I transferred the rest into a backup file, in case I ever needed to access it.

User avatar
SpartanFan
Posts: 275
Joined: Tue Aug 07, 2007 10:49 am
Location: Michigan - MSU

Re: Excel for Personal Finance Accounting ?

Post by SpartanFan » Sun Jan 21, 2018 8:00 am

AntsOnTheMarch wrote:
Sun Jan 21, 2018 7:46 am

How familiar are you with excel or other spreadsheet programs? They’d do the job but there is a learning curve. You don’t just start entering numbers and it’s all done for you. You can get a template and work off that but chances are you’ll eventually run into things you want to change. Over almost 3 decades I went from quicken, to a custom database, to a collection of spreadsheets—which is where I stand now. It was definitely a Goldilocks situation for me.

Quicken was great for a while but I realized that my data was essentially held hostage and they would do to me exactly what’s panned out (rent seeking, subscription crapware), so I dumped it. The custom database (FileMaker Pro) was amazing but took a lot time to develop and changes were complicated and time consuming to make. Also, the software is not cheap and although I didn’t update to every new version, it got pricey.

For the last 5 years I’ve been using Apple numbers spreadsheet which is free, works perfectly within the apple ecosystem, and does everything I need in a spreadsheet. I prefer using my iPad for 95% of my personal computer activity and numbers works beautifully on the iPad (better than even on the Mac, imho—but a small percentage of features can only be accessed on the Mac).

The most popular spreadsheet options are excel, open office and libre office (open source freeware / shareware / donationware) and google sheets (free—which means you are the product). Any of the spreadsheet options can handle the job.
I have years of experience with Excel. If there is a good template out there, then that is what I need to get started. I just do not want to "recreate" the wheel. Your comments on Quicken are exactly why Iam looking for an alternative.
"There's a crack in everything, that's how the light gets in" - Leonard Cohen

AntsOnTheMarch
Posts: 610
Joined: Mon May 29, 2017 5:47 pm

Re: Excel for Personal Finance Accounting ?

Post by AntsOnTheMarch » Sun Jan 21, 2018 8:04 am

SpartanFan wrote:
Sun Jan 21, 2018 8:00 am
AntsOnTheMarch wrote:
Sun Jan 21, 2018 7:46 am

How familiar are you with excel or other spreadsheet programs? They’d do the job but there is a learning curve. You don’t just start entering numbers and it’s all done for you. You can get a template and work off that but chances are you’ll eventually run into things you want to change. Over almost 3 decades I went from quicken, to a custom database, to a collection of spreadsheets—which is where I stand now. It was definitely a Goldilocks situation for me.

Quicken was great for a while but I realized that my data was essentially held hostage and they would do to me exactly what’s panned out (rent seeking, subscription crapware), so I dumped it. The custom database (FileMaker Pro) was amazing but took a lot time to develop and changes were complicated and time consuming to make. Also, the software is not cheap and although I didn’t update to every new version, it got pricey.

For the last 5 years I’ve been using Apple numbers spreadsheet which is free, works perfectly within the apple ecosystem, and does everything I need in a spreadsheet. I prefer using my iPad for 95% of my personal computer activity and numbers works beautifully on the iPad (better than even on the Mac, imho—but a small percentage of features can only be accessed on the Mac).

The most popular spreadsheet options are excel, open office and libre office (open source freeware / shareware / donationware) and google sheets (free—which means you are the product). Any of the spreadsheet options can handle the job.
I have years of experience with Excel. If there is a good template out there, then that is what I need to get started. I just do not want to "recreate" the wheel. Your comments on Quicken are exactly why Iam looking for an alternative.
Have not used excel for a long time but I’m sure that there are many templates out there. Maybe some come with the software itself? Do a google search for what you’re looking for and also, there are many power excel users on this forum so tap their wisdom. Good luck!

AnnieK
Posts: 46
Joined: Sat May 14, 2016 10:51 am

Re: Excel for Personal Finance Accounting ?

Post by AnnieK » Sun Jan 21, 2018 9:13 am

I was a long time Quicken user that changed to Excel about 10 years ago for the same reason. I am much happier with Excel and I find it easier. I have good Excel skills though.

It can be as simple or as complicated as you want it to be. To me simpler is better. Mine is a workbook with a sheet that is an account register for each account. I also have a sheet where I track my net worth which I update at the end of every quarter.

Back in the olden days we did everything with paper and pencil. My Excel system is just an electronic version of that.

User avatar
midareff
Posts: 5792
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Excel for Personal Finance Accounting ?

Post by midareff » Sun Jan 21, 2018 9:19 am

Always used excel.. started at work as soon as they went PC and had MS programs. Has to be since Win95 so 23+ years at this point. Have some excel skills but certainly not a wizard. If there is something I want to do and don't know how I just Google it and it's always out there at some site. I use excel for all financial matters, investments and so forth, any data I want I just program the sheets/workbooks to provide.

runner540
Posts: 753
Joined: Sun Feb 26, 2017 5:43 pm

Re: Excel for Personal Finance Accounting ?

Post by runner540 » Sun Jan 21, 2018 9:28 am

I've been using Excel for about 10 years. Have built a template for tracking everything I want to (expenses versus budget, net worth on a quarterly basis, estimating taxes, etc.) If you know what you're doing in Excel, all you need to enter is the transaction date, amount and category, then Excel can do the rest if you've set up a template. I start a new file in January of every year, and tweak my template and budget.

Only downside: I keep it on my home computer, so I have to find time to sit down and update it when I'm at home. Can't do it on the go.

H-Town
Posts: 1270
Joined: Sun Feb 26, 2017 2:08 pm

Re: Excel for Personal Finance Accounting ?

Post by H-Town » Sun Jan 21, 2018 10:05 am

SpartanFan wrote:
Sun Jan 21, 2018 7:16 am
I have been a Quicken user since the old MS-DOS days but now that Quicken has gone to a annual fee based upgrade model (which I refuse to upgrade to) I would like to find a replacement to reconcile and maintain our deposit and credit card accounts that is Excel based. The simpler the better.

Any suggestions ?
I design my excel spreadsheet for personal finance from the ground up. Keep in mind that:
1) Focus on your goals: Why do you need this tool? What information or data do you need from this tool to make decision?
2) Keep it consistent so that you have accurate and comparable data.
3) Explore some templates so that you get a complete idea of what you want out of your excel. See Vertex42.
4) Keep it simple so that you don't have to spend a lot of time once you finish setting it up.
5) Keep it simple so that you and your spouse can immediately see important metrics, progress towards goals, and potential issues, etc.
6) Keep details record of transactions so you can cross check with record keeping of your bank, brokerage house, your employer's 401-k, etc.
7) Keep details record so it can help you with estimated tax, maximize your itemized deductions and help with any tax audit. Those "general ledger" can be saved in a separate spreadsheet so it does not clutter your main excel.

My background are in both finance and software engineer so this is my "playground".

AntsOnTheMarch
Posts: 610
Joined: Mon May 29, 2017 5:47 pm

Re: Excel for Personal Finance Accounting ?

Post by AntsOnTheMarch » Sun Jan 21, 2018 10:13 am

runner540 wrote:
Sun Jan 21, 2018 9:28 am
I've been using Excel for about 10 years. Have built a template for tracking everything I want to (expenses versus budget, net worth on a quarterly basis, estimating taxes, etc.) If you know what you're doing in Excel, all you need to enter is the transaction date, amount and category, then Excel can do the rest if you've set up a template. I start a new file in January of every year, and tweak my template and budget.

Only downside: I keep it on my home computer, so I have to find time to sit down and update it when I'm at home. Can't do it on the go.
That’s the exact system I use—except for having it on Apple numbers/iCloud so that I can access from every device. No more than a few keystrokes and I’ve entered and categorized a transaction. All the reports and analysis are pulled from that essential info.

I have a separate sheet for net worth/investments as it’s a perpetual spreadsheet (do not close it out at year end). In that sheet, I copy over each year’s total income and expenses from the budget sheet at the end of each year. This gives me all sorts of running totals of savings, expenses, income, etc.

User avatar
Peter Foley
Posts: 4581
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

Re: Excel for Personal Finance Accounting ?

Post by Peter Foley » Sun Jan 21, 2018 10:31 am

I used MS Money for many years before moving to Excel about 10 -12 years ago. While MS Money was better at tracking spending, I found that I really did not use that data. For tracking asset performance, asset allocation, and net worth, Excel works well. I have a relatively simple portfolio and updating the numbers on a quarterly basis only takes about a half hour.

MikeG62
Posts: 1171
Joined: Tue Nov 15, 2016 3:20 pm
Location: New Jersey

Re: Excel for Personal Finance Accounting ?

Post by MikeG62 » Sun Jan 21, 2018 10:56 am

SpartanFan wrote:
Sun Jan 21, 2018 7:16 am
I have been a Quicken user since the old MS-DOS days but now that Quicken has gone to a annual fee based upgrade model (which I refuse to upgrade to) I would like to find a replacement to reconcile and maintain our deposit and credit card accounts that is Excel based. The simpler the better.

Any suggestions ?
Don’t know of any templates. I custom designed my multi-tabbed excel spreadsheet which suits my needs. Wrote this is a similar thread a while back...

Multi-tabbed Excel spreadsheet.

One tab is electronic checkbook register (created by me which replaces paper checkbook register). Second tab summarizes monthly credit card spending in the categories (about 8 of them) I want to track for such spending - for all CC's used that month. Third tab pulls in the checkbook data and credit card spending and summarizes them by month, including comparison to budget.

It takes time each month, but not an inordinate amount of time. Checkbook register is easiest - like using a paper register, but much easier to reconcile to bank statement (actually, I reconcile in real time periodically throughout the month - I no longer reconcile to monthly bank statement). Reviewing CC statements and categorizing into the buckets I want to track takes the most time. I am retired so I don't mind doing it. Plus, it keeps me close to our spending and avoids the need for periodic spending reviews (as the review now happens in real time and is ongoing).

...At the end of 2017, I added/built-in a number of charts and graphs - or I should say I pulled data from the spreadsheets into several pie and bar charts. Made the process of reviewing our 2017 investable assets, performance and spending with my DW “a lot” easier too. Much more receptive to looking at charts than what otherwise seems like a blizzard of numbers in a spreadsheet.
Real Knowledge Comes Only From Experience

User avatar
SpartanFan
Posts: 275
Joined: Tue Aug 07, 2007 10:49 am
Location: Michigan - MSU

Re: Excel for Personal Finance Accounting ?

Post by SpartanFan » Sun Jan 21, 2018 12:35 pm

Thanks for all the feedback ...

From what I have looked at I believe I will pursue the VRTEX42 Excel Template.

Tom
"There's a crack in everything, that's how the light gets in" - Leonard Cohen

annielouise
Posts: 343
Joined: Wed May 14, 2008 4:11 pm

Re: Excel for Personal Finance Accounting ?

Post by annielouise » Sun Jan 21, 2018 1:16 pm

I didn't read very carefully, just wanted to point out that it isn't too hard to extract quicken history and put it in excel. We did that when we made the switch last year.

User avatar
Emiliania
Posts: 16
Joined: Sat Dec 20, 2014 4:05 pm

Re: Excel for Personal Finance Accounting ?

Post by Emiliania » Sun Jan 21, 2018 2:05 pm

SpartanFan wrote:
Sun Jan 21, 2018 7:16 am
I have been a Quicken user since the old MS-DOS days but now that Quicken has gone to a annual fee based upgrade model (which I refuse to upgrade to) I would like to find a replacement to reconcile and maintain our deposit and credit card accounts that is Excel based.
Wait, isn't Excel going to an annual subscription model too? Isn't that what this Office 365 stuff is? Because of this, I was looking to migrate out of Excel and into Open Office or Libre Office.

michaeljc70
Posts: 3746
Joined: Thu Oct 15, 2015 3:53 pm

Re: Excel for Personal Finance Accounting ?

Post by michaeljc70 » Sun Jan 21, 2018 3:12 pm

I know Excel very well, but would not use it for that purpose. To generate enough adequate reports and do what you want would be more work than I would want to do. If you just want to keep a list of expenses, that wouldn't be too bad.

Why don't you just use Quicken? The download features stop working after it expires, but the rest of the application works. It is made for what you want to do.

User avatar
pezblanco
Posts: 477
Joined: Thu Sep 12, 2013 8:02 pm

Re: Excel for Personal Finance Accounting ?

Post by pezblanco » Sun Jan 21, 2018 3:21 pm

GnuCash has been an absolute revelation for me and for my partner who keeps not only personal accounts but accounts for a thriving small business.

It is open source and has an active user base. Highly recommended if you want to avoid the cloud based, user fee, model that so many computer applications are going to.

User avatar
cheese_breath
Posts: 7998
Joined: Wed Sep 14, 2011 7:08 pm

Re: Excel for Personal Finance Accounting ?

Post by cheese_breath » Sun Jan 21, 2018 3:23 pm

I've used Excel for over 2 decades. It might not have all the fancy bells and whistles of some packages, but it enables me to do what I want.
The surest way to know the future is when it becomes the past.

ubermax
Posts: 1451
Joined: Tue Feb 11, 2014 2:19 pm
Location: Connecticut

Re: Excel for Personal Finance Accounting ?

Post by ubermax » Sun Jan 21, 2018 4:05 pm

SpartanFan wrote:
Sun Jan 21, 2018 8:00 am
I have years of experience with Excel. If there is a good template out there, then that is what I need to get started. I just do not want to "recreate" the wheel. Your comments on Quicken are exactly why I am looking for an alternative.
I also have years of experience with Excel and so , if it were me, I'd design my own spreadsheet - you end up with a customized product developed around your situation - templates by nature , IMO, are generalized whereas you and your needs are unique - looks like a few others have also leaned towards DIY .

Good Luck !!

User avatar
randomizer
Posts: 1529
Joined: Sun Jul 06, 2014 3:46 pm

Re: Excel for Personal Finance Accounting ?

Post by randomizer » Sun Jan 21, 2018 4:07 pm

I use Google Docs for everything. Having it in the cloud is both scary and incredibly convenient. But nothing is secure these days so I try not to dwell on it too much.
87.5:12.5, EM tilt — HODL the course!

DorothyB
Posts: 69
Joined: Wed Oct 17, 2012 6:17 pm

Re: Excel for Personal Finance Accounting ?

Post by DorothyB » Sun Jan 21, 2018 4:15 pm

I use Excel for creating my budget, tracking some of my savings categories, income tax projection, etc . . . but for most of my day to day tracking, check book reconciliation for checking and credit card accounts, etc i use the sunset version of Microsoft Money which is free. I don't believe it downloads transactions, but that isn't something I want to do anyway.

User avatar
SpartanFan
Posts: 275
Joined: Tue Aug 07, 2007 10:49 am
Location: Michigan - MSU

Re: Excel for Personal Finance Accounting ?

Post by SpartanFan » Sun Jan 21, 2018 4:32 pm

michaeljc70 wrote:
Sun Jan 21, 2018 3:12 pm
I know Excel very well, but would not use it for that purpose. To generate enough adequate reports and do what you want would be more work than I would want to do. If you just want to keep a list of expenses, that wouldn't be too bad.

Why don't you just use Quicken? The download features stop working after it expires, but the rest of the application works. It is made for what you want to do.
Well, I could keep using Quicken but eventually I know I wont be able to keep reinstalling it on every new PC I happen to get over the years w/o a bunch of issues from Intuit. Just dont trust them any longer.

I only need an app for my deposit accts and a couple credit cards so I can keep it pretty simple.
"There's a crack in everything, that's how the light gets in" - Leonard Cohen

ubermax
Posts: 1451
Joined: Tue Feb 11, 2014 2:19 pm
Location: Connecticut

Re: Excel for Personal Finance Accounting ?

Post by ubermax » Sun Jan 21, 2018 4:35 pm

DorothyB wrote:
Sun Jan 21, 2018 4:15 pm
I use Excel for creating my budget, tracking some of my savings categories, income tax projection, etc . . . but for most of my day to day tracking, check book reconciliation for checking and credit card accounts, etc i use the sunset version of Microsoft Money which is free. I don't believe it downloads transactions, but that isn't something I want to do anyway.
I find Excel great for balancing the check book - our bank and probably most others allow you to download transactions into Excel/CSV - for us this is nice because it captures electronic payments as well as hard checks - I have it setup so that all disbursements whether hard or electronic are put together in one column so I can total them and balance the check book like in "olden" times ; for the credit card I download transactions into Excel in between statements just for the purpose of checking off purchase receipts so that they don't pile up - we keep receipts for a few days , another "olden" times habit :happy

Chief_Engineer
Posts: 34
Joined: Wed Jan 15, 2014 9:32 pm

Re: Excel for Personal Finance Accounting ?

Post by Chief_Engineer » Mon Jan 22, 2018 9:50 am

I'll put in a third recommendation for GnuCash. I can't list pros and cons because it's the only accounting software I've ever used. But it's flexibility is unmatched. If you're familiar with Python, there's a package (piecash) that lets you access the GnuCash file and process the data any way you want.

GLState
Posts: 141
Joined: Wed Feb 15, 2017 10:38 am

Re: Excel for Personal Finance Accounting ?

Post by GLState » Mon Jan 22, 2018 10:01 am

SpartanFan wrote:
Sun Jan 21, 2018 4:32 pm
michaeljc70 wrote:
Sun Jan 21, 2018 3:12 pm
I know Excel very well, but would not use it for that purpose. To generate enough adequate reports and do what you want would be more work than I would want to do. If you just want to keep a list of expenses, that wouldn't be too bad.

Why don't you just use Quicken? The download features stop working after it expires, but the rest of the application works. It is made for what you want to do.
Well, I could keep using Quicken but eventually I know I wont be able to keep reinstalling it on every new PC I happen to get over the years w/o a bunch of issues from Intuit. Just dont trust them any longer.

I only need an app for my deposit accts and a couple credit cards so I can keep it pretty simple.
Quicken isn't owned by Intuit any longer.
https://www.computerworld.com/article/3 ... uyout.html

User avatar
midareff
Posts: 5792
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Excel for Personal Finance Accounting ?

Post by midareff » Mon Jan 22, 2018 10:04 am

runner540 wrote:
Sun Jan 21, 2018 9:28 am
I've been using Excel for about 10 years. Have built a template for tracking everything I want to (expenses versus budget, net worth on a quarterly basis, estimating taxes, etc.) If you know what you're doing in Excel, all you need to enter is the transaction date, amount and category, then Excel can do the rest if you've set up a template. I start a new file in January of every year, and tweak my template and budget.

Only downside: I keep it on my home computer, so I have to find time to sit down and update it when I'm at home. Can't do it on the go.
Two thoughts... can't you keep the sheet in Dropbox and update it on an excel equipped phone?

Or, email it back and forth to your phone.

User avatar
SpartanFan
Posts: 275
Joined: Tue Aug 07, 2007 10:49 am
Location: Michigan - MSU

Re: Excel for Personal Finance Accounting ?

Post by SpartanFan » Wed Jan 24, 2018 6:33 am

pezblanco wrote:
Sun Jan 21, 2018 3:21 pm
GnuCash has been an absolute revelation for me and for my partner who keeps not only personal accounts but accounts for a thriving small business.

It is open source and has an active user base. Highly recommended if you want to avoid the cloud based, user fee, model that so many computer applications are going to.
Does this app work with Stock quotes in Window 10 ?
"There's a crack in everything, that's how the light gets in" - Leonard Cohen

User avatar
oldcomputerguy
Posts: 3430
Joined: Sun Nov 22, 2015 6:50 am
Location: In the middle of five acres of woods

Re: Excel for Personal Finance Accounting ?

Post by oldcomputerguy » Wed Jan 24, 2018 7:15 am

rkhusky wrote:
Sun Jan 21, 2018 7:59 am
I'm sure you could find an Excel-based (or other spreadsheet-based) personal finance package on the web, but you could also try GnuCash, which is a free accounting package.
To the OP, another option you might consider is Moneydance. It's not free, but you can download a trial version and play around with it to see if it works for you, then buy a license key for $50.
It’s taken me a lot of years, but I’ve come around to this: If you’re dumb, surround yourself with smart people. And if you’re smart, surround yourself with smart people who disagree with you.

annielouise
Posts: 343
Joined: Wed May 14, 2008 4:11 pm

Re: Excel for Personal Finance Accounting ?

Post by annielouise » Wed Jan 24, 2018 7:47 am

SpartanFan wrote:
Sun Jan 21, 2018 4:32 pm
michaeljc70 wrote:
Sun Jan 21, 2018 3:12 pm
I know Excel very well, but would not use it for that purpose. To generate enough adequate reports and do what you want would be more work than I would want to do. If you just want to keep a list of expenses, that wouldn't be too bad.

Why don't you just use Quicken? The download features stop working after it expires, but the rest of the application works. It is made for what you want to do.
Well, I could keep using Quicken but eventually I know I wont be able to keep reinstalling it on every new PC I happen to get over the years w/o a bunch of issues from Intuit. Just dont trust them any longer.

I only need an app for my deposit accts and a couple credit cards so I can keep it pretty simple.
One thing that I found helpful was to make a quick checklist at the top of each tab on how to download transactions from that site. In nearly all cases I found the .csv the most useful (even when an excel option is offered).

marklar13
Posts: 99
Joined: Wed Jul 08, 2015 11:47 am

Re: Excel for Personal Finance Accounting ?

Post by marklar13 » Wed Jan 24, 2018 8:06 am

For budget, I use Google sheets; essentially same as Excel.

I have my bank and credit cards on mint, and then each month I just download and paste into a transactions tab. I have another tab that maps the mint categories into categories I actually want to track, and then the budget keys off of these.

This let's me group and track things the way I want, and also let's me keep the mint categories separate in case I ever want to review at a lower level.

Took a long time to set up the way I wanted, but now I just have to download the transactions every so often (and possibly map a new spending item here and there if mint records it as something I haven't set up yet).

User avatar
pezblanco
Posts: 477
Joined: Thu Sep 12, 2013 8:02 pm

Re: Excel for Personal Finance Accounting ?

Post by pezblanco » Wed Jan 24, 2018 9:30 am

SpartanFan wrote:
Wed Jan 24, 2018 6:33 am
pezblanco wrote:
Sun Jan 21, 2018 3:21 pm
GnuCash has been an absolute revelation for me and for my partner who keeps not only personal accounts but accounts for a thriving small business.

It is open source and has an active user base. Highly recommended if you want to avoid the cloud based, user fee, model that so many computer applications are going to.
Does this app work with Stock quotes in Window 10 ?
Yes.

User avatar
Sid
Posts: 28
Joined: Mon Mar 05, 2007 12:11 pm
Location: Central Texas

Re: Excel for Personal Finance Accounting ?

Post by Sid » Wed Jan 24, 2018 1:49 pm

I'm in the same situation as the OP, except I used MS Money until it went extinct, then switched to Quicken. I will not pay a subscription and keep my data on their server.

I might try GnuCash based on the comments here, but I am actually considering simplifying my life by not tracking every transaction anymore. I currently download or enter every transaction whether it is checking, credit cards, investing, etc. But I ask myself why? Why not just go to the latest Vanguard statement or website if I want to know what is up with my Vanguard investments. In 40 years of doing this I have never found a mistake in my bank or investment statements. So why worry about it. I have had fraudulent credit card charges, but the credit card company actually spotted those and alerted me.

If I can control my OCD tendencies, I may just stop tracking every trans action in some separate program or spreadsheet app.
Sid

mptfan
Posts: 4714
Joined: Mon Mar 05, 2007 9:58 am

Re: Excel for Personal Finance Accounting ?

Post by mptfan » Wed Jan 24, 2018 1:53 pm

thangngo wrote:
Sun Jan 21, 2018 10:05 am
I design my excel spreadsheet for personal finance from the ground up. ...

My background are in both finance and software engineer so this is my "playground".
Me too. I am not a software engineer, but I do have a degree in finance and I enjoy crunching numbers and "playing" with spreadsheets, and I have also created my own spreadsheets from the ground up. I have all my personal financial transactions and data going back 8 years in various Google Sheets.

The benefits to me are that I can design my spreadsheets any way that I want and I own the data so I am not trapped into using a certain proprietary software for the rest of my life and subject to their constantly changing and increasing subscription fees.

mptfan
Posts: 4714
Joined: Mon Mar 05, 2007 9:58 am

Re: Excel for Personal Finance Accounting ?

Post by mptfan » Wed Jan 24, 2018 1:58 pm

Sid wrote:
Wed Jan 24, 2018 1:49 pm
I currently download or enter every transaction whether it is checking, credit cards, investing, etc. But I ask myself why? Why not just go to the latest Vanguard statement or website if I want to know what is up with my Vanguard investments. In 40 years of doing this I have never found a mistake in my bank or investment statements. So why worry about it.
One man's worry is another man's hobby.

turno77
Posts: 40
Joined: Tue May 14, 2013 2:45 pm

Re: Excel for Personal Finance Accounting ?

Post by turno77 » Wed Jan 24, 2018 2:18 pm

I also built an Excel 'register' to track our checking account. Very simple setup with 5 columns:

A: Day of week (automatically generated off column B with =TEXT(B1,"dddd"))
B: Date
C: Notes
D: Credit or Debit formatted as Number with negative numbers red
E: Balance =SUM(E1+D2), then drag down to autofill.

I compare with our bank a few times a week. I prefill several months in advance with all of the regular bills which allows me to forecast available cash. I don't have one set up for the credit card, but it could easily follow this format.

User avatar
daytona084
Posts: 823
Joined: Mon Feb 01, 2010 10:47 pm

Re: Excel for Personal Finance Accounting ?

Post by daytona084 » Wed Jan 24, 2018 3:01 pm

The OP's goal is to "to reconcile and maintain our deposit and credit card accounts"... If that's all one wants to do, Excel (or any other spreadsheet) can do the job with ease. With or without a template to start with.

On the other hand, to duplicate all the features in Quicken requires a relational database (which would be MS Access in the Microsoft world). It would be impossible to do this with a spreadsheet. To duplicate all of Quicken's functionality, a person would actually be "reinventing the wheel" and in the process find out why Quicken employs an army of programmers and has to charge real money for the use of its product.

ElwoodBlues
Posts: 74
Joined: Wed Jul 19, 2017 10:00 pm

Re: Excel for Personal Finance Accounting ?

Post by ElwoodBlues » Wed Jan 24, 2018 5:03 pm

GnuCash works well for me. It's free and open source, but does have a bit of a learning curve.

Also, among the multiple other threads you can find on this topic, you might find this one useful:
viewtopic.php?f=2&t=231261&p=3601485#p3601483

I love playing around in Excel too, but I find it better for what-if analysis, and other long term planning. For straight up tracking and reporting, unless you're keeping it really really simple, I would use one of the purpose built apps or services.

AntsOnTheMarch
Posts: 610
Joined: Mon May 29, 2017 5:47 pm

Re: Excel for Personal Finance Accounting ?

Post by AntsOnTheMarch » Wed Jan 24, 2018 5:46 pm

daytona084 wrote:
Wed Jan 24, 2018 3:01 pm
The OP's goal is to "to reconcile and maintain our deposit and credit card accounts"... If that's all one wants to do, Excel (or any other spreadsheet) can do the job with ease. With or without a template to start with.

On the other hand, to duplicate all the features in Quicken requires a relational database (which would be MS Access in the Microsoft world). It would be impossible to do this with a spreadsheet. To duplicate all of Quicken's functionality, a person would actually be "reinventing the wheel" and in the process find out why Quicken employs an army of programmers and has to charge real money for the use of its product.
Most of us don’t need the complexity of a relational database to keep track of personal finances. For those of us, spreadsheets are a Goldilocks solution. Once you get started with even a simple spreadsheet, it can be easy to modify and personalize. I often make modifications on the fly when I get an idea like “wouldn’t it be great if I had checksum feature next to this cell to make sure I didn’t goof up the data entry?” Interestingly, I had created a custom relational database for my finances years ago and gave it up. While it did everything and the kitchen sink, any changes required more effort than I had time for.

While quicken and other dedicated money manager programs are great in some ways, often the features are not what many users need/want—or they are not implemented in a way that suits all. The subscription model quicken gravitated to over the year makes the company money but does not serve me in the least. It does not serve me to have new features I don’t want pushed on me in mandatory upgrades that force me to deal with compatibility issues. Sticking with an outdated version has its own problems. It will eventually stop working with some new OS release and you’ll then need a dedicated legacy computer to keep it running—and eventually you won’t be able to get security updates for that computer. And while you can get data out of quicken to migrate your data, the reality is always much more complicated than that because the structure and formatting of that data usually requires major manipulating to make it useful.

There’s no free lunch. I speak as a former user of quicken and one who’s thrilled to be off the upgrade merry-go-round.

michaeljc70
Posts: 3746
Joined: Thu Oct 15, 2015 3:53 pm

Re: Excel for Personal Finance Accounting ?

Post by michaeljc70 » Wed Jan 24, 2018 6:28 pm

20+ year Quicken user here. I have had issues with Quicken (some documented on this board). I personally cannot imagine logging into each of my account's website and downloading data and importing it into Excel or manually entering transactions. It would remind me too much of the 1990s and be too big of a hassle. I have Quicken 2017 and when it expires in 2020, I will re-evaluate, but I am not going this route.

I guess if you use 1 checking account and 1 credit card it wouldn't be that bad, all but primitive.

User avatar
SpartanFan
Posts: 275
Joined: Tue Aug 07, 2007 10:49 am
Location: Michigan - MSU

Re: Excel for Personal Finance Accounting ?

Post by SpartanFan » Thu Jan 25, 2018 4:18 pm

Got this mssg today from Quicken trying to ge me to upgrade, after telling me they are stopping certain services on my 2015 version - Glad Iam not using there products anymore going forward ...

"Purchase entitles you to Quicken for 1 year, starting at purchase. Full payment is charged to your card immediately. At the end of the membership period, membership will automatically renew every year and you will be charged the then-current price (prices subject to change). You may cancel before renewal date. For full details, consult the Quicken License Agreement. You can manage your subscription at your My Account page. "
"There's a crack in everything, that's how the light gets in" - Leonard Cohen

michaeljc70
Posts: 3746
Joined: Thu Oct 15, 2015 3:53 pm

Re: Excel for Personal Finance Accounting ?

Post by michaeljc70 » Fri Jan 26, 2018 12:55 pm

SpartanFan wrote:
Thu Jan 25, 2018 4:18 pm
Got this mssg today from Quicken trying to ge me to upgrade, after telling me they are stopping certain services on my 2015 version - Glad Iam not using there products anymore going forward ...

"Purchase entitles you to Quicken for 1 year, starting at purchase. Full payment is charged to your card immediately. At the end of the membership period, membership will automatically renew every year and you will be charged the then-current price (prices subject to change). You may cancel before renewal date. For full details, consult the Quicken License Agreement. You can manage your subscription at your My Account page. "
I bought a copy of 2017 H&B for $30 and am good until April 2020.

2015
Posts: 2072
Joined: Mon Feb 10, 2014 2:32 pm

Re: Excel for Personal Finance Accounting ?

Post by 2015 » Fri Jan 26, 2018 8:11 pm

MikeG62 wrote:
Sun Jan 21, 2018 10:56 am
SpartanFan wrote:
Sun Jan 21, 2018 7:16 am
I have been a Quicken user since the old MS-DOS days but now that Quicken has gone to a annual fee based upgrade model (which I refuse to upgrade to) I would like to find a replacement to reconcile and maintain our deposit and credit card accounts that is Excel based. The simpler the better.

Any suggestions ?
Don’t know of any templates. I custom designed my multi-tabbed excel spreadsheet which suits my needs. Wrote this is a similar thread a while back...

Multi-tabbed Excel spreadsheet.

One tab is electronic checkbook register (created by me which replaces paper checkbook register). Second tab summarizes monthly credit card spending in the categories (about 8 of them) I want to track for such spending - for all CC's used that month. Third tab pulls in the checkbook data and credit card spending and summarizes them by month, including comparison to budget.

It takes time each month, but not an inordinate amount of time. Checkbook register is easiest - like using a paper register, but much easier to reconcile to bank statement (actually, I reconcile in real time periodically throughout the month - I no longer reconcile to monthly bank statement). Reviewing CC statements and categorizing into the buckets I want to track takes the most time. I am retired so I don't mind doing it. Plus, it keeps me close to our spending and avoids the need for periodic spending reviews (as the review now happens in real time and is ongoing).

...At the end of 2017, I added/built-in a number of charts and graphs - or I should say I pulled data from the spreadsheets into several pie and bar charts. Made the process of reviewing our 2017 investable assets, performance and spending with my DW “a lot” easier too. Much more receptive to looking at charts than what otherwise seems like a blizzard of numbers in a spreadsheet.
Wow, I do pretty much the same thing with my custom designed multi-tabbed excel workbook, although I'm too lazy to create (and don't really have a need for) pie and bar charts. What a time saver. I know I got the idea to consolidate my separate spreadsheets into one workbook from this forum, and it was probably from your prior thread! :beer

User avatar
ladders11
Posts: 814
Joined: Fri Nov 14, 2008 4:20 pm

Re: Excel for Personal Finance Accounting ?

Post by ladders11 » Fri Jan 26, 2018 11:12 pm

Excel 2016 has preloaded templates provided by Microsoft for this. There's a Family Budget or another called Expense Trends Budget which are pretty good.

https://templates.office.com/en-us#categories

Look under Budgets or Financial Management.

Post Reply