+ Reply to Thread
Results 1 to 5 of 5

Creating an Account Balance chart using a PivotTable from an imported set of Bank data

  1. #1
    Registered User
    Join Date
    01-28-2020
    Location
    Newbury, Berkshire
    MS-Off Ver
    Office 365
    Posts
    4

    Creating an Account Balance chart using a PivotTable from an imported set of Bank data

    Hi Chaps,

    There are a few old posts around stuff like this but nothing that i can find that seems to get to the crux of what the problem i am having...

    Bottom line - I want to be able to create a chart showing the balance in my bank account(s) over time

    Project Outline:
    - I have bank data imported into my Excel sheet through Power Query that can be updated every month by adding in a new account csv and it automatically refreshing the data in my sheet... works a treat - so i have all my account transactions from all my bank accounts from a number of banks i have my accounts in, put into a lovely data table... that includes the balance after each transaction from any of the accounts...
    - I want to create a pivotchart from this data such that i can select any of my accounts and it then plot the balance of that account over time in a pivot chart

    I cant seem to get the right format of the pivot table in place in the right way to just simply plot balance against date but it keeps summing the balances for each day. ... I have attached a screenshot of the type of simple format i think i need in Excel (and summaried below)

    "Filter" for Account Name at the top, then a list of dates for the "rows" and for each date a balance for that day that goes in the "Values" field...
    ____________________
    Account Name | drop down of account
    __________________________________________

    DAte | Balance
    -
    -
    -
    -
    -

    nothing complicated about that but i might be missing a REALLY simple trick here to just get the data in the right table output...i think i just need a way for it to display the Balance as the number as it appears rather than the "sum" of the balances that it displays by default. there is nothing in the Field Settings which allows me to just display the values

    There are a number of posts I have found on topics like this that require an original statement of balance to start and then to plot the on-going balance by calculating it through transactions made but i'd like to see if i can get it all run directly from my imported account data on its own if i can so i have a minimum of additional stuff going on...as i have the information all there in front of me with the imported data anyway.

    Thanks in advance...
    Ian
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Creating an Account Balance chart using a PivotTable from an imported set of Bank data

    Pivot tables are meant to summarize data, not show time series of data. I am assuming that each transaction for the day includes the balance after the transaction is made, so you have multiple entries for some days and zero entries for others.

    The easiest approach would be to use either MAX or MIN as your data setting - the order of withdrawals within a day is essentially meaningless. But you will still be missing days where you have no data. If you want to see a true time progression, use a table with formulas where you enter the starting date of interest and the account, and use formulas like

    Starting Date Acct StartingBalance

    =StartDate =IFERROR(1/(1/MINIFS(Balance column, Date Column, DateCell, Acct column, Acct)),CellAbove) =IFERROR(1/(1/MAXIFS(Balance column, Date Column, DateCell, Acct column, Acct)),CellAbove)
    =CellAbove +1 Copy from above Copy from above
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-28-2020
    Location
    Newbury, Berkshire
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Creating an Account Balance chart using a PivotTable from an imported set of Bank data

    yeah i was hoping there would be a simple way around it having got the import from csv all nicely arranged, then find a way of using the pivot filters to extract the balance information for any account against a run of dates...

    The min and max approach kinda works but it looks messy when i added in dates (though the table options) including no data to plot...

    I might need to look at using formulas on the table similar to how you mention although not used that logic before so i'll need to have a look again...

    Thanks for the response and i might drop you a message back on your logic if i get stuck if thats ok...

    Thsnks again

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Creating an Account Balance chart using a PivotTable from an imported set of Bank data

    If you run into any issue interpreting my post and applying it to your workbook, I or someone here will help you out.

  5. #5
    Registered User
    Join Date
    01-28-2020
    Location
    Newbury, Berkshire
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Creating an Account Balance chart using a PivotTable from an imported set of Bank data

    All sorted... i used SUMIFS as i wanted to get a breakdown of the individual daily transactions in a report rather than just an MAX or MINIF on the balance along (with IFERRORS to capture problems)

    Thanks for your help... all sorted and have also managed append as many accounts into the report as i want to get an single or combined set of statements...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-07-2020, 12:43 AM
  2. How do I create a rolling bank account balance?
    By Dubby20 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2013, 09:08 AM
  3. Replies: 3
    Last Post: 01-04-2013, 03:45 PM
  4. [SOLVED] Excel 2007 : Find bank balance in bank statement
    By Baldev Kumar in forum Excel General
    Replies: 5
    Last Post: 07-05-2012, 02:58 PM
  5. bank account balance
    By kc27315 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-08-2008, 02:48 PM
  6. [SOLVED] automatic entering Balance at every entry in my bank account
    By Sena in forum Excel General
    Replies: 2
    Last Post: 08-10-2005, 01:05 PM
  7. What is the formula for bank account row balance column
    By Hazeldean in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1