Hi moneymonitors!
I'm new to some of the stuff I recommended, as I have just started using Excel again for almost the first time in over 10 years, so if I miss something in my advice hopefully others will comment on it. But I have been experimenting with the features I talked about and have extensive prior database and VBA experience, so I have a foundation.
I'm just learning about Tables and QueryTables myself. I don't quite follow the formula that you mentioned in your comment. Does your formula refer to a column with the heading "This Row" (which I would think would have the # placed more like [#[This Row]]? Or does it refer to the row # in the table without any special column having the row # in it?
You're right about QueryTables -- but a trick that is not so obvious is that you can refer to tables or data in your worksheets in your self-same Excel file using queries or SQL statements (a QueryTable has a SQL statement indicating where to get it's data). The data does not really have to be "external".
I wasn't talking about using web queries, although it might be possible. The security on bank websites could pose a problem. Also, I don't know if web queries can pull data from a CSV file -- they can pull data from a regular table on a web page, but the tabulated data for viewing in the browser might not contain the whole range of transactions you want. Basically I wasn't suggesting this
I would wait on trying to automatically get data from your bank websites.
OK, here are some steps that I think will give you a merged All Transactions worksheet for all your accounts, using a QueryTable. These steps should work in Excel 2010 (the version I am working with) but could require some modification in other versions of Excel.
1. Create a worksheet for each account, as you said, and import the data (CSV, etc.) -- make sure there are column headings.. each account can have difference headings and types of data for this method. Rename each worksheet to reflect the name of the account. For example "Bank A" and "Bank B".
2. In a text editor, you will need to write a SQL statement to merge all your bank's transactions:
Code:
a. Start by typing in a list of all the column headings each CSV file has. You can skip some of them if you know you don't need them in the QueryTable.
[code]b. Decide what name you want each column to have in the Query table. For example, in your Bank A data you have a "Transaction Date" column, and in your bank B data you have a plain "Date" column. Decide which column heading you want to use. In your text document, type the new column name several spaces to the right of the original one in your list, like:
[pre][code]Transaction Date Date
[/pre]
c.[/code] When done, you should have a list of column headings for each worksheet, with the original and new heading for each column.
d.[/code] Start with the following as a template for your SQL statement (this based on your initial post's sample data and two bank worksheets called "Bank A" and "Bank B"):
SELECT
'Bank A' AS "Account",
~Transaction Date~ AS "Date",
NULL AS "Date Entered",
NULL AS "Reference",
~Transaction Type~ AS "Type",
~Sort Code~ AS "Sort Code",
~Account Number~ AS "Account Number",
~Transaction Description~ AS "Description",
~Credit Amount~-~Debit Amount~ AS "Amount",
~Balance~ AS "Balance"
FROM ~'Bank A$'~r />
UNION ALL
SELECT
'Bank B' AS "Account",
~Date~ AS "Date",
~Date entered~ AS "Date Entered",
~Reference~ AS "Reference",
NULL AS "Type",
NULL AS "Sort Code",
NULL AS "Account Number",
~Description~ AS "Description",
~Amount~ AS "Amount",
NULL AS "Balance"
FROM ~'Bank B$'~
Code:
e. Change all the ~ characters in the template to backquotes (the backwards single quote character, usually on the same key as the tilde). This forum doesn't seem to allow typing of backquotes. The backquotes are enclosing each column name on the left-hand part of the SQL statement as well as the table/worksheet name.
[code]f. Extend the template to chave a section for each bank account using the column data you collected in steps 2a to 2c -- simply add "UNION ALL" and another "SELECT...FROM" group for each. Make sure you have all the same column headings on the right-hand side for each section. Those headings will be the headings in your merged QueryTable.
[code]g. Note that the worksheet name in the FROM clause of each section has the backquotes around it as well as single quotes inside the backquotes, and there is a $ appended to the end of the real worksheet name!
[code]h.
On the left-hand side, you can include certain types of formulas to calculate a value for the QueryTable, if the value you want doesn't exist as-is in that bank's data. Notice the example formula for Bank A's "Amount" column in the template.
i.[/code] On the left-hand side, put NULL for any columns going in your QueryTable that do not exist in that bank's data and that you don't intend to calculate.
j.[/code] On the left-hand side, you can include constant text strings, as I did to create values for the "Account" column indicating which Bank account. Include the text in single quotes (not backquotes), as I did in the template.
k.[/code] There's lots more you can do with SQL but it's a large topic. Please note that following the procedure outlined here you are using the ODBC driver (open database connectivity) to run your SQL statement. There are several flavors of SQL, and this flavor is the ODBC flavor.
3. When you have finished composing your SQL statement (you might want to save it for future reference), go back to Excel.
4. Create an "All Transactions" worksheet. Call it whatever you like.
5. Save your workbook before continuing.
6. In the blank "All Transactions" worksheet, go to the Data tab on the ribbon.
7. Click "Get External Data" on the ribbon.
8. Click "From other sources".
9. Click "From Microsoft Query".
10. Uncheck "Use the Query Wizard to create/edit queries"
11. Click "Excel Files"
12. Click "OK"
13. Browse to your current Excel file in the file list and select it
14. Check "Read Only"
15. Click "OK"
16. Close the Add Tables window
17. Click the "SQL" button
18. Paste your SQL statement in
19. Click "OK" to warning that query can't be represented graphically
20. Your data will appear, but your query was messed up due to a Microsoft bug -- http://support.microsoft.com/kb/298955 . Don't worry if what you see isn't quite right.
21. Close the window
br />22. At the "Import Data" pop up, insure "Table" is selected
23. Click "Properties" (still in Import Data)
24. Type a descriptive name in the "Connection name" box, like "All Accounts Query"
25. Make sure "background refresh" is selected (it should be)
26. Don't bother with a refresh interval or any other options; Excel seems to keep your QueryTable synchronized with changes immediately when the query is run against the current workbook.
27. Click the "Definition" tab
28. Click in the "Command text" box
29. Delete everything there, and re-paste your SQL statement there. This will unmangle it and make it work right
30. Click "OK"
31. Now back at the "Import Data" popup, make sure "Where do you want to put the data" is correct. If not, click in there and select the location for the top left corner of the table (i.e. A1) with your mouse.
32. Click "OK" to save the QueryTable.
33. If you want another column that has the row # in it from the query, allowing you to sort the QueryTable back to it's default order by sorting by that column (assuming you've re-sorted the QueryTable), you can create that column by:
Code:
a. Right click in table
[code]b. Click "Table"
[code]c.
Click "External Data Properties"
d.[/code] Put a check mark next to "Include row numbers"
e.[/code] Click "OK"
34. Format any columns that need to be formatted differently.
35. Voila! You can peruse, sort and filter this All Accounts QueryTable as you please.
If you try this I will try to help with any questions or problems you have.
P.S. In my template/demo SQL statement, I calculated a single "Amount" column from the Credits and Debits for Bank A because Bank B doesn't have separate Credit/Debit columns, and for your merged table to be most useful, you want the same type of data (i.e. transaction amount) from every bank account to end up in the same column(s). Another solution might be to leave separate Credit and Debit columns, and assume that negative Amounts are always debit transactions and Positive amounts are always Credits (accountants have their reasons for not always doing that, but...) and have formulas for all banks that don't include separate Credit/Debit columns to split off the numbers from the Amount column based on sign (<0 or >0).
Asa
[edit: I had some stray text mixed into the SQL template...]
