Is anyone using Excel for managing finances?

Topic

New Reply

[dmt_3904]

I have been a Quickbooks user for many years.  We no longer have a business and I no longer want/need to pay for QB.  It’s great software! Very functional and feature-rich.  I am not always happy with Intuit, but it’s a huge software corporation, just like Microsoft – replete with problems!  I need to perform these functions:

• Keep check registers
• Reconcile accounts
• Download credit card transactions (can download in excel)
• Create accounts to sort income & expenses
• Run reports – of course will not have the robust reports that QB has

I am aware of MS Money in Excel.  I am also aware that there are many other financial software providers out there (like Mint).  I am not willing to give up my bank account passwords to them or any third parties.  QB doesn’t save passwords. (And btw if you use those products, are you aware that your bank will not support you if there is a breach and your account is hacked- you are on your own!)

I’ve checked a few of the budget templates, but none of them seem what I am looking for.  Is anyone using Excel in for keeping finances?  How are you doing it? What template are you using, if any?  How are you doing monthly reconcile of accounts?  I know it won’t be as nice as Quickbooks, but I am hoping I can make it work for my simple needs. thanks.

• This topic was modified 4 years, 2 months ago by dmt_3904.
• This topic was modified 4 years, 2 months ago by dmt_3904.

Reply | Quote

Replies

Three questions immediately come to mind:  1)  HOW knowledgeable are you in Excel?  You many need to know various builtin functions as well as have the ability to create your own sorting functions, etc.  2)  HOW knowledgeable are you in accounting?  3)  Do you know how to use ACCESS to extract data from an Excel spreadsheet and create reports?

For what it’s worth, back in 1982, I created my own sub-chapter S corporation to do computer consulting.  Although I had plans, it never progressed beyond just myself.  As I had 2 semesters of accounting in college in the late ’60s, I set up a rudimentary full-year bookkeeping spreadsheet using Lotus 1-2-3 and later, Excel.  I used separate columns for everything with the cash account a single column with both debits and credits, then posting the opposite side in an appropriate column.  I manually calculated payroll numbers and had columns for each accrual account.   At the bottom was a continuous profit & loss statement as well as full balance sheet.  As my invoices were monthly or project ‘milestone’ based, they were few in number per year.  Crude?  Yes.  Did it work for me?  Absolutely.  I’ve attached it but first replaced all specific names with generic ones, etc.  I should note that when I created the corporation, I bought 100 shares of stock, and loaned the rest to the company for startup.  Hence, the ‘due officer’ payments.

And for what it’s worth, I’ve kept my checkbook as an Excel spreadsheet for at least the past 10 years or so.  It’s not much more than what is in a printed checkbook with columns for date, payee, amount, a ‘cleared’ note column (X) and notes.  As it’s in the ‘my documents’ folder on my computer, it gets backed up in full to the USB thumb drive on my key ring each week as well as weekly and quarterly SSD backups.

 

Reply | Quote

Oh thank you for sharing your spreadhseet! I will check it out.  Lotus 1-2-3!!!! Oh my gosh, I have not heard of that in ages, now you are bringing me back to my IBM days, heh, heh, heh.

1) HOW knowledgeable are you in Excel? You many need to know various builtin functions as well as have the ability to create your own sorting functions, etc.

I am fairly knowledgeable, been using it a long time, not an expert.  I can sort, filter, create charts, pivots.  What builtin functions are you referring to?

2) HOW knowledgeable are you in accounting?

Not very – I have been using QB as I stated, I get wrapped around the axle with accounting, but I can figure it out or ask someone.  I couldn’t do what you did to create a spreadsheet for business.

3) Do you know how to use ACCESS to extract data from an Excel spreadsheet and create reports? Do you mean MS ACCESS? No, but I think I have it in my o365 subscription.  And I could learn how to do it.

I realize my QB register is basically an excel spreadsheet.  How do you reconcile. I was thinking I’d have to manually reconcile each transaction and add up the amounts each month to balance.

 

Reply | Quote

