How does Inventory by Location Report work for Investment Securities?

1

I’m trying to match the quantities of securities in Manager with various investment accounts.

I find Manager very, very flexible so there is generally more than one way to use Manager for the same purpose. Maybe you can tell me whether what I am doing is needlessly complex when there might be a simpler way.

I have Bank Accounts, Inter Account Transfers, and Inventory Items enabled.

I have one Manager Bank Account for each Stock Brokerage Account. If the balance is Debit (or positive), then I have cash in my account. If the balance is Credit (negative), then I have a Margin Loan. I find this simpler than having a Cash account in Balance Sheet Assets and another Margin Loan account that appears in Balance Sheet Liabilities.

Manager makes reconciling with an Excel spreadsheet very easy: I download from a bank my account transactions. I export from Manager my transactions for that bank account. I do some database types of actions. I group by amounts and count the number of each amount. I do the group action for both sets of data and then compare. A perfect match of each amount and its count proves a match. When the two sets of data do not match, I have a list of amounts and counts that only appear in the bank’s records, and a list of amounts and counts that only appear in Manager. Manager makes it easy to search for those entries that only appear in Manager.

Now that I turn to my investment accounts, seems I can do the amounts comparison the same as with any bank account. The investment accounts are different, though. I need to check that the number of shares matches.

So now, I am searching for where I ought to get the data in Manager to reconcile the number of shares (or options or bonds, etc.). Exporting from the Inventory Items tab does not work because it does not say the location. Analyzing the Batch Update data does not work because it doesn’t show the location except for starting balances and because the location is given as a GUID, so I still don’t know the location.

In Reports, looked like the Inventory by Location might be what I want. I was a little startled that I do not need to create a report. Looks like there is no way to specify the reporting as of a particular date. It does not show the transactions, just the total quantities.

But all this may be okay. I can download the present account positions (which are also totals) from the stock broker. I can match against these totals in Manager. And Manager makes drilling down to the transactions easy.

To really communicate clearly, I might have to write a better description of what I am doing with examples, maybe a pamphlet. However, I would rather not spend the time writing the equivalent of a pamphlet, and I am sure you would not want to spend the time reading it and working through it. What I am hoping is that you can get a rough idea (but good enough idea) of what I am doing in my reconciliations.

Maybe you get a good enough idea what I am trying to do to recommend using the Inventory by Location Report or some other Manager source of data so I can make sure I have entered the number of shares is correct.

2

Have you created different inventory locations and / or inventory control accounts.
You could create an inventory location for each stockbroker / investment account.
Also you could create an inventory control account for each stockbroker/ investment account.
Then you could have both quantity and value balances in which you can reconcile to both the stockbroker / investment accounts

Then why don’t you also import those transactions into Manager (which could be weekly), in the place of doing manual entries and reconciling. Manager is just a record of transactions, if one needs to know the actual balances during the period then you can just look up the bank or stock broker account balances.

If your transactions are mostly financial (receipts and payments) then your balances would almost be in balance as you are using the same data as your broker. The only time inventory quantities can get out of tilt is when you receive DRP shares or other bonus issues.

3

Without casting doubts on Manager’s ability to keep track of personal investment accounts, can I suggest you have a look at Microsoft Money - you can download it here https://www.microsoft.com/en-ie/download/details.aspx?id=20738

I use it to keep track of my investments - you can also download an add-on which will update stock prices automatically (for a small cost) - Gaier Software - Restore Microsoft Money Online Quotes with MSMoneyQuotes

4

Brucanna, you increased my confidence. I did create a location for each investment account. It’s very easy to make the entries and specify a location. And I see a great advantage. I can see in Manager how much of a stock I have in a particular account and the total of the stock in all accounts.

I’m using both manual entry and batch entry. The Manager user interface is so efficient that cloning a similar transaction, updating the date and amount is very fast. I need to have several similar transactions for a batch entry to save time.

Sometimes I do have a lot of transactions. I enter one so that I have the right account GUIDs and everything else. Then I take the Batch Update (not the Batch Create), to paste it into an Excel spreadsheet. I remove the right-most column (the Key column) to get the Batch Create form. I delete all lines except the headings and the one transaction I entered as an example. Then I enter all the specific information. Then I fill down from the example transaction. I delete the example row. Then I copy and paste into Manager Batch Create (not the Batch Update).

Exactly! You understand what I am trying to do.

I don’t know why I did not think of that before. This is exactly why I find posting to the Manager forum valuable. I might identify some accounts whose entries don’t have to be up to date to this second. I can then batch enter the transactions even just once or twice a month depending on the account.

5

You ask a very good question, a question that should be asked. I appreciate the links you provided to make it easy for me to check out the example alternative of Microsoft Money.

Here is what I am thinking:

I used to use Intuit Quick Books. Then I switched to Microsoft Money. Microsoft Money was good while it lasted. But then Microsoft stopped developing, maintaining, and supporting Money. So, I fell back to Quick Books. Finally, I discovered Manager. Manager is being actively developed, maintained, and supported. And Manager does not interrupt my work with intrusive ads. When I invoke Manager, I am assured of getting straight to reports or straight into making entries. I can deal with individual entries or deal with batches of entries (especially with an Excel spreadsheet).

What I see today is that Microsoft provides “Money Plus Sunset Deluxe.” It is sunset for Microsoft Money. Looks like whoever wants can continue to use Money Plus Sunset Deluxe for free. Money Plus Sunset Deluxe will not check whether you are a paid subscriber or purchaser. So, I see Microsoft did the right thing by its Money users by not just leaving them in the lurch. Even so, looks like Money Plus Sunset Deluxe is not being actively developed, maintained, or supported. You can use Money Plus Sunset Deluxe, but you are on your own.

You can also use Manager Accounting for free. But I pay to subscribe to the cloud version (1) as a way to support the Manager team in development, maintenance, and support of Manager Accounting, (2) to have secure storage of Manager data (I won’t lose data from a hard drive catastrophe, etc.), and (3) to access Manager from any device (any of my computers, a friend’s computer, or my smartphone).

I find that Manager is valuable on its own. I find that Manager with Excel Power Query is more than two or three times the value. Data moves very well between the two.

I appreciate your question because your question triggered my rethinking what I am doing. And I have shared my thinking. Hope this post is useful to whoever is interested.

6

I am not sure that we are on the same page here. When I referred to “import those transactions”, I was referencing the Manager feature 0%20Import%20Bank%20State not any batch creation / entry.

7

You are correct that I was referring to another way to make batch entries. I have used the Bank Import. Bank Import works quite well and easily for me. I can make bank rules in Settings to key on the description column to allocate to income and expense accounts however you like.

The last time I used Bank Import, I saw no way to do transactions involving inventory. So, I was not able to use Bank Import for stock buys and sells. But I can still batch create the entries using the Batch Create feature.

8

Yes you can, you just edit the “imported bank transaction” with the stock buy / sell data. Well, that’s how I do it.

Entering the same stock buy / sell data into excel first and then doing “some database types of actions” followed by a Batch Create, just seems complicated in comparison.

9

Yes, what your are saying about Bank Import then editing is simpler.

Of course, I am not entering by hand into an Excel spreadsheet. I import the transactions from a downloaded file into the Excel spreadsheet using Excel’s Power Query (Menu >> Data >> Get Data >> Launch Power Query Editor).

When the transactions are not available to download as a CSV file, I use Manager’s entry forms. Manager’s data entry is well designed.

10

Is the file from the bank or from the stockbroker ?

11

The file is from the stockbroker.

Would be nice if the Bank Import handled inventory buys and sells which amounts to being able to specify an inventory item and quantity in addition to the transaction amount. Even if Bank Import did handle transactions with inventory, I would still prepare the CSV file with an Excel spreadsheet. The reason is the stockbroker shows each individual purchase and sale transaction. So for each company, I sum all the quantities and total amounts, then format the entry for loading or entering into Manger.

I am probably expressing myself in a hard-to-follow manner. I hope an example helps: I buy 10,000 shares of XCO at $20 per share. Ignore taxes and fees to simplify this example. So, from my point of view, I will own 10,000 shares for a total amount of $200,000. From my point of view, this is one transaction. But the stockbroker gives me the whole breakdown of the individual transactions to get the 10,000 shares for me. I get umteen little transactions with quantities of 100 to 1000 shares and different prices. So, I sum it all to one transaction.

Seems to me that a retail store that wants to use a cash register software application would like Bank Imports to handle quantities, too. I see that there is this Lighspeed cash register software (https://www.lightspeedhq.com) that looks like it can export to QuickBooks, Xero, and Sage accounting. I don’t know whether those accounting programs handle items and quantities automatically or you have to edit the transactions after the import.

Anyway, I am already pleased with Manager the way it is. And I value that Manager is actively developed, maintained, and supported. And I value your suggestions and recommendations – and your patience.