Re: [GNC] AQBanking OFX DirectConnect

[csingley]

> Aside from the extra whitespace, which shouldn't matter, the git code has
closing tags on every element while the AQB5 code has closing tags only on
grouping elements.
> 
> USERID is specified in the OFX 1.6 DTD as
> 
> and according to
> https://en.wikipedia.org/wiki/Standard_Generalized_Markup_Language#OMITTAG
> the "- o" in the middle means that it should have an opening tag and no
> closing tag.

Sorry to butt in..  MAY have, not SHOULD have.  Closing tags are optional
for OFXv1 "elements" (i.e. data-bearing leaf nodes).

But the spec doesn't really matter in the real world.  The FIs code to
Quicken, not to the spec... and Quicken sends OFXv1 with unclosed elements. 
Therefore, AqBanking really should do so as well in order to be useful.

Contrary to the spec, USAA doesn't accept closing tags on elements. 
However, USAA does accept OFXv2, specifically versions 202 and 205.  Just
send them XML.

Here's the result of scanning USAA's server:

(ofxtools) 6:30:50 csingley@nous ofxtools sequences ? ofxget scan usaa
[{"versions": [102, 151], "formats": [{"pretty": false, "unclosedelements":
true}, {"pretty": true, "unclosedelements": true}]}, {"versions": [200,
202], "formats": [{"pretty": false}, {"pretty": true}]}, {"chgpinfirst":
false, "clientuidreq": false, "authtokenfirst": false, "mfachallengefirst":
false}]




--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
___
gnucash-user mailing list
gnucash-user@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.

Re: [GNC] ofxtools - a Python OFX library

[csingley]

John Ralls-2 wrote
> 
> Thanks.
> 
> A couple of things you might be interested in: We use AQBanking for OFX
> Direct Connect imports, but if you import an OFX or QFX file we use
> https://github.com/libofx/libofx.
> 
> The other is that there's a more up-to-date database of OFX Direct-Connect
> supporting institutions at https://ofxhome.com. AQBanking retrieves its
> connection parameters from there.
> 
> Regards,
> John Ralls
> ___
> gnucash-user mailing list

> gnucash-user@

> 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.

libofx is great, and certainly much faster than my parser.  On the other
hand, ofxtools can generate arbitrary OFX, and I believe covers
significantly more of the spec.  Different tasks, different tools.

OFX Home is where I pulled down the FI data for ofxtools (I've been talking
to Jesse about it - he provides a great service).  I walked through every FI
he's got, recording connection parameters that resulted in receiving an OFX
profile response.  Some of them have odd formatting requirements, like SGML
must not have closing tags on leaf elements, or XML must have newlines
inserted between tags.  This database is included in ofxtools, and is the
part I imagine would be most immediately useful to GnuCash users very
easy to configure OFX downloads, with settings that can be transferred to
GnuCash.   It would be great if some of this information were published
through the OFX Home API.

One of these days I'm going to set up wireshark and capture an updated list
of URL/FI/ORG parameters for OFX servers that Quicken knows about...



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
___
gnucash-user mailing list
gnucash-user@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.

[GNC] ofxtools - a Python OFX library

[csingley]

Some of you might be interested in a project I've been working on:

https://pypi.org/project/ofxtools/

