Tracking Commodity Prices in Ledger
Ledger is a command line accounting utility. I use it to track my daily expenses as well as my trades on the market. One extremely useful feature of ledger is that it allows arbitrary commodities in transactions. A commodity in ledger lingo is just something you can trade, so this means ledger supports tracking stock and mutual fund purchases, transactions in diffferent currencies, time, and even video game purchases (I don’t do this, I promise). This is a little different than the typical definition of a commodity as some sort of physical good, but it’s important to keep this terminology in mind.
Normally when displaying your balance, ledger shows you the exact amount of each commodity you own. However, ledger also allows you to display everything in terms of one commodity (like dollars). My goal is to achieve a state where I can track my net worth in terms of dollars as it changes with market fluctuations each day.
Commodity basics
Note: All supporting files for this post are available on GitHub.
My ledger file (stored in a file named ledger.dat) contains transactions like the following:
2020/01/01 * Company
Assets:Checking $1000.00
Income:Salary
2020/01/15 * Index fund purchase
Assets:Investments 10 VTSAX @ $80.83
Assets:Checking
This is where I say this is not investment advice, please don’t take it as such, this is just an example with made-up amounts, etc etc…
These two transactions are showing a debit of $1,000.00 into my checking account, followed by a purchase of 10 shares of the mutual fund VTSAX at a price of $808.30. Note the arbitrary commodity symbol used in the stock purchase transaction. Symbols that appear before the amount (such as $) represent currencies, while symbols that appear after (with a space) refer to commodities.
Running a balance report now shows the amount of each commodity I own.
$ ledger -f ledger.dat bal
$191.70
10 VTSAX Assets
$191.70 Checking
10 VTSAX Investments
$-1000.00 Income:Salary
--------------------
$-808.30
10 VTSAX
Under Assets I can see I have $191.70 in my checking account and 10 VTSAX. But I want to see my account value in dollars. Well, ledger is going to help us out under the hood. When I created the transaction buying 10 shares of VTSAX, I specified a purchase price for each unit of the commodity. 10 VTSAX @ 80.83 means each single unit of VTSAX cost $80.83. This gives ledger a conversion between dollars and VTSAX for the date 2020/01/15.
We can convert all commodities to a commodity of our choosing with the --exchange COMMODITY flag. Let’s run a report to see my balance in dollars.
$ ledger -f ledger.dat --exchange $ bal assets
$1000.00 Assets
$191.70 Checking
$808.30 Investments
--------------------
$1000.00
As expected, my assets sum to $1,000.00.
This is great, but many commodities change in value over time. Stocks are a great example – they usually go down right after I buy them.
Let’s say I continue to buy shares of VTSAX over the next few months.
2020/01/01 * Some theoretical company that should pay me
Assets:Checking $1000.00
Income:Salary
2020/01/15 * Index fund purchase
Assets:Investments 10 VTSAX @ $80.83
Assets:Checking
2020/02/01 * Company
Assets:Checking $1000.00
Income:Salary
2020/02/10 * Index fund purchase
Assets:Investments 12 VTSAX @ $82.32
Assets:Checking
2020/03/01 * Company
Assets:Checking $1000.00
Income:Salary
2020/03/20 * Index fund purchase
Assets:Investments 9 VTSAX @ $55.76
Assets:Checking
VTSAX changed in price each time I purchased shares. Ledger uses these transactions to establish a price history for the commodity.
Running a balance report shows the number of VTSAX shares I own as well as my checking account balance.
$ ledger -f ledger.dat bal assets
$702.02
31 VTSAX Assets
$702.02 Checking
31 VTSAX Investments
--------------------
$702.02
31 VTSAX
But what if we run the report with conversion to dollars?
$ ledger -f ledger.dat --exchange $ bal assets
$2430.58 Assets
$702.02 Checking
$1728.56 Investments
--------------------
$2430.58
The $1,728.56 in my investment account comes from the number of shares of VTSAX I own (31) multiplied by the latest price for VTSAX that ledger knows about ($55.76 per share).
I can also see the cost basis for my 31 purchased shares.
$ ledger -f ledger.dat --basis bal investments
$2297.98 Assets:Investments
But my original goal was to bo able to track changes in net worth on a day to day basis, not to see how much money I’ve lost. Ledger only has commodity prices for days I transact the commodity. So asking ledger for the amount my investments are worth on February 10 vs March 20 will show a difference, but asking for March 20 vs March 24 will yield the same result even though VTSAX went up.
$ ledger -f ledger.dat --exchange $ --now 2020/02/10 bal investments
$2551.92 Assets:Investments
$ ledger -f ledger.dat --exchange $ --now 2020/03/20 bal investments
$1728.56 Assets:Investments
$ ledger -f ledger.dat --exchange $ --now 2020/03/24 bal investments
$1728.56 Assets:Investments
Establishing price history
Ledger allows you to specify commodity prices at any date and time. I can specify a price for VTSAX on March 24 by creating a price history file (prices.db, although it can be named whatever you want) and adding a line for 03/24.
P 2020/03/24 VTSAX $59.34
Now if I include this file when generating a report, ledger will know the price of VTSAX on March 24.
$ ledger -f ledger.dat --price-db prices.db --market --now 2020/03/20 bal investments
$1728.56 Assets:Investments
$ ledger -f ledger.dat --price-db prices.db --market --now 2020/03/24 bal investments
$1839.54 Assets:Investments
Notice the --market flag now instead of --exchange $. Ledger will compute the value of your commodities in terms of the conversion specified in the price database (dollars in this case).
Fetching daily price data
The final piece of the puzzle is to fetch and store a price for each commodity I’m interested in, daily. There are a few existing scripts to do this [#1, #2]. I also wrote my own in Python, available on GitHub. Then, this script just needs to be run once daily to fetch and write the latest price to the prices file.
Scheduling a script on Mac
I’ll briefly cover how to run a script once a day on Mac. If you are on a UNIX system you should be able to use cron. If you are on Windows, good luck.
Macs use launchd to manage daemons and agents. We’ll create a scheduled agent (a user level service) to run once per day after market close. Create a file named com.yourname.stockservice.plist in ~/Library/LaunchAgents/ with the following contents.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>com.yourname.stockservice</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/bin/python3</string>
<string>/path/to/prices.py</string>
</array>
<key>StartCalendarInterval</key>
<dict>
<key>Hour</key>
<integer>20</integer>
<key>Minute</key>
<integer>00</integer>
</dict>
<key>StandardOutPath</key>
<string>/path/to/output.log</string>
<key>StandardErrorPath</key>
<string>/path/to/error.log</string>
</dict>
</plist>
Make sure to replace the Label value with whatever you named the file, and replace the path to the Python file to pull stock data. Also provide paths for StandardOutPath and StandardErrorPath. Any output the script produces will be written to StandardOutPath, while errors will be written to StandardErrorPath.
The script is scheduled to run at 20:00 (8 PM) each day, well after market close. The Python script I’ve provided has duplicate detection, so we don’t need to worry about running it on weekends.
Schedule the job to run using the command line.
$ launchctl load ~/Library/LaunchAgents/com.yourname.stockservice.plist
Conclusion
You now have the tools to keep a detailed, daily view of your financial state. Use this power for good, not evil. At some point I’ll get around to figuring how to create graphs to actually utilize some of the data I have, but until then I can use the command line to watch my net worth drop each day.
If you want to learn more, the documentation for ledger is excellent.