MayP pretty well covered it, but since you are trying to reconcile for a bank Deposit for the Month, what I found is the easiest way with no calculations is to Go to the Balance link MayP provided then export your Months Deposits to a csv file for Google sheets or Excel. I prefer excel since I have it on my desktop and do not need to upload the csv file. Once I open the file I select a cell (any cell will work with data in it), then select Insert from the top and choose a Pivot Table to a new sheet. I then Select Deposit Date (Goes in Row), Collected, Fees, Deposited all go in the Values box. Since Feburary is a short month and its a slow month for me I can show a screen shot here of what I mean your pivot table in excel should look like.
So as you can see the Dates are right in order. Dates missing might be a Weekend or Bank Holiday. On Mondays (not shown I forgot this step) if you put the Deposit ID in the box with Dates you will see each deposit from Square. So like days after a Holiday or when Banks are closed I may have 2 or 3 deposits on one day.
Sum Collected is the Amount of CC transactions Collected by Square
Fees are Squares Fees for the Summed up
Leaving you the Sum of the Deposit to your Bank account.
Now if you have multiple locations, you could throw Location in to the Filter, then in Cell A1 where it shows Location ALL you could select each individual store location instead of (ALL). I only have 1 location with Square. The other nice thing is at the bottom it shows your total Income and Fees for Square plus your Gross Income of CC sales.
I just added the Deposit ID to show you what I meant. The Yellow highlighted days have 2 deposits since Feb 4th, 11th and 18th were Sundays. So what Happens for me is my bank shows a Deposit from Sat and From Sunday first thing Monday morning the funds are either Pending or Deposited, not sure how the Bank words it. But the numbers in the Sum Deposited column Match my Bank Statement.
Hint if you ever take out a Square Loan you can Add the Type in which will break out fees that go to the loan too. The only box NOT to put this in is the Value box. This is because it is a Negative and Excel reports it as 0 for sum reason. All other boxes in the lower right of the Image of the Pivot table work and will show those Payments too.
Hey there @Dimitri2, thanks for reaching out, this is a great question! I am going to provide my suggestions on how you can keep accurate reporting, but I will tag some of our super sellers at the bottom who can provide more insight on how they do it.
To be fully transparent you will need to use multiple reports. I know it's not ideal but that to me is just the only way it can be done. I also like to look at the reporting from the Dashboard, not the app, It's easier since you have to reference multiple reports to have each report on a different tab on your computer.
1) The first thing I advise is to look at the transactions for the day. I make sure the payments are filtered by card payments only, since we do not deposit cash and other tender. When you do that it will show you how many payments you processed for that day,
2) Then I go into the balance report you can filter the report by day, month, and year. I would filter for the day and click on your transfer report. On the report, it will show all the payments that were sent to your bank for the day. So you should see on there the four payments you processed for the day it will also show you the total collected and the fees. The only time a payment won't show is if you took the transaction after your close of business day. This process will help you track down and make sure each payment is sent to your bank account. If you just want to see the overview of your sales and fees you can use the Sales Summary Report. If I am missing anything please let me know @Candlestore @TheRealChipA @ryanwanner @PartyManiaMD. I hope that helps! 🤞
MayP pretty well covered it, but since you are trying to reconcile for a bank Deposit for the Month, what I found is the easiest way with no calculations is to Go to the Balance link MayP provided then export your Months Deposits to a csv file for Google sheets or Excel. I prefer excel since I have it on my desktop and do not need to upload the csv file. Once I open the file I select a cell (any cell will work with data in it), then select Insert from the top and choose a Pivot Table to a new sheet. I then Select Deposit Date (Goes in Row), Collected, Fees, Deposited all go in the Values box. Since Feburary is a short month and its a slow month for me I can show a screen shot here of what I mean your pivot table in excel should look like.
So as you can see the Dates are right in order. Dates missing might be a Weekend or Bank Holiday. On Mondays (not shown I forgot this step) if you put the Deposit ID in the box with Dates you will see each deposit from Square. So like days after a Holiday or when Banks are closed I may have 2 or 3 deposits on one day.
Sum Collected is the Amount of CC transactions Collected by Square
Fees are Squares Fees for the Summed up
Leaving you the Sum of the Deposit to your Bank account.
Now if you have multiple locations, you could throw Location in to the Filter, then in Cell A1 where it shows Location ALL you could select each individual store location instead of (ALL). I only have 1 location with Square. The other nice thing is at the bottom it shows your total Income and Fees for Square plus your Gross Income of CC sales.
I just added the Deposit ID to show you what I meant. The Yellow highlighted days have 2 deposits since Feb 4th, 11th and 18th were Sundays. So what Happens for me is my bank shows a Deposit from Sat and From Sunday first thing Monday morning the funds are either Pending or Deposited, not sure how the Bank words it. But the numbers in the Sum Deposited column Match my Bank Statement.
Hint if you ever take out a Square Loan you can Add the Type in which will break out fees that go to the loan too. The only box NOT to put this in is the Value box. This is because it is a Negative and Excel reports it as 0 for sum reason. All other boxes in the lower right of the Image of the Pivot table work and will show those Payments too.