This originally started almost 10 years ago when I asked Jeremy Jongsma if
he minded if I reworked his ofx.py script (he didn't).  One thing led to
another, and I ended up implementing a superset of libofx in Python (as you
do).  It's grown into a sort of a Swiss army knife for dealing with OFX
data.

Y'all are all hooked up with AqBanking, so I suppose you don't have much
need of the meat of the library.  However I have recently been beefing up
the client utility, which might be of interest to those trying to set up
connections to download OFX data.  It contains a listing of every FI from
the old MS Money database that's still online, along with working connection
parameters (URL, OFX version, ORG, FID, BROKERID, along with oddball markup
formatting requirements for FIs like Vanguard).  You can use this to set up
AqBanking direct connect, or download the data directly.

ofxtools can generate OFX, so it's pretty handy for writing conversion
utilities from other formats into OFX so the data can be imported.  It can
also edit OFX data, so it can be helpful in standardizing the bizarre
variety of protocol implementation quirks seen at various FIs.  Working in
Python makes it pretty easy to develop something that works.

I hope an announcement on this list isn't in poor taste - it's certainly no
kind of commercial solicitation.  I just hope to make it easier for users of
free software to get access to their financial data, and for developers to
work with it.  Feedback is welcome.

Cheers, and thanks to the GnuCash developers for continuing to produce great
software.




--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
___
gnucash-user mailing list
gnucash-user@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.

Re: [GNC] Importing investment info from Financial Planner's website

[csingley]

Just to summarize what people are telling you:

GnuCash doesn't have any sort of functionality to automatically create
accounts for an investment the first time it's seen, if that's what you're
after.  You will need to manually set up your accounts with beginning
balances.  It's not that bad, unless your RIA is way off the deep end on the
"zillions of ETFs" gameplan.

Having done that, you can import investment transactions into GnuCash. 
GnuCash hasn't implemented direct web connection for investment
transactions, so you'll need to save the transactions to a local file, then
use File -> Import -> Import OFX/QFX inside GnuCash to import the saved file
and map it to your account structure.

Your advisor's website probably has a facility for downloading OFX/QFX data. 
If not, it looks like Ameriprise has a publicly-available OFX server; you
should be able to download the data.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
___
gnucash-user mailing list
gnucash-user@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.

Re: [GNC] Cashflow-like report to show realized and unrealized gains / balance sheet delta report

[csingley]

Setting aside my umbrage at GAAP-related gaslighting... Chary Chary, I think
what you're looking for is a custom report.  I believe GnuCash already
captures all the necessary data, it just doesn't treat unrealized as a
first-class citizen.

But for your purposes as I understand them, GAAP financial statements are
really a bit of a red herring.  You can do a lot better than GAAP compliance
for investment performance reporting!

Sadly, I lack the ability to knock up custom reports in Scheme that would
help fill this gap.  I'd be game to help anybody that does, though.  I'm
pretty familiar with the relevant calculations and their application to
performance reporting (this isn't really very difficult).


David Carlson-4 wrote
> On Thu, Feb 28, 2019 at 7:50 AM Chary Chary <

> chary314@

> > wrote:
> 
>> csingley,
>>
>> thanks for feedback.  I found the correct report name, which I think I
>> want. In finance world it is called "Statement of Comprehensive Income"
>>
>> https://www.investopedia.com/terms/c/comprehensiveincome.asp
>>
>> ===
>> . Comprehensive income includes net income and unrealized income, such as
>> unrealized gains/losses on hedge/derivative financial instruments and
>> foreign currency transaction gains/losses. Comprehensive income provides
>> a
>> holistic view of a company's income not fully captured on the income
>> statement.
>> 
>>
>> But I am not sure I understand the complexity to calculate it in gnucash.
>>
>> Once just has to walk through all records in the price database (stock
>> and
>> currency) and calculate net worth before and after change and log delta
>> as
>> a transaction in some virtual account.
>>
>>
>> Regards.
>>
>>
>> While it is probably possible to do that in GnuCash, it is well beyond
>> the
> scope of the design of GnuCash.  Particularly generating and tracking all
> of the necessary non-cash information in a manner that would satisfy an
> auditor who understood what you were doing would be mind-boggling to me.
> 
> I doubt that other users here have contemplated stretching GnuCash that
> far, and while several of us can help telling you details here and there
> about how parts of GnuCash work 'under the hood', you will have to do your
> own spadework to craft questions to ask us.
> 
> That said, GnuCash is very versatile and many users have figured out ways
> to stretch it to do amazing things.
> 
> Good luck with your ambitions.
> -- 
> David Carlson
> ___
> gnucash-user mailing list

> gnucash-user@

> 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.





--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
___
gnucash-user mailing list
gnucash-user@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.

Re: [GNC] Cashflow-like report to show realized and unrealized gains / balance sheet delta report

[csingley via gnucash-user]

Chary Chary,

Generally, foreign currency/exchange and derivatives/hedging transactions
belong in the statement of comprehensive income.

Current GAAP mandates that unrealized gain/loss from equity securities be
included on the income statement (usually as "other income").

http://www.fasb.org/jsp/FASB/Document_C/DocumentPage?cid=1176167762170&acceptedDisclaimer=true

Companies have been dragging their heels about adopting ASU 2016-01, because
they prefer to keep this volatility off their income statement & stick it
under the statement of comprehensive income (which is much less analyzed b/c
less significant).

However, the FASB has been very insistent about fair valuing all the things,
and running those balance sheet changes through net income.  They view this
as a top priority in updating GAAP.

The people who set the accounting standards (both in USA and globally...
IFRS is similar on this point) are vigorously opposed to the viewpoint that
we should ignore unrealized gains and just value things at cost.  They're
working hard to pull this idea up by the roots, despite some stubborn
opposition.

Happily, free software can hide from their baleful scrutiny and continue to
resist the man.  But we shouldn't be under any illusions that the way we
handle investments in GnuCash represents best accounting practice.  Quite
the opposite is true, really.


Chary Chary wrote
> csingley,
> 
> thanks for feedback.  I found the correct report name, which I think I
> want. In finance world it is called "Statement of Comprehensive Income"
> 
> https://www.investopedia.com/terms/c/comprehensiveincome.asp
> 
> ===
> . Comprehensive income includes net income and unrealized income, such as
> unrealized gains/losses on hedge/derivative financial instruments and
> foreign currency transaction gains/losses. Comprehensive income provides a
> holistic view of a company's income not fully captured on the income
> statement.
> 
> 
> But I am not sure I understand the complexity to calculate it in gnucash.
> 
> Once just has to walk through all records in the price database (stock and
> currency) and calculate net worth before and after change and log delta as
> a transaction in some virtual account.
> 
> 
> Regards.
> 
> 
> 
> On Wed, Feb 27, 2019 at 6:48 PM csingley <

> csingley@

> > wrote:
> 
>> Adrien Monteleone-2 wrote
>> > Keep in mind, except for very special circumstances, for most people,
>> > ‘unrealized gains’ are simply a ‘what if’ level of thinking. They do
>> not
>> > belong in your books. Only actual realized gains are reflected there.
>>
>> I don't think "owning investments" counts as very special
>> circumstances...
>> and for investments, carrying them at historical cost is really the "what
>> if" thinking.  The entire profession of accountancy has risen up with one
>> voice to declare loudly that financial assets are worth what you can sell
>> them for today, and anchoring on your cost basis is bad accounting that
>> leads to bad decisions.  In fact, Deutsche Bank is currently being widely
>> shamed in the financial press for just this very sin (refusing to write
>> down
>> some horrible mortgage deals until they actually disposed of them years
>> later).
>>
>> If you buy a stock, and it becomes worthless, you are *actually poorer*
>> even
>> if you don't realize the loss.  Conversely, try telling long-time
>> Berkshire
>> Hathaway stockholders that their position deserves a 95% haircut, even if
>> they can sell their BRK stock today and exchange it for bonds that would
>> pay
>> an annual income equal to their cost basis in BRK.  Guess they'd better
>> postpone retirement until they sell their stock... "Using this one weird
>> accounting trick, he increased his safe retirement withdrawal 10x with
>> the
>> click of a mouse!  CFPs hate him!!"
>>
>> Looking only at realized gains can be fine if you just want to report
>> *what
>> happened*, as for paying taxes.  However, if you want financial reports
>> that
>> help you decide *what to do*, it's unhelpful to convince yourself that
>> unrealized gains don't exist.
>>
>> It's not really "what if" to examine your winners & losers, and decide to
>> rebalance your portfolio accordingly.  It's not really "what if" to look
>> at
>> how much in unrealized losses you have to offset realized gains in order
>> to
>> manage your tax bill.  These are not weird corner cases, they are primary
>> concerns of the users of these f

Re: [GNC] Cashflow-like report to show realized and unrealized gains / balance sheet delta report

[csingley]

Adrien Monteleone-2 wrote
> Keep in mind, except for very special circumstances, for most people,
> ‘unrealized gains’ are simply a ‘what if’ level of thinking. They do not
> belong in your books. Only actual realized gains are reflected there.

I don't think "owning investments" counts as very special circumstances...
and for investments, carrying them at historical cost is really the "what
if" thinking.  The entire profession of accountancy has risen up with one
voice to declare loudly that financial assets are worth what you can sell
them for today, and anchoring on your cost basis is bad accounting that
leads to bad decisions.  In fact, Deutsche Bank is currently being widely
shamed in the financial press for just this very sin (refusing to write down
some horrible mortgage deals until they actually disposed of them years
later).

If you buy a stock, and it becomes worthless, you are *actually poorer* even
if you don't realize the loss.  Conversely, try telling long-time Berkshire
Hathaway stockholders that their position deserves a 95% haircut, even if
they can sell their BRK stock today and exchange it for bonds that would pay
an annual income equal to their cost basis in BRK.  Guess they'd better
postpone retirement until they sell their stock... "Using this one weird
accounting trick, he increased his safe retirement withdrawal 10x with the
click of a mouse!  CFPs hate him!!"

Looking only at realized gains can be fine if you just want to report *what
happened*, as for paying taxes.  However, if you want financial reports that
help you decide *what to do*, it's unhelpful to convince yourself that
unrealized gains don't exist.

It's not really "what if" to examine your winners & losers, and decide to
rebalance your portfolio accordingly.  It's not really "what if" to look at
how much in unrealized losses you have to offset realized gains in order to
manage your tax bill.  These are not weird corner cases, they are primary
concerns of the users of these financial reports (like the OP).

They're just a pain to implement.  They're not wrong-headed.


Adrien Monteleone-2 wrote
> If the gain is unrealized, then the account balance has not changed.
> 
> If you physically (or in a bank account) have USD 1000 at the beginning of
> the year because you traded EUR 1000 at a 1:1 rate, and at the end of the
> year, have not done anything with that USD 1000, you still only have USD
> 1000.
> 
> GnuCash might value this at EUR 2000 for the purpose of balancing your
> books, but what you actually hold has not changed. Your bank isn’t going
> to report that you now have EUR 2000 instead of USD 1000.
> 
> Now, if you trade that USD 1000 back to EUR at the new rate, then and only
> then will you really have EUR 2000 - a realized gain.
> 
> If you are using multiple currencies properly, the USD account should
> reflect USD 1000, not EUR 2000.
> 
> If you want to see why the GnuCash balance sheet might reflect a valuation
> change on a currency or commodity, turn on Trading Accounts, select them
> in the report options and also choose the option to show exchange rates.
> 
> If you look at the exchange rates for the end of last year balance sheet
> and new balance sheet, they difference will reflect the value assigned to
> the ’Trading Gain/Loss’ line.
> 
> Keep in mind, except for very special circumstances, for most people,
> ‘unrealized gains’ are simply a ‘what if’ level of thinking. They do not
> belong in your books. Only actual realized gains are reflected there.
> (even being required to ‘mark to market’ would be handled by a transaction
> to realize any gain/loss)
> 
> Regards,
> Adrien
> 
>> On Feb 27, 2019, at 4:18 AM, Chary Chary <

> chary314@

> > wrote:
>> 
>> Christopher,
>> 
>> thanks.
>> 
>> But I must say, I still have a sneaky feeling, that I may be missing
>> something, because I just don't understand how people who deal with
>> several
>> currencies and who have stock can possibly live without such report,
>> which
>> would show unrealized gains.
>> 
>> Without report with unrealized gains you have a situation, that you have
>> some balance at the beginning, balance at the end, but you have no
>> report,
>> which would show how you got from balance at the beginning to balance at
>> the end. Even though all your individual transactions are balanced, if
>> you
>> add them all together they will not produce a delta between balance
>> sheets,
>> in case you deal with  more then one currency or have stock / gold
>> (anything).
>> 
>> In a very simple situation:
>> 
>> Say I want to report in EUR, but I keep money in USD. Say I have 1000 USD
>> and exchange rate was 1:1 at the beginning.
>> So, starting balance sheet will say, that I have 1000 EUR
>> Then exchange rate changed and now 1 USD costs 2 EUR. So, all of a sudden
>> balance sheet at the closing will show, that I now have 2000 EUR.
>> But how did happen?
>> I would then expect some line, saying something like: "unrealized gain
>> due
>> to USD/E

Re: [GNC] Cashflow-like report to show realized and unrealiz

[csingley]

David wrote
> IANAA, but as I understand it, gnucash doesn't track unrealized gains well
> mostly because traditional accounting isn't focused on unrealized gains.
> I'm told that accountants want to be certain that the *amount* of what you
> have is accurate. The value of that is less interesting.

GAAP care very intensely about valuing assets and unrealized gains.  For
financial assets, with very limited exceptions, mark to market is the law of
the land.  No CPA will attest financial statements that don't fair-value
financial assets.


David wrote
> Honestly, the deeper you dig in that hole, the crazier it seems to get.
> Value as determined by whom? At what time? In what jurisdiction? In which
> currency? At what exchange rate? All of these will affect a valuation that
> ultimately is a fantasy. Just ask any art collector...

A yeah, get you some  FAS 157   
boyee!!

FASB in fact prescribes a fair-value hierarchy classifying the degree of
sketchiness of your valuations.

You want crazy?  How about those investment banks booking unrealized gains
during the 2008 financial crisis because their outstanding bonds were
plummeting in price... solid Level 1 valuations!! ... and FAS 157 let them
elect to fair-value their *liabilities*, not just their assets.

The rabbit hole can indeed get pretty deep down there at Level 3, but OP is
asking about plain vanilla Level 1 assets (currency & marketable
securities).


David wrote
> None of it is real until someone pays for it-- and then you have a
> transaction to record it.

You are describing the perspective of cash-basis accounting, which is what
we use GnuCash for ... it's useful for things like budgeting & taxes.

GAAP is accrual accounting, and a foundational principle is that the
earnings process is divorced from cash flows.  Noncash transactions tend to
occupy a big chunk of the ledger.  If you want your balance sheet to present
assets at fair value rather than historical cost, you need unrealized gains
to make your income statement tie out to the balance sheet as it rolls
forward.

What this looks like is that, on your chart of accounts, a financial asset
has subaccounts for cost basis and unrealized gain/loss.  There's an
unrealized gain/loss account under income, too, along with a realized
gain/loss account.  When you fair-value an asset, the transaction is split
between the asset subaccount and the income subaccount.  When you close out
a financial asset, you Dr/Cr the unrealized gain/loss account on the income
statement against the realized gain/loss account.  This is in the addition
to splits wiping out the asset subaccounts against
cash/equivalents/receivables.

What OP is running into is a design limitation of GnuCash - it only handles
a single accounting basis, and it is geared toward cash basis, so it's good
for analysis of those types of problem (cash flow, realized gains, etc.)

A long time ago I tried setting up GAAP chart of accounts for securities in
GnuCash, and it was super annoying to work with b/c the lack of automation.

In actual accounting practice, people who need GAAP financial statements
(e.g. anybody who's accounting for investments) set up a chart of accounts
geared toward GAAP, and keep the ledger on that basis.  Then they push a
series of adjusting transactions to transform to tax basis, and another
series of adjusting transactions to transform to statement of cash flows.

Financial software packages tend to implement this functionality in reports
by flagging certain categories of income-statement accounts as with "ignore
this for taxable income".  It gets more complicated for cash flow.

I'm not an accountant, either, but I do have to deal with this stuff, and
no, GnuCash doesn't implement the needed functionality.  It's a fairly
glaring omission if you care about investments, but the amount of work
needed to relax this constraint would be pretty significant, and I don't
think most people care enough to make it happen.  I mean, hell, I've written
my own library to calculate capital gains, and I haven't yet cared enough to
make that happen, either.  You get in hot water with the tax authorities if
your cash-basis accounting isn't just right, but unless you have financial
reporting obligations, the same is not true of GAAP accounting so we all
tend to concentrate development effort on cash basis, and end up using
brokerage statements and spreadsheets or scripts for tracking personal
investments.
 
  On Wed, Feb 27, 2019 at 15:50, Chary Chary wrote:  
Christopher-

thanks.

But I must say, I still have a sneaky feeling, that I may be missing
something, because I just don't understand how people who deal with several
currencies and who have stock can possibly live without such report, which
would show unrealized gains.

Without report with unrealized gains you have a situation, that you have
some balance at the beginning, balance at the end, but you have no report,
which woul