I still use Lotus 123 and Word Pro which comes with Lotus SmartSuite ’97.  I only use these for my own home use now (Budget, keeping track of Car Maintenance, charities, grocery costs, medical costs, xmas card labels, etc.).  I used this at work years ago, I liked it then and started to use it at home when I got a free copy (an actual CD) with an IBM Aptiva computer I bought in 1997.

Being 20 something in the 70's was so much better than being 70 something in the insane 20's

1 user thanked author for this post.

dmt_3904

Reply | Quote

DMT,

You might take a look at the MS Money Sunset Edition. Its free software that will do everything you want with the exception of downloading credit card transactions.

I’ve been using it since MS abandoned MS Money years ago and it still works great. Unless y0u have a whole boat load of CC transactions its not that onerous to input them manually. The software does have a nice balancing feature you use when your bill comes in, you just enter the new balance then check off all the items on your statement when it hits zero you’re balanced and it will then place an entry in your checkbook to pay the bill.

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks, I do tend to have lots of cc transactions.  Can I import them or copy / paste?  I can download cc transactions to a spreadsheet.  I will check it out.

Reply | Quote

Tried it, got this msg: We’re sorry, this download is no longer available.
: (

Reply | Quote

[RetiredGeek]

Still available here. HTH

Edit: Note they renamed it USMoneyDixSunset.exe. However, if you right click on the downloaded file and look the Properties->Details Tab it shows that it is indeed MS Money Plus Deluxe!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

• This reply was modified 4 years, 2 months ago by RetiredGeek.

1 user thanked author for this post.

dmt_3904

Reply | Quote

I’m testing https://kmymoney.org/ as replacement for Quicken and so far impressed with it.

Win10 22H2 Pro, MBAM Premium, Firefox, OpenOffice, Sumatra PDF.

Reply | Quote

Tony, I am not familiar with it, but will take a look, thanks.

Reply | Quote

Hello dmt.
To answer your question. “Yes! Many of us are using Excel for managing finances”. Back in the late 80s I used DOS Lotus v2.1 to build the budgeting worksheets for Laidlaw Waste Systems, a North American company with about 750 offices across Canada and the USA. I thought “If they can do it, so can I!” (even though I was doing it for them)

In late 1989 I wrote myself a simple worksheet that tracked cash purchases against the contents of my pocket (all bills and coins down to the penny), and told me at the end of each day if the POCKet didn’t balance – I had forgotten to account for a coffee.

Thirty years later I use Excel2003 with a pop-up form that allows me to categorize income and outflow by 38 different accounts/types of transactions, two chequing accounts (with credit card transactions), two online savings accounts, bank loan. I have sheets “Transaction”, “Budget”, “Details”, “Cheques”, “Car rental”, “WhatFAQ”.
The monthly transaction sheet shows the 38 transaction types summarized as: “Threshold”, “Bank Fees”, “Clothing Purchase”, “Clothing Repair”, “Communications Internet”, “Communications Telephone”, “Dental”, “Education”, “Ent/Vac Coffee”, “Ent/Vac Vacation”, “Groceries”, “Health Pharmacy”, “House Hydro”, “House Insurance”, “House Mortgage”, “House Supplies”, “Loan Interest”, “Loan Interest”, “Loan Payment”, “Meals”, “Medical Prescriptions”, “Other Expense”, “Subscriptions”, “Supplies”, “Travel Car rental”, “Travel Delivery”, “Travel Gasoline”, “Travel Taxi”., with conditional formatting and stuff that tells me when my credit card balances exceed my cash-in-bank.
All the bells and whistles.

Obviously this has been an ongoing project, but you could start with a simple sheet and add features as you need them.

One word of caution, passed on to me by my accountant 30 years ago. Introduce a manual process between the automated processes. I had written a golf-club-bar chits collection process whereby members tabs were summarized. I offered to route the collected, summarized data (PCFile at the time) directly into the bookkeeping system, but No! Rae would have a golf club clerk do that because, he said, it was important to have a human eye/brain eyeing each members bill as it was entered into the bookkeeping process. That way, he said, we can catch any obvious errors before an angry club member gets billed thousands of dollars when it should be only a hundred. He was correct.

For that reason I do not import my bank/credit card data directly. I print the monthly statement to a PDF file, then manually scroll down the PDF statement “checking off” by bolding each transaction in my sheet.

At the start of a new month, one of the many macros inserts the new month (right now “April 2021”, and calculates my budget as the twelve-month running average of the past twelve months. It is not so much a budget as a means of alerting me when my running average of any expense item is trending upwards.

And yes, like you, I am in retirement and want to maintain track of my finances.

I hope this helps
Chris

Unless you're in a hurry, just wait.

Reply | Quote

Thank you Chris.  While I can do some programming with excel, I am not as advanced as you are. But I believe I can find some examples to follow and, as you suggest,

 start with a simple sheet and add features as you need them

 

Reply | Quote

HaHaHaHaHa
But this is not a joke. This is an example of my actual usage of Excel to keep my finances.
Don’t you wish yours were this uncomplicated?

Reply | Quote

Hey, it works for you!  No need to complicate things.  It appears to be a simple check register – as you would manually keep it, right?   Which I think is fine, depending on needs/desires.  Remember how we did that in the olden days???  Heh, heh ; )  How do you do monthly reconcile? I thought I could have a column to check items off – and add them manually or using excel function.

I don’t want too much more than that – I like to check accounts and see spending – e.g. how much did I spend on groceries last week, month or year?   I also like to search for things – which I do often in QB. For example, the zipper on my handbag broke recently and I was able to find the purchase date in QB to check on the warranty (it’s out of warranty,of course!)  I should be able to do those things with Excel.  I think I will create a simple spreadsheet – I saw this on howtogeek https://www.howtogeek.com/462166/how-to-create-expense-and-income-spreadsheets/

and there was something on you tube I want to check out, plus the great suggestions here, thanks everyone, it’s much appreciated!  I was also thinking I can download credit card transactions to an excel and keep that separately, because importing into the main ss can get messy.  I know I can search for cc transactions online – but that doesn’t always work well and it’s nice to have that info handy.  For me, it’s a matter of deciding on a format, making the switch to excel and jump in!  It can be changed/modified, as Chris notes above.

 

Reply | Quote

dmt_3904 wrote:

How do you do monthly reconcile?

Another easy one. Bank balance = spreadsheet balance last day of the month Far right column is processed/outstanding charges verification. Three different symbols (per monthly) per quarter.

Credit card balance = type of transaction. One credit card = medical. Another = Internet, phone, TV, etc. Another = daily expenses (groceries, etc). I’m retired but still get the Co’s employee discount for gasoline on their branded credit card.
Download copy of each credit card statement each month. All sorted by card. Pay off the balance monthly (can’t afford usury interest rates).

Told you it was simple.
Fixed $$ income, spend it all outgo. LOL

1 user thanked author for this post.

dmt_3904

Reply | Quote

can’t afford usury interest rates

Totally agree! I pay off each month too.

Reply | Quote

quicken starter is cheaper than excel and Ive been using quicken since DOS version. And I am not adept at Excel! Disliked when Intuit sold out but too bad for me

1 user thanked author for this post.

opti1

Reply | Quote

Quicken is not cheaper than LibreOffice Calc. I’ve used Calc to run complex accounting spreadsheets written in Excel without issue for years. The only thing it doesn’t do is run VBA.

cheers, Paul

Reply | Quote

Right Paul but libre Office Calc is a spreadsheet not so much different from excel. So even tho it is free, I am still ill equipped and not inclined to tackle the learning curve.

Reply | Quote

Like @RetiredGeek, I use MS Money Sunset Edition.  Before that, I used MS Money Deluxe for many years until it was “sunset”.  My Money accounts go back to 2000.  I’m a member of Penfed (Pentagon Federal) Credit Union where I also have my most-used credit card.  Penfed offers transaction downloads of any/every account in .OFX format (the file type Money uses), so I have a Money account set up for checking, savings, and credit card.

I also have an Excel spreadsheet setup as budget reconciliation and a handy double-check on Money’s bookkeeping.  When my Penfed statements (the 10th for checking/savings, 20th for credit card), Money and Excel are all in agreement, I’m confident that my accounts are reconciled.

I am also on a fixed income, and the Excel spreadsheet helps me keep track of which payments are due when (all but three are on autopay).  I check my accounts at Penfed daily, and download a cumulative file when any withdrawal shows up.

A very nice benefit of my Penfed credit card is that I get unlimited 2% cash back on any purchase, and Penfed encourages use of their credit card for monthly bills.  I use mine for almost every purchase.  On the 25th of every month, the earned cash back can be automatically transferred into my savings account.  I’m averaging $30/month in “reward” cash.  I don’t carry a balance on any credit card, so I don’t pay any interest.

Always create a fresh drive image before making system changes/Windows updates; you may need to start over!

We all have our own reasons for doing the things that we do with our systems; we don't need anyone's approval, and we don't all have to do the same things.

We were all once "Average Users".

Computer Specs

1 user thanked author for this post.

DriftyDonN

Reply | Quote

bbearren wrote:

I get unlimited 2% cash back on any purchase

Surely this means the card vendor is charged at least 3% on the transaction, so you have already paid the money – the credit union aren’t giving you money for nothing.

cheers, Paul

Reply | Quote

[bbearren]

Paul T wrote:

bbearren wrote: I get unlimited 2% cash back on any purchase.

Surely this means the card vendor is charged at least 3% on the transaction, so you have already paid the money – the credit union aren’t giving you money for nothing.

Not quite, you’ve got that a bit backward.  Transaction fees are paid by the merchant to the credit card company.  Have you ever noticed that many gas stations have two prices advertised, cash price and credit card price?  The cash price usually 5¢ cheaper than the credit card price.  There’s a reason for that.

So no, I haven’t paid the money, the merchant has.  Typically dry goods and grocery merchants don’t have discounted prices for cash buyers, so they make up the transaction fees through all their customers, both cash and credit.

bbearren wrote:

I don’t carry a balance on any credit card, so I don’t pay any interest.

I use my credit card to make purchases for which I would otherwise use my debit card.  I’m spending the same amount of cash, just deferring the payment to the credit card statement date.  The funds are in my checking account, and a simple online transfer pays my credit card balance before the due date, hence, no interest is charged.

My credit union charges merchants transaction fees on both credit cards and debit cards, but the transaction fees that merchants pay is a bit higher for credit cards, so my credit union encourages use of their credit card versus debit card.

And again, I’m only deferring what would otherwise be cash/debit card transactions, and paying the balance due before the due date.  So my rewards cash for credit card purchases is only in very small part (transaction fees spread amongst all customers whether credit or cash) money I have spent.

If I were to use cash only, I would spend the same amount of money (to the penny), but get nothing back at the end of the month.  The simple trick is to never carry a balance past the due date and incur interest charges.

RetiredGeek wrote:

Those who understand the game take the cash back and pay off their bill every month and win the game.

I’m winning the game every month.

Always create a fresh drive image before making system changes/Windows updates; you may need to start over!

We all have our own reasons for doing the things that we do with our systems; we don't need anyone's approval, and we don't all have to do the same things.

We were all once "Average Users".

Computer Specs

• This reply was modified 4 years, 1 month ago by bbearren.

Reply | Quote

Paul,

Cash Back is nothing but an inducement to go into debt and pay high interest rates far exceeding the cash back. Those who understand the game take the cash back and pay off their bill every month and win the game. Unfortunately, few people understand this so the card issuers make a fortune by using this inducement. It’s old but it’s true “there is no thing as a free lunch” or free anything for that matter.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

bbearren wrote:

Transaction fees are paid by the merchant to the credit card company

Yep, that’s what I meant.

bbearren wrote:

The cash price usually 5¢ cheaper than the credit card price

Where I live / visit that’s illegal – at least I think it is because it’s never done. The advertised price is the price, CC or cash. Maybe it’s a US specific thing?

cheers, Paul

Reply | Quote

Paul T wrote:

bbearren wrote: Transaction fees are paid by the merchant to the credit card company Yep, that’s what I meant.

You are correct in that Penfed Credit Union is not giving me money for nothing.  They are giving me what they advertise as “Reward Cash” for using the credit card so that they can charge transaction fees to the merchants where I use my credit card.

What I meant is that

bbearren wrote:

If I were to use cash only, I would spend the same amount of money (to the penny), but get nothing back at the end of the month. The simple trick is to never carry a balance past the due date and incur interest charges.

The thing about credit unions is that they are owned by the members (like me), they are not a separate entity unto themselves, and so their “bottom line” comes down to returning the best value to the owners (members like me).  The “Reward Cash”, for example.  They make money on the transaction, and give me some of that money they earn.  The credit card interest rate on any unpaid balance is 5.75%.  As for bank credit cards:

I haven’t used a bank in over 47 years.  And cash discounts for gasoline here in the US is legal.  The gasoline distribution companies that own the stations actually save money by giving the cash discount, particularly after gasoline prices became so high.  The cash discount is “per gallon”, whereas the credit charge transaction fee is a percentage of the total purchase.

And yes, I use Excel as a quite useful budgeting tool.

Always create a fresh drive image before making system changes/Windows updates; you may need to start over!

We all have our own reasons for doing the things that we do with our systems; we don't need anyone's approval, and we don't all have to do the same things.

We were all once "Average Users".

Computer Specs

Reply | Quote

[dmt_3904]

So, I tried a couple different methods.  Not quite happy yet : )

