[GNC] Accounting for gold coins

Gary Holtum diamondhranchqh at earthlink.net
Thu Feb 28 10:01:38 EST 2019 

Adrien,

Thank you for the detailed response. I will work on the steps you describe but I am beginning to think GnuCash is not suited for providing the data I would like. Maybe an Excel spreadsheet would be easier. Thanks again for your expertise.

Gary 

-----Original Message-----
From: gnucash-user [mailto:gnucash-user-bounces+diamondhranchqh=earthlink.net at gnucash.org] On Behalf Of Adrien Monteleone
Sent: Wednesday, February 27, 2019 11:35 AM
To: gnucash-user
Subject: Re: [GNC] Accounting for gold coins

Gary,

1. Turn on Trading Accounts.

	File > Properties > Accounts > Use Trading Accounts

2. Set full transaction view options. (I find this helps to see everything about a transaction and to better understand double-entry)

	View > Transactions Journal (optionally, View > Double Line)

3. Switch to formal accounting labels (again, helps to understand double-entry, the non-formal terms can get confusing I find)

	Preferences > Accounts > Use Formal Accounting Labels

4. Set up your online quotes

	a. If you have not already done so, read the relevant sections of the Help Manual
	 * https://www.gnucash.org/docs/v3/C/gnucash-help/tool-price.html
	 * https://www.gnucash.org/docs/v3/C/gnucash-help/tool-security-edit.html

	b. Follow the instructions in the Help manual to setup Online Price Retrieval
	 * https://www.gnucash.org/docs/v3/C/gnucash-help/acct-create.html#Online-price-setup
	 * get your Alpha Vantage API key, and enter it in the Preferences > Online Quotes section

5. Add XAU as a Currency in the Price Editor

	You can choose Bid, Ask, or Last as a price type, also available are: ’Net’ which I’m not sure of, and ‘Unknown’ which I can’t fathom the purpose for. I went with ‘Last’ because that is what I had been tracking for years before I started using GnuCash, but ‘Bid’ is closest to what you will get if you sell on that day. (‘Bid' and ‘Ask' aren’t always available, ‘Last’ is reliable)

*note* XAU & XAG are spot prices of 1 ounce of metal. (XPT and XPD are available as well) But this does not mean the price quoted is what to expect to receive or pay. Rather, these are based on a 1 Kilogram contract (32.1507 Troy ounces) for Gold, or a 1000 Troy ounce contract for Silver. But it is likely the best approximation absent an actual buying/selling price from a real-world transaction.

	You can always enter your own price quotes in the Price Editor manually if you like. (or you are having technical difficulties retrieving them)

6. Create your Asset holding account

	You can place this in the Asset tree where you want, but for something more formal, I suggest:

	Assets:Current Assets:Other (in my case, I labeled ‘Other’ as ‘Metals & Collectables’)
	
	You’ll likely want to make that a Placeholder account so you don’t put any transactions in it and create sub accounts for each metal type:

	Assets:Current Assets:Metals & Collectables:Gold Bullion
	Assets:Current Assets:Metals & Collectables:Gold Numismatics
	Assets:Current Assets:Metals & Collectables:Silver Bullion
	Assets:Current Assets:Metals & Collectables:Silver Numismatics
	etc.

	Set this account as type ‘Cash’
	Set the currency to XAU (or other metal as needed) for the bullion accounts.

	You don’t need separate accounts for each form of bullion as bullion is valued all the same. (a bar or ingot is the same as a bullion coin for example) I do however set my Description or Notes to reflect what I actually purchased. (bars, Eagles, etc.) This allows me to do Transaction Reports to show those items if I wish.

	For Numismatics, I set those accounts to my home currency and book them at actual purchase price. I don’t estimate any ‘unrealized gain’ on them as there is no way to tell what they might fetch at sale. (as a numismatic) If you want to track these as bullion instead, you can just lump them in the other account entered as their own fractions/multiples of metal.

7. Enter your Opening Balance

	This is for any holdings prior to the date you want to start the account, usually the opening date of your file
	
	a. Enter the amount of metal in ounces as a debit assigned to this account (already listed by default), using decimals as needed. (if you had a 1-kilo bar, you’d enter XAU 32.1507 as the amount)
	b. Assign the balancing split as a credit in your home currency to the Equity:Opening Balances account
	c. When you hit [Enter] GnuCash will dutifully create the additional trading account splits to balance with your book’s home currency.

	Here’s a generic 1oz. example using yesterday’s last price of $1328.67412 USD:

	Dr. Assets:Current Assets:Metals & Collectables:Gold	1.000000
	Dr. Trading:CURRENCY:USD				$1328.67
	Cr. Equity:Opening Balances						$1328.67
	Cr. Trading:CURRENCY:XAU						1.000000

	*note* due to the messy fractions, when you enter the credit to Equity and hit [Enter] GnuCash will more than likely give you a pop up asking you to confirm the currency transfer. (technically, that is what you are doing, transferring one currency asset to another) At the bottom of this window are two options: Exchange Rate & Debit Amount. You’ll want to choose Debit Amount and set it to the same quantity of metal you are entering in the transaction. (In the above example, 1.000000) Otherwise, you’ll likely end up with an extra split to the Imbalance account to handle rounding.

	*note* this will create a price in the Price db based on this transaction. You don’t want to ever delete or edit that price unless the actual transaction was wrong to begin with. (the Price editor will indicate which prices are the result of transactions)

8. Enter a buy transaction (I suspect this is where you are now)

	Same as the Opening Balance, but this time, the balancing split will be a credit to either a cash or checking account.
	Same notes apply about choosing Debit Amount when confirming the currency transfer.

----------

Reporting

You can now select your new asset accounts (as well as the trading accounts) and turn on the trading accounts section in the P&L and Balance Sheet reports to see your performance. Of course, you won’t see anything interesting until there is a change in price and then, usually across periods. You’ll want to likely choose ’Nearest in time’ for your prices in the report options.

Hope that helps,

Regards,
Adrien


> On Feb 27, 2019, at 6:17 AM, Gary Holtum <diamondhranchqh at earthlink.net> wrote:
> 
> Adrien,
> 
> I guess I am missing the point here. I can't enter a simple transaction for buying gold coins. I know the quantity and the total cost. I cr cash and dr gold acct. I get imbalance. I don't understand need for "Trading Acct" on. I tried "buy" gold and "sell" cash but got imbalance errors.
> 
> Presently. I have an acct, gold, that has a balance representing the total of three purchases at different unit costs. I would like to be able to see the current increase or decrease in value of the total based on market fluctuations.
> 
> I set up a new currency account  using XAU but I can't enter the transactions without errors. I am doing something wrong!
> 
> A detailed walk through of a transaction would be very helpful.
> 
> Gary


_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

More information about the gnucash-user mailing list