def test_get_values_meta__multi(self):
entries, _, options_map = loader.load_string(TEST_INPUT)
commodity_map = getters.get_commodity_map(entries)
values = getters.get_values_meta(commodity_map, 'name', 'ticker')
self.assertEqual(
{
'HOOL': ('Hooli Corp.', 'NYSE:HOOLI'),
'PIPA': ('Pied Piper', None),
'USD': (None, None)
}, values)
def test_get_commodities_map(self):
entries, _, options_map = loader.load_string(TEST_INPUT)
commodity_map = getters.get_commodity_map(entries, options_map)
self.assertEqual({'HOOL', 'PIPA', 'USD'}, commodity_map.keys())
self.assertTrue(all(isinstance(value, data.Commodity)
for value in commodity_map.values()))
self.assertEqual(commodity_map['HOOL'],
next(entry
for entry in entries
if isinstance(entry, data.Commodity)))
def test_get_values_meta__single(self):
entries, _, options_map = loader.load_string(TEST_INPUT)
commodity_map = getters.get_commodity_map(entries)
values = getters.get_values_meta(commodity_map, 'name', default='BLA')
self.assertEqual(
{
'USD': 'BLA',
'PIPA': 'Pied Piper',
'HOOL': 'Hooli Corp.'
}, values)
def merge_meta(entries, options_map, config):
"""Load a secondary file and merge its metadata in our given set of entries.
Args:
entries: A list of directives. We're interested only in the Transaction instances.
unused_options_map: A parser options dict.
config: The plugin configuration string.
Returns:
A list of entries, with more metadata attached to them.
"""
external_filename = config
new_entries = list(entries)
ext_entries, ext_errors, ext_options_map = loader.load_file(
external_filename)
# Map Open and Close directives.
oc_map = getters.get_account_open_close(entries)
ext_oc_map = getters.get_account_open_close(ext_entries)
for account in set(oc_map.keys()) & set(ext_oc_map.keys()):
open_entry, close_entry = oc_map[account]
ext_open_entry, ext_close_entry = ext_oc_map[account]
if open_entry and ext_open_entry:
open_entry.meta.update(ext_open_entry.meta)
if close_entry and ext_close_entry:
close_entry.meta.update(ext_close_entry.meta)
# Map Commodity directives.
comm_map = getters.get_commodity_map(entries, False)
ext_comm_map = getters.get_commodity_map(ext_entries, False)
for currency in set(comm_map) & set(ext_comm_map):
comm_entry = comm_map[currency]
ext_comm_entry = ext_comm_map[currency]
if comm_entry and ext_comm_entry:
comm_entry.meta.update(ext_comm_entry.meta)
# Note: We cannot include the external file in the list of inputs so that a
# change of it triggers a cache rebuild because side-effects on options_map
# aren't cascaded through. This is something that should be defined better
# in the plugin interface and perhaps improved upon.
return new_entries, ext_errors
def test_get_money_instruments(self, entries, errors, options_map):
"""
1900-01-01 commodity VMMXX
export: "MUTF:VMMXX (MONEY:USD)"
1900-01-01 commodity IGI806
export: "(MONEY:CAD)"
"""
commodities_map = getters.get_commodity_map(entries)
self.assertEqual({
'USD': 'MUTF:VMMXX',
'CAD': 'IGI806'
}, export_reports.get_money_instruments(commodities_map))
def create_row_context(entries, options_map):
"""Create the context container which we will use to evaluate rows."""
context = RowContext()
context.balance = inventory.Inventory()
# Initialize some global properties for use by some of the accessors.
context.options_map = options_map
context.account_types = options.get_account_types(options_map)
context.open_close_map = getters.get_account_open_close(entries)
context.commodity_map = getters.get_commodity_map(entries)
context.price_map = prices.build_price_map(entries)
return context
def generate_table(self, entries, errors, options_map):
commodity_map = getters.get_commodity_map(entries, options_map)
ticker_info = getters.get_values_meta(commodity_map, 'name', 'ticker', 'quote')
price_rows = [
(currency, cost_currency, ticker, name)
for currency, (name, ticker, cost_currency) in sorted(ticker_info.items())
if ticker]
return table.create_table(price_rows,
[(0, "Currency"),
(1, "Cost-Currency"),
(2, "Symbol"),
(3, "Name")])
def test_get_money_instruments(self, entries, errors, options_map):
"""
1900-01-01 commodity VMMXX
quote: USD
ticker: "MUTF:VMMXX"
export: "MONEY"
1900-01-01 commodity IGI806
quote: CAD
export: "MONEY"
"""
commodities_map = getters.get_commodity_map(entries, options_map)
self.assertEqual({
'USD': 'MUTF:VMMXX',
'CAD': 'IGI806'
}, export_reports.get_money_instruments(commodities_map))
def main():
import argparse, logging
logging.basicConfig(level=logging.INFO, format='%(levelname)-8s: %(message)s')
parser = argparse.ArgumentParser(description=__doc__.strip())
parser.add_argument('filename', help='Filename')
args = parser.parse_args()
entries, errors, options_map = loader.load_file(args.filename)
commodity_map = getters.get_commodity_map(entries, options_map)
ticker_info = getters.get_values_meta(commodity_map, 'name', 'ticker', 'quote')
print('Fetching:')
for currency, (name, ticker, cost_currency) in sorted(ticker_info.items()):
if ticker:
print('{:16} {:16} {:16} {}'.format(currency, ticker, cost_currency, name))
print()
print('Skipping:')
for currency, (name, ticker, cost_currency) in sorted(ticker_info.items()):
if not ticker:
print('{:16} {:16} {:16} {}'.format(currency, '',
cost_currency or '',
name or ''))
def get_portfolio_matrix(asof_date=None):
"""
打印持仓
Args:
asof_date: 计算该日为止的持仓, 避免未来预付款项影响。
"""
if asof_date is None:
asof_date = datetime.date.today()
(entries, errors, option_map) = beancount.loader.load_file(get_ledger_file())
entries = [entry for entry in entries if entry.date <= asof_date]
assets_holdings, price_map = holdings_reports.get_assets_holdings(entries, option_map)
account_map = get_account_map(entries)
commoditiy_map = getters.get_commodity_map(entries, create_missing=False)
holding_groups = {}
for holding in assets_holdings:
account_obj = account_map[holding.account]
if account_obj is None:
raise ValueError("account is not defined for %s" % holding)
currency_obj = commoditiy_map[holding.currency]
if currency_obj is None:
raise ValueError("commoditiy is not defined for %s" % holding)
if bool(int(account_obj.meta.get("sunk", 0))):
logger.warning(f"{account_obj.account} is an sunk. Ignored.")
continue
if bool(int(account_obj.meta.get("nondisposable", 0))):
logger.warning(f"{account_obj.account} is nondisposable. Ignored.")
continue
for meta_field in ("name", "asset-class", "asset-subclass"):
if meta_field not in currency_obj.meta:
raise ValueError(
"Commodity %s has no '%s' in meta"
"" % (holding.currency, meta_field)
)
account_name = account_obj.meta.get("name")
if not account_name:
raise ValueError("Account name not set for %s" % holding.account)
account_nondisposable = bool(int(account_obj.meta.get("nondisposable", 0)))
symbol_name = currency_obj.meta["name"]
asset_class = currency_obj.meta["asset-class"]
asset_subclass = currency_obj.meta["asset-subclass"]
symbol = holding.currency
currency = holding.cost_currency
price = holding.price_number
price_date = holding.price_date
if price is None:
if symbol == currency:
price = 1
price_date = ''
qty = holding.number
if currency == "CNY":
cny_rate = 1
else:
cny_rate = price_map[(currency, "CNY")][-1][1]
# 2. bloomberg_symbol
holding_dict = {
"account": account_name,
"nondisposable": account_nondisposable,
"symbol": symbol,
"symbol_name": symbol_name,
"asset_class": asset_class,
"asset_subclass": asset_subclass,
"quantity": qty,
"currency": currency,
"price": price,
"price_date": price_date,
"cny_rate": cny_rate,
}
group_key = (symbol, account_nondisposable)
holding_groups.setdefault(group_key, []).append(holding_dict)
rows = []
cum_networth = 0
for (symbol, account_nondisposable), holdings in holding_groups.items():
qty_by_account = {}
for holding in holdings:
if holding["account"] not in qty_by_account:
qty_by_account[holding["account"]] = 0
qty_by_account[holding["account"]] += holding["quantity"]
total_qty = sum(qty_by_account.values())
hld_px = holding["price"]
if hld_px is None:
raise ValueError(f"price not found for {holding}")
networth = holding["cny_rate"] * hld_px * total_qty
cum_networth += networth
row = {
"一级类别": holding["asset_class"],
"二级类别": holding["asset_subclass"],
"标的": holding["symbol_name"],
"代号": symbol,
"持仓量": "%.3f" % total_qty,
"市场价格": "%.4f" % holding["price"],
"报价日期": holding["price_date"],
"市场价值": int(round(holding["price"] * total_qty)),
"货币": holding["currency"],
"人民币价值": "%.2f" % networth,
}
rows.append(row)
rows.sort(key=sort_key)
for row in rows:
pct = Decimal(row["人民币价值"]) / cum_networth
row.update({
"占比": "%.2f%%" % (100 * pct)
})
return rows, cum_networth
def export_holdings(entries,
options_map,
promiscuous,
aggregate_by_commodity=False):
"""Compute a list of holdings to export.
Holdings that are converted to cash equivalents will receive a currency of
"CASH:<currency>" where <currency> is the converted cash currency.
Args:
entries: A list of directives.
options_map: A dict of options as provided by the parser.
promiscuous: A boolean, true if we should output a promiscuious memo.
aggregate_by_commodity: A boolean, true if we should group the holdings by account.
Returns:
A pair of
exported: A list of ExportEntry tuples, one for each exported position.
converted: A list of ExportEntry tuples, one for each converted position.
These will contain multiple holdings.
holdings_ignored: A list of Holding instances that were ignored, either
because they were explicitly marked to be ignored, or because we could
not convert them to a money vehicle matching the holding's cost-currency.
"""
# Get the desired list of holdings.
holdings_list, price_map = holdings_reports.get_assets_holdings(
entries, options_map)
commodities_map = getters.get_commodity_map(entries)
dcontext = options_map['dcontext']
# Aggregate the holdings, if requested. Google Finance is notoriously
# finnicky and if you have many holdings this might help.
if aggregate_by_commodity:
holdings_list = holdings.aggregate_holdings_by(
holdings_list, lambda holding: holding.currency)
# Classify all the holdings for export.
action_holdings = classify_holdings_for_export(holdings_list,
commodities_map)
# The lists of exported and converted export entries, and the list of
# ignored holdings.
exported = []
converted = []
holdings_ignored = []
# Export the holdings with tickers individually.
for symbol, holding in action_holdings:
if symbol in ("CASH", "IGNORE"):
continue
if holding.cost_number is None:
assert holding.cost_currency in (None, holding.currency)
cost_number = holding.number
cost_currency = holding.currency
else:
cost_number = holding.cost_number
cost_currency = holding.cost_currency
exported.append(
ExportEntry(symbol, cost_currency, holding.number, cost_number,
is_mutual_fund(symbol),
holding.account if promiscuous else '', [holding]))
# Convert all the cash entries to their book and market value by currency.
cash_holdings_map = collections.defaultdict(list)
for symbol, holding in action_holdings:
if symbol != "CASH":
continue
if holding.cost_currency:
# Accumulate market and book values.
cash_holdings_map[holding.cost_currency].append(holding)
else:
# We cannot price this... no cost currency.
holdings_ignored.append(holding)
# Get the money instruments.
money_instruments = get_money_instruments(commodities_map)
# Convert all the cash values to money instruments, if possible. If not
# possible, we'll just have to ignore those values.
# Go through all the holdings to convert, and for each of those which aren't
# in terms of one of the money instruments, which we can directly add to the
# exported portfolio, attempt to convert them into currencies to one of
# those in the money instruments.
money_values_book = collections.defaultdict(D)
money_values_market = collections.defaultdict(D)
money_values_holdings = collections.defaultdict(list)
for cost_currency, holdings_list in cash_holdings_map.items():
book_value = sum(holding.book_value for holding in holdings_list)
market_value = sum(holding.market_value for holding in holdings_list)
if cost_currency in money_instruments:
# The holding is already in terms of one of the money instruments.
money_values_book[cost_currency] += book_value
money_values_market[cost_currency] += market_value
money_values_holdings[cost_currency].extend(holdings_list)
else:
# The holding is not in terms of one of the money instruments.
# Find the first available price to convert it into one
for money_currency in money_instruments:
base_quote = (cost_currency, money_currency)
_, rate = prices.get_latest_price(price_map, base_quote)
if rate is not None:
money_values_book[money_currency] += book_value * rate
money_values_market[money_currency] += market_value * rate
money_values_holdings[money_currency].extend(holdings_list)
break
else:
# We could not convert into any of the money commodities. Ignore
# those holdings.
holdings_ignored.extend(holdings_list)
for money_currency in money_values_book.keys():
book_value = money_values_book[money_currency]
market_value = money_values_market[money_currency]
holdings_list = money_values_holdings[money_currency]
symbol = money_instruments[money_currency]
assert isinstance(book_value, Decimal)
assert isinstance(market_value, Decimal)
converted.append(
ExportEntry(
symbol, money_currency,
dcontext.quantize(market_value, money_currency),
dcontext.quantize(book_value / market_value, money_currency),
is_mutual_fund(symbol), '', holdings_list))
# Add all ignored holdings to a final list.
for symbol, holding in action_holdings:
if symbol == "IGNORE":
holdings_ignored.append(holding)
return exported, converted, holdings_ignored
def get_commodity_map(self):
return getters.get_commodity_map(self.entries)
def get_commodities_at_date(entries, options_map, date=None):
"""Return a list of commodities present at a particular date.
This routine fetches the holdings present at a particular date and returns a
list of the commodities held in those holdings. This should define the list
of price date points required to assess the market value of this portfolio.
Notes:
* The ticker symbol will be fetched from the corresponding Commodity
directive. If there is no ticker symbol defined for a directive or no
corresponding Commodity directive, the currency is still included, but
'None' is specified for the symbol. The code that uses this routine should
be free to use the currency name to make an attempt to fetch the currency
using its name, or to ignore it.
* The 'cost-currency' is that which is found on the holdings instance and
can be ignored. The 'quote-currency' is that which is declared on the
Commodity directive from its 'quote' metadata field.
This is used in a routine that fetches prices from a data source on the
internet (either from LedgerHub, but you can reuse this in your own script
if you build one).
Args:
entries: A list of directives.
date: A datetime.date instance, the date at which to get the list of
relevant holdings.
Returns:
A list of (currency, cost-currency, quote-currency, ticker) tuples, where
currency: The Beancount base currency to fetch a price for.
cost-currency: The cost-currency of the holdings found at the given date.
quote-currency: The currency formally declared as quote currency in the
metadata of Commodity directives.
ticker: The ticker symbol to use for fetching the price (extracted from
the metadata of Commodity directives).
"""
# Remove all the entries after the given date, if requested.
if date is not None:
entries = summarize.truncate(entries, date)
# Get the list of holdings at the particular date.
holdings_list = get_final_holdings(entries)
# Obtain the unique list of currencies we need to fetch.
commodities_list = {(holding.currency, holding.cost_currency)
for holding in holdings_list}
# Add in the associated ticker symbols.
commodities_map = getters.get_commodity_map(entries)
commodities_symbols_list = []
for currency, cost_currency in sorted(commodities_list):
try:
commodity_entry = commodities_map[currency]
ticker = commodity_entry.meta.get('ticker', None)
quote_currency = commodity_entry.meta.get('quote', None)
except KeyError:
ticker = None
quote_currency = None
commodities_symbols_list.append(
(currency, cost_currency, quote_currency, ticker))
return commodities_symbols_list
def main():
logging.basicConfig(level=logging.INFO, format='%(levelname)-8s: %(message)s')
parser = argparse.ArgumentParser(description=__doc__.strip())
parser.add_argument('filename', help='Beancount input file')
#parser.add_argument('docid', help="Spreadsheets doc id to update")
parser.add_argument('-n', '--dry-run', action='store_true')
args = parser.parse_args()
# Load the file contents.
entries, errors, options_map = loader.load_file(args.filename)
# Enumerate the list of assets.
def keyfun(posting):
if posting.cost is None:
return (1, posting.units.currency, posting.account)
else:
return (0, posting.account, posting.cost.currency)
postings = sorted(get_balance_sheet_balances(clean_entries_for_balances(entries),
options_map),
key=keyfun)
# Simplify the accounts to their root accounts.
root_accounts = get_root_accounts(postings)
postings = [posting._replace(account=root_accounts[posting.account])
for posting in postings]
# Aggregate postings by account/currency.
agg_postings = sorted(aggregate_postings(postings), key=keyfun)
agg_postings = list(agg_postings)
# Add prices to the postings.
agg_postings = add_prices_to_postings(entries, agg_postings)
# Get the map of commodities to export meta tags.
commodities_map = getters.get_commodity_map(entries)
exports = getters.get_values_meta(commodities_map, 'export')
asset_type = getters.get_values_meta(commodities_map, 'assets')
# Get the map of accounts to export meta tags.
accounts_map = {
account: open
for account, (open, _) in getters.get_account_open_close(entries).items()}
tax_map = populate_with_parents(getters.get_values_meta(accounts_map, 'tax'), 'TAXABLE')
# Filter out postings to be ignored.
agg_postings = [posting
for posting in agg_postings
if exports.get(posting.units.currency, None) != 'IGNORE']
# Realize the model.
price_map = prices.build_price_map(entries)
model = Model(price_map, list(agg_postings), exports, asset_type, tax_map)
# Write out the assets to stdout in CSV format.
if args.dry_run:
return
table = model_to_table(model)
table[0][0] += ' ({:%Y-%m-%d %H:%M})'.format(datetime.datetime.now())
wr = csv.writer(sys.stdout)
wr.writerows(table)
def execute_query(query, entries, options_map):
"""Given a compiled select statement, execute the query.
Args:
query: An instance of a query_compile.Query
entries: A list of directives.
options_map: A parser's option_map.
Returns:
A pair of:
result_types: A list of (name, data-type) item pairs.
result_rows: A list of ResultRow tuples of length and types described by
'result_types'.
"""
# Filter the entries using the FROM clause.
filt_entries = (filter_entries(query.c_from, entries, options_map)
if query.c_from is not None else
entries)
# Figure out the result types that describe what we return.
result_types = [(target.name, target.c_expr.dtype)
for target in query.c_targets
if target.name is not None]
# Create a class for each final result.
# pylint: disable=invalid-name
ResultRow = collections.namedtuple('ResultRow',
[target.name
for target in query.c_targets
if target.name is not None])
# Pre-compute lists of the expressions to evaluate.
group_indexes = (set(query.group_indexes)
if query.group_indexes is not None
else query.group_indexes)
# Indexes of the columns for result rows and order rows.
result_indexes = [index
for index, c_target in enumerate(query.c_targets)
if c_target.name]
order_indexes = query.order_indexes
# Figure out if we need to compute balance.
balance = None
if any(uses_balance_column(c_expr)
for c_expr in itertools.chain(
[c_target.c_expr for c_target in query.c_targets],
[query.c_where] if query.c_where else [])):
balance = inventory.Inventory()
# Create the context container which we will use to evaluate rows.
context = RowContext()
context.balance = balance
# Initialize some global properties for use by some of the accessors.
context.options_map = options_map
context.account_types = options.get_account_types(options_map)
context.open_close_map = getters.get_account_open_close(entries)
context.commodity_map = getters.get_commodity_map(entries)
context.price_map = prices.build_price_map(entries)
# Dispatch between the non-aggregated queries and aggregated queries.
c_where = query.c_where
schwartz_rows = []
if query.group_indexes is None:
# This is a non-aggregated query.
# Precompute a list of expressions to be evaluated, and of indexes
# within it for the result rows and the order keys.
c_target_exprs = [c_target.c_expr
for c_target in query.c_targets]
# Iterate over all the postings once and produce schwartzian rows.
for entry in filt_entries:
if isinstance(entry, data.Transaction):
context.entry = entry
for posting in entry.postings:
context.posting = posting
if c_where is None or c_where(context):
# Compute the balance.
if balance is not None:
balance.add_position(posting)
# Evaluate all the values.
values = [c_expr(context) for c_expr in c_target_exprs]
# Compute result and sort-key objects.
result = ResultRow._make(values[index]
for index in result_indexes)
sortkey = (tuple(values[index] for index in order_indexes)
if order_indexes is not None
else None)
schwartz_rows.append((sortkey, result))
else:
# This is an aggregated query.
# Precompute lists of non-aggregate and aggregate expressions to
# evaluate. For aggregate targets, we hunt down the aggregate
# sub-expressions to evaluate, to avoid recursion during iteration.
c_nonaggregate_exprs = []
c_aggregate_exprs = []
for index, c_target in enumerate(query.c_targets):
c_expr = c_target.c_expr
if index in group_indexes:
c_nonaggregate_exprs.append(c_expr)
else:
_, aggregate_exprs = query_compile.get_columns_and_aggregates(c_expr)
c_aggregate_exprs.extend(aggregate_exprs)
# Note: it is possible that there are no aggregates to compute here. You could
# have all columns be non-aggregates and group-by the entire list of columns.
# Pre-allocate handles in aggregation nodes.
allocator = Allocator()
for c_expr in c_aggregate_exprs:
c_expr.allocate(allocator)
# Iterate over all the postings to evaluate the aggregates.
agg_store = {}
for entry in filt_entries:
if isinstance(entry, data.Transaction):
context.entry = entry
for posting in entry.postings:
context.posting = posting
if c_where is None or c_where(context):
# Compute the balance.
if balance is not None:
balance.add_position(posting)
# Compute the non-aggregate expressions.
row_key = tuple(c_expr(context)
for c_expr in c_nonaggregate_exprs)
# Get an appropriate store for the unique key of this row.
try:
store = agg_store[row_key]
except KeyError:
# This is a row; create a new store.
store = allocator.create_store()
for c_expr in c_aggregate_exprs:
c_expr.initialize(store)
agg_store[row_key] = store
# Update the aggregate expressions.
for c_expr in c_aggregate_exprs:
c_expr.update(store, context)
# Iterate over all the aggregations to produce the schwartzian rows.
for key, store in agg_store.items():
key_iter = iter(key)
values = []
# Finalize the store.
for c_expr in c_aggregate_exprs:
c_expr.finalize(store)
context.store = store
for index, c_target in enumerate(query.c_targets):
if index in group_indexes:
value = next(key_iter)
else:
value = c_target.c_expr(context)
values.append(value)
# Compute result and sort-key objects.
result = ResultRow._make(values[index]
for index in result_indexes)
sortkey = (tuple(values[index] for index in order_indexes)
if order_indexes is not None
else None)
schwartz_rows.append((sortkey, result))
# Order results if requested.
if order_indexes is not None:
schwartz_rows.sort(key=lambda x: x[0],
reverse=(query.ordering == 'DESC'))
# Extract final results, in sorted order at this point.
result_rows = [x[1] for x in schwartz_rows]
# Apply distinct.
if query.distinct:
result_rows = list(misc_utils.uniquify(result_rows))
# Apply limit.
if query.limit is not None:
result_rows = result_rows[:query.limit]
# Flatten inventories if requested.
if query.flatten:
result_types, result_rows = flatten_results(result_types, result_rows)
return (result_types, result_rows)