I just exported my check register from Quickbooks into an excel.  I think that may be best for me, however, I don’t understand the function/rule it is using  =ROUND. Which, according to the internet, is an Excel formula that rounds numbers 1-4 down and 5-9 up.  My balance is correct, but I don’t get it.  When I round, I change the last digit to a 0 – e.g. $12.54 = $12.50.  $12.58 = $12.60.

This is from the excel =ROUND(G40+F41,5).  The formula is in the last column. Also, what is the ,5?  That is in every line of the formula.  See attached sample (fake data, of course).  Can someone help explain? I don’t really want to use it if I can’t understand the formula.

*Sorry look @ 2nd jpg.  I could not delete the 1st. I neglected to show amounts that were not even numbers, to make the point about rounding.

• This reply was modified 4 years, 1 month ago by dmt_3904.
• This reply was modified 4 years, 1 month ago by dmt_3904.

Reply | Quote

Look at the formula in my attachment above.
The formula is relative, so you can copy/paste it down the “Balance” column.
It’s simple, but it works.

Reply | Quote

ok thanks

Reply | Quote

Format your colums as numeric, two decimal places.

Reply | Quote

Keep in mind that formatting a cell to 2 decimal places only affects the DISPLAYED value. It has no effect on the value used to CALCULATE. This a common mistake and then people can’t understand why the Sum of the column or row doesn’t equal the displayed values. When ever doing calculations (especially division or multiplication by decimal values) enclose it in a Round() function to make sure the calculations match the displayed values.

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek wrote:

Keep in mind that formatting a cell to 2 decimal places only affects the DISPLAYED value. It has no effect on the value used to CALCULATE. This a common mistake and then people can’t understand why the Sum of the column or row doesn’t equal the displayed values. When ever doing calculations (especially division or multiplication by decimal values) enclose it in a Round() function to make sure the calculations match the displayed values.

I haven’t found that to be an issue, since my spreadsheet doesn’t do any multiplication or division, only adding and subtracting.

Since the input is only to two decimal places, the addition and subtraction is only to two decimal places.  No rounding necessary.

Always create a fresh drive image before making system changes/Windows updates; you may need to start over!

We all have our own reasons for doing the things that we do with our systems; we don't need anyone's approval, and we don't all have to do the same things.

We were all once "Average Users".

Computer Specs

Reply | Quote

The proper Excel formula to round to US Cents is =Round(Value,decimalplaces). Thus, to round 12.578 to 2 decimal places you would use =Round(12.578,2) resulting in 12.58.

It only rounds the digit after (to the right of) the number of decimal places you specify. In this case the 8 is rounded up (>=5) to result in the 7 being turned into an 8.

If your number had been 12.574 the result would to be round down, thus dropping the 4 and resulting in 12.57.

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote