On 05/21/2014 08:59 AM, John Ralls wrote:
On 20 May 2014, at 22:45, Bob Gustafson <[email protected]> wrote:
On 05/20/2014 07:20 PM, John Ralls wrote:
On May 20, 2014, at 4:02 PM, Bob Gustafson <[email protected]> wrote:
No, I am not doing it in GnuCash - but I wish I could.
My comment is to encourage any effort in the direction of time and timezone
support and discourage attempts to close off that path.
I don’t think that that’s a direction we want to go. I can’t see many users
making the effort to include the time they wrote a check, or swiped their debit
card, or whatever. FWIW, one CC company seems to post everything at 1700. Every
transaction looks like
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20140121170000.000
Everybody else I use just reports posted dates.
As an example, here is a box of chocolates complete with timestamps..
viewspread13.27.csv:12345678 09/23/2013 -40.32 EINZUG
AUSLANDSLASTSCHRIFT|9208|CHF 49,50KURS1,2276000|KURS VOM 20.09.13
MAFD|ZUERICH-FL AM19.09.13 15.50| LINDT ZRH CHXR CHE| 10010000|
959566027| EC-POSMAGNET6 GEB.EU 0,00| 002|
I see two dates and one timestamp. The timestamp may or may not be useful;
there’s no way of knowing without detailed information from the bank about what
it means. Here’s one guess: You made a purchase for CHF49.50 at 1550 on 19 Sept
2013 in Zurich. It was posted to the seller’s bank on 20 Sept and to your bank
at $40.32 on the 23rd. That’s an effective exchange rate of $1.230/CHF.
The rates, according to
http://www.exchangerates.org.uk/CHF-USD-exchange-rate-history-full.html,
were
Monday 23 September 2013 1 CHF = 1.0979 USD
Sunday 22 September 2013 1 CHF = 1.0984 USD
Saturday 21 September 2013 1 CHF = 1.0987 USD
Friday 20 September 2013 1 CHF = 1.0987 USD
Thursday 19 September 2013 1 CHF = 1.0979 USD
The actual rate used by the banks could have been any of those, or something else,
but the spread is $.0008, or < 4¢ on the transaction out of the $4.25 the bank
charged you. Talk about sweating the small stuff. I don’t see how you gain
anything at all by knowing you made the purchase — or whatever actually happened —
at 1550.
Regards,
John Ralls
Pretty good analysis!
The exchange was into Euros, so the historical exchange rate would have been:
<Head-slap> D'oh.
Monday 23 September 2013 1 CHF = 0.8136 EUR or 40.2732 EUR
Sunday 22 September 2013 1 CHF = 0.8120 EUR or 40.1940
Saturday 21 September 2013 1 CHF = 0.8124 EUR or 40.2138
Friday 20 September 2013 1 CHF = 0.8124 EUR or 40.2138
Thursday 19 September 2013 1 CHF = 0.8115 EUR or 40.1692
The actual euros deducted was -40.32 and the CHF spent was 49.50 or an exch
rate of 0.8145
Picking the rate of 0.8124 gives a Euro deduction of 40.2138 - the actual
deduction was 40.32, so whatever exchange rate the banks used, they did not
lose money.
(The convenience of using a plastic card at the point of sale is certainly
worth 10 euro cents!)
-------
All of this analysis and reporting can be done without human keystrokes, as
long as the data is available. The fact that the transaction was done on a
Thursday at 15:50 and not reported until the following Monday rather increases
the time slop.
Suppose that the transaction was done at 12:50 and that was early enough to
make an earlier bank forex time window and the deduction could then be reported
on Friday 20 Sept (yes, unlikely..)
With enough transactions, some of these details of the forex mechanism could be
teased out of an otherwise very boring bunch of numbers.
But irrelevant to GnuCash, because from the standpoint of your own books, only
the entry date of the transnaction -- the date that you recognize the payment
in your book -- is relevant and recorded. The date it's recognized by the
merchant's bank and by your bank isn't part of your book, it's part of theirs,
and so there's no reason to record it in GnuCash. The time the transaction is
posted in the merchant's book, which is the only one you have, is also
irrelevant and tells you nothing about the forex transaction, which took place
sometime between being posted to the merchant's bank and to yours.
The best tool for playing with that sort of analysis is a spreadsheet program,
which is no doubt what you're using.
Regards,
John Ralls
I have control of the purchase date/time. In the ideal case, the
date/time of payment from the bank account should be a few milliseconds
later. If it is longer (like 3.5 days..), you might say that the banking
system is loaning me money for that time period. This time period is
interesting to me.
On my personal credit card, I am aware of the date the monthly statement
closes. If I purchase stuff just after this date, my bank 'loan' is for
a longer period.
--------
The programs I use are a parser and database to load up the data from
downloaded bank transactions (MT940 format). Everything in the bank
files is loaded into the database.
Once the data is loaded, it is processed by several different programs,
the primary one is a pattern recognition system to sort transactions
into categories (bank charges, taxes, wages, .. 30 different slots).
Other programs zero in on whatever is of interest - steep changes in
insurance costs, cash payments, etc. and are created ad hoc. It is a
management system.
Most of the code is in Ruby. The parser and database are managed within
Ruby on Rails with RubyMine as the IDE. A spreadsheet is only used to
view intermediate files.
GnuCash is tantalizing - it has a lot of multi-currency pieces that
could be useful. However, there remains a gap between my use case and
GnuCash's current capabilities.
Bob G
_______________________________________________
gnucash-devel mailing list
[email protected]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
