Direct Investment Register Export to Excel .xsls files?

wsalomon

Current version R52.33 for Windows

I am trying to export an Investment Register to find account errors since the Account's inception (2017).

This Fiduciary does something very annoying - for every Buy/Sell/Misc Exp/Cash Dividend) there is a "paired" "Cash Deposit Account" share Purchase/Sale of x.xx shares (at $1/sh) representing the $x.xx involved. I could keep track of these as a Security (with no CUSIP or standard ticker ID), but then I would have a perpetual Cash Balance of $0.00 (not what I want to see at the bottom of the Investment account Register, or the Account in the Portfolio view).

What I do after a download is:

1. Delete all these unneeded "paired" transactions associated with Security Buy/Sells, Account Withdrawals/Transfers, Investment Management Fees, "Cash Dividends", Fed/State Withholding payments, etc.
2. Re-categorize Cash Dividends from the "Cash Deposit Account" as Interest Income (IntInc) as reported on my 1099 DIV/INT

Most transactions can be exported to Excel as a .csv (Comma Separated Variable) file which is the saved and manipulated as a .xlsx file. As intermediate steps, the "Cleared" (C) Column is deleted, and "$ Amount" and "Cash Balance" columns are formatted as 2 decimal place columns with "," thousand separators.

I also do a "sanity check" on Quicken's running Cash Account Balance to compare to that from my Fiduciary.

My question is: Is there a direct to .xlsx Export without going through the CSV clean-up?

Thanks in advance.

q_lurker

I assume you are starting from a "Print Register" prompt. Would a BANKING Transaction Report give you enough of the same information?

If so, those types of reports can be copied to Excel (Copy and Paste operations) rather than the csv file operations. I have always found the Copy Paste process smoother and simpler. You can easily customize the report to exclude Tags, memos, categories, and to use only the desired account(s).

When pasting into Excel, you are pasting in at a specific cell location and all the formatting, column widths, etc. are maintained. So if you have a base Excel sheet setup, copying in the latest data becomes very effective right away.

The major missing feature form the Banking transaction report is often the Running Balance so that might be a deal breaker for you.

mshiggins

It sounds like a lot of work to repeatedly delete or replace downloaded transactions. After the clean up activity, how many transactions do you end up with on average per month?

wsalomon

Q - Correct - starting with the Print Register. I did try the Transaction Report, but as you note, the Running Cash Total was not there. Part of the reason for running it is to make sure it matches my on-line Fiduciary downloads to make sure no transactions are missing ("stalled" downloads do happen), and I think is one of the reason all 4 accounts were corrupted. To find missing transactions, I take the Quicken dump and Fiduciary dump (which is a some complete pain to remove the paired transactions), and put the two reports side-by-side in Excel to find out where the error(s) happen.

When trying to remedy this, there were 600-800 transactions in each account, hence a "programmatic" approach was far more effective than matching from paper statements. Fortunately, in 3 accounts, the errors (and there were multiple) happened in the last 17 months (the farthest back on could pull data). Some of the other account errors happened more than 17 months ago, so I am working with the Fiduciary to do a complete dump. (I mean, how difficult can it to do a simple SQL query from a transaction database? Unless (of course) some of the older data is not "live" and has to go back to pulling Back-up Tapes? No telling about these back-office things where there may be millions/billions of historic transactions.)

It clearly points out why frequent back-ups are crucial to catching and reversing errors early.

M - You're right. This is a considerable amount of work I never had to deal with back with the "Brand F" 900 lb brokerage gorilla that was pretty bullet-proof. There are about 40-50 "paired" transactions for each of 4 accounts a month (2 taxable Brokerage, 2 IRA). I download every other day or so, and such go through the Register doing "Ctl-D".

mshiggins

I was thinking if you are deleting so many transactions each month, it might be easier to manually enter your transactions than downloading and deleting. There are also alternative transaction import methods, like QIF that might be less work.