Enhancement idea: Excel or other spreadsheet input

I like the ability to import bank statements. Seems to me that really
you could use “import bank statements” to make batch entries, even if
not downloaded from a bank.

What you cannot do are entries that have inventory. For example, I
cannot make a CSV file to enter a batch of cash sales transactions,
because have to specify which inventory items are sold. Nor can I make
a CSV file to enter a batch of wholesaler purchases because I would
need to specify which inventory item. I think all I can specify is the
date, payee, reference, description, and amount. And because Manager
Accounting can link the description to an account, the account to
debit or credit is also part of what I can specify.

What would be nice is to be able to specify not just the account but
also the inventory item.

The Enhancement Idea:

What would really be nice is everywhere that you can enter single
transactions, you should be able to batch load the same transactions
from a CSV or TSV file.

Everywhere would include Journal Entries, Inter Account Transfers, Cash
Account Transactions and most anyplace else you could enter data. This
ability to enter batch transactions wherever you can enter a single
transaction would be pervasive throughout Manager Accounting.

The ability to make batch entries necessitates also providing batch
delete and providing a batch export. Batch update is not as necessary
if you can export, delete and create.

It pretty much follows what you already have for batch creating
inventory items.

Another way to provide the same functionality described is to provide
just two functions: A dump entries function and a load entries
function. The dump function would export all entries to a CSV or
TSV file. The file could be edited in Excel or a text editor, etc.
Then the load function would replace everything that was dumped.

Basically, what I am hoping is that you will do more of what you are
already doing. Manager Accounting works great with Excel. This would
be expanding a lot on what Manager Accounting already does with Excel
(and other spreadsheets).

Will be interesting if other users would like to be able to prepare
batch entries in Excel and then import all the entries at once into
Manager Accounting.

I am thinking further, if you decide to implement and implement with the second way (just two functions: dump and load). After a few years, that could be a lot of data to import and export, especially if all you want to do is add or change 5 or 10 entries. Maybe you could provide a way to have the dump and load only apply to some date range that the user specifies. Or maybe Lock Date could apply to dumps and loads. That is to say, only dump entries after the lock date. Load entries with dates on or before the lock date will be ignored.

But anyway, I would not be surprised that Manager Accounting developers come up with ways that are better than my thoughts on how to do batch entries and deletes.

i do not find how this would ease the accounting in a business.
Manager is well capable of being used as a standalone program for all the day-to-day activities of a business.
preparing everything in a spreadsheet program, then formatting it to a suitable importable format, importing the data, allocating the contents of each cell to its relevant account, etc etc etc does not look like an efficient way to run a business. you will need thrice the time than doing it in Manager directly.
Manager is an accounting software and not a simple folder to store a scanned data. there are multiple accounts involved in every transaction and then there are other dependent transactions. these cannot be solved with the use of a spreadsheet program.
the batch operation necessary to update list items of a business is already available in Manager which is more than sufficient. other operations are meant to be done within Manager.
also, the recent introduction of Form Defaults have further reduced the time needed to enter a transaction in Manager.

I agree that for a most entries, the Manager interface is far superior to a spreadsheet and to other accounting programs with which I have experience. I certainly would not want you to take away anything from this aspect of Manager.

I agree here, too. Most entries and reporting are easy in Manager and difficult in a spreadsheet.

Yes, in most cases, but I will describe what I am doing below which would be helped if I could make the entries from a spreadsheet.

Now I want to look at form defaults. But doesn’t sound like that will help with what I am doing.

I think the main argument for batch entries is that some transaction information start off as downloaded CSV files.

Here is what I am doing:
On a particular day, say Monday as an example, I sell 15 stocks and 15 options, and then buy 12 stocks and 12 options. The stock brokerage emails me notices within seconds on each transaction, but these transactions don’t include the commissions and taxes. I have to wait until the next day for that. So, on Tuesday, I download all the information for Monday’s transactions to a CSV file.

So, one thing that Manager does not do for me is calculate the price given the quantity and total amount of a stock. Because the total amounts I use include the commission and taxes, I have to take the total amount and divide by the quantity of stocks to get a price that I enter into Manager. The prices that I need to enter are like 249.2275862, for example. To do this using a pocket calculator is error prone. What I do is load the CSV into Excel and calculate the prices for all the stocks at once. Then, Manager is very close to the correct amount with only a small error. Would be nice if I could enter the exact total amount so that this amount would be exact. Then the price could be off a little instead of the total amount being off by a fraction of a penny.

Fortunately, updating my inventory items is easy. Manager lets me export what I already have into my spreadsheet. I use Excel’s database functions to compare what I already have with the stocks and options in my new transactions. Thus, I can quickly determine which inventory items are new. Then, Manager lets me quickly create all the new inventory items. I wish this kind of functionality would be available in more places in Manager.

Now that I have all the inventory items, I go to the Bank Transactions section of Manager. Here, I have some frustration. I have all the transactions downloaded from the stock broker. But I cannot use the Import Bank Statement feature because it has no provision for the CSV file to specify inventory items. If it could extract both account and inventory item from the description, that would be great.

So, I wind up working out one huge entry for all the sells,and one huge entry for all the buys. I work it all out in the spreadsheet. When I have manually copied all the entries for a Receive Money or Spend Money transaction, I calculate a check total of all the quantities because Manager does not do that for me. Manager does give me a total for all the Amounts entered. I compare the check totals with my spreadsheet’s totals. When they match, I assume I have all these numbers entered correctly and create the entry.

For me, Inter Account transfers would also be nice to load via CSV file. The Manager interface is excellent. But I am using one Manager main-business for the overall stocks and options. I have one Manager sub-business for each brokerage account. The sub-business total equity must equal the corresponding asset in the main-business. About each sub-business: All transactions are with the Cash bank account. That way, I only have the one Cash account to scan when I look for a transaction. But the Cash account is negative when I have a margin loan. So, the Cash account negative balance needs to flip to the Margin Loan account. The Inter Account transfers work great for that on an individual basis.

I was needing to make a lot of corrections to these Inter Account flipping transactions. I could work out in a spreadsheet using data exported from Manager what the net effect of the stock and option entries is and what Inter Account entry to make so that for a given month’s balance sheet, only Cash or Margin Loan would have a balance. Would have been nice to be able to batch delete all the erroneous Inter Account transfers and batch enter all the correct Inter Account transfers.

Anyway, maybe you have some ideas I never thought of to make the stock and option transactions, and the inter account transfers easier.

For a start you shouldn’t be using Inter Account Transfers, instead use a Transaction Clearing Account where the contra amounts (the inter account transfers amounts) are posted.

I would suggest that this is incorrect, as you haven’t recorded in Manager the actual purchase price. Instead enter the purchase details as they occurred and then add the commission / taxes as a separate line item, that way Manager calculates the average cost. Refer to the Freight-In Guide for adding the commission/taxes.

If you enter as per below, then you always have the exact total amount, no rounding.

Its unfortunate that Manger only rounds average cost to 2 decimals instead of 3 or 4.
For myself, I only import the cash settlement transaction, which means waiting an extra day or two. In another words. I don’t do the purchase transaction separate from the payment transaction - its combined

1 Like


I am awed that you read all my verbiage. I am thankful for your helpful insights.

I am thinking that if I use a Transaction Clearing Account, I might never have any need to use Inter Account Transfer.

Seems that a Transaction Clearing Account would work whether it is an Asset, Liability, or a Capital account. But which is proper? Maybe Capital? I think for payroll, the clearing account is “Employee clearing account”, and is a Liability.

But now you helped me think further. Maybe my Margin Loan account should not be a bank account. Margin Loan should be just a simple account in the Liabilities section. That way I can keep Cash and Margin Loan balances the way they should be without the Transaction Clearing account.

I think using the Transaction Clearing Account will be a big improvement. For example, when I pay a credit card from a checking account, both accounts are properly bank accounts. And I can use the bank statement import for both the checking account and credit card account.

~ ~ ~

That I could adjust the total amount of an inventory item by entering an amount with zero (or blank) quantity did not occur to me. I suppose I could enter a hypothetical “Buy 300 shares AAA at $22.23 per share with $9.95 commission and $0.23 tax” as 1 share at $6679.18 and 299 shares at $0 to get the same result. Even so what you described is the way to do it. I’m just realizing I can adjust what is going to happen in Cost of Goods Sold when I sell.

I don’t think the Import Bank Statement provides a way to specify inventory items.

~ ~ ~

I think payroll would be another place where would be nice to be able to import all the payslips from a TSV (tab separated values) file. I have a payroll program (PayWindow) that calculates all my employer taxes and employee withholding amounts. The output from the payroll program is a CSV (comma separated values) file, or hard copy (printed pages). I can easily convert the CSV file to TSV using Excel. TSV is a good inter-program data exchange format. I think TSV is much easier to implement than CSV. I will save why TSV is easier for some other post, someday, maybe. I already made this post too long.

Thank you for your insight, Brucanna.

Yes, especially if you are using imported statements, also use a asset or liability location, rather then as a capital account. Perhaps, locate all clearing accounts together.

This depends on the transactions, are they actual Receive and Spend Money or are they just transfers to and from the cash account. Noting that, by the use of Custom Control Accounts you can locate the Margin (bank) Account and the Credit Card accounts under BS > Liabilities.

Yes, if the buy / sell results in a profit, then make both custom accounts the same so you have the nett in the account rather then the sell under income and the buy under COGS.

So the P&L only has these two accounts for short term trades.

If you are using a payroll programme, then there is no need to duplicate the payslips within Manager. Just enter a summary of the payroll programme’s pay period transactions.

1 Like

I’m trying out converting a manager business to use the clearing account instead of the Inter Account Transfer.

So, in general I am finding the clearing account works great. The Inter Account Transfers require one entry for one transaction; the clearing account requires two entries for one transaction. So, for manual entry, seems that Inter Account Transfer is a lot less work. However, if you generally have a lot of transactions or you are starting from a CSV file, then the Bank Statement Import with the clearing account is possibly easier.

I want to check my findings on multiple currencies and Inter Account Transfers. Last year I had a couple of sellers wanted me to pay in Bitcoin. So, I had to figure out how to get some, and worry whether I could trust the entities involved. I was pleasantly surprised that Manager handles foreign currencies so easily and well.

In Manager, I created a bank account denominated in Bitcoin. However, I could not figure how to make multiple currencies work with a clearing account. I think it is not working because a clearing account does not allow Manager to know both currencies in both halves of the the transaction at the same time.

Say I buy $100 of Bitcoin to get 0.01 BTC (Bitcoin). Attempting to use a clearing account, I Credit the checking account $100, and Debit the clearing account $100. That is the first half of the transaction. The other half is to Debit the Bitcoin account for 0.01 BTC and credit the clearing account for…for what? for $100? I would be entering this clearing account transaction from the Bitcoin account which is denominated in BTC, not dollars. Looks to me like Manager has no way to know whether I mean to use $50 to acquire 0.01 BTC, or the whole $100 to acquire 0.01 BTC.

However, if I use Inter Account Transfer, this all works out well. I specify how many dollars from the checking account and how many BTC for the Bitcoin account. Manager knows everything it needs to know to make this work.

So, am I finding correctly? …that to enter a transaction in which one account is denominated in dollars as the first and base currency and the other account is denominated in BTC (or any other second currency), is not choice of which is easier. Rather, clearing account cannot work. Inter Account Transfer is the only way it can work.

Am I on the right track? or am I missing something?

Sounds correct.