https://bugs.documentfoundation.org/show_bug.cgi?id=106058
Bug ID: 106058
Summary: EDITING: Make Calc understand currency
Product: LibreOffice
Version: 3.3.0 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: enhancement
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: cheate...@gmail.com
Description:
I often use Calc when comparing prices of things I want to buy. Those things
are located around the world and their prices are in different currencies. I
would like to be able to do the following.
1. Enter a price with a currency suffix (e.g. 150 USD or 99,95 EUR) and have
Calc understand that this is an amount of money in that specific currency.
2. Sort a column that contains polymorphic data, that is, entries in multiple
currencies, according to exchange rate. I should want to set a "main currency"
that Calc will convert all other currencies to when doing this sorting. Some
online service should be used to fetch currency exchange rates. I would like to
be able to set how often this happens. I am fine with currencies being up to a
day old, others might want them updated every 5 minutes. For example, Google
seems to have this ability, but I'm not sure if there's a freely available API.
3. Convert data to other currencies. For example, if B3 contains 150 EUR, and
C3 contains =CONVERT("USD", B3), then C3 should display 160.08 USD (according
to today's exchange rate as of writing this bug description)..
4. Auto-convert data if currencies are incompatible. For example, if B3
contains 150 EUR, and A3 contains 20 USD, and my default currency is USD, and
D3 contains =A3+B3, then D3 should evaluate to 180.08 USD (according to today's
exchange rate as of writing this bug description).
5. Data conversion should happen at the very last moment possible. Source data
should always be kept in the currency originally entered. This is very
important.
6. Calculate on currency data. For example, if B3 contains 150 EUR, and E3
contains =B3/10, then E3 should evaluate to 15 EUR.
7. Dividing two currencies by one another should evaluate to a currency-less
number.
8. There should be a way to display the current exchange rates. For example,
=CONVERT("USD", "1 EUR") should display the current EUR to USD exchange rate.
9. There should be a way to refresh the current exchange rates immediately.
10. Exchange rates should not be refreshed every time data is entered, to
prevent lag and delays in processing.
11. Exchange rates used everywhere in the spreadsheet should all be fetched at
the same time and should all be homogenous. Otherwise calculations will not
work out. If a field with a previously un-fetched currency exchange is entered,
all previously fetched rates should be re-fetched as well.
12. There should be shorthands displayed for currencies. USD should be
displayed as e.g. $ 100.00 and EUR should be displayed as € 100.00. This should
be always prefix or always suffix in order to make the data displayed readable.
13. The shorthands should be configurable.
14. The currency used in a field should be available in the formatting codes
used for display formatting and it should be possible to branch based on the
currency used. This could be used to implement 12 and 13.
15. I should be able to enter shorthand currency data in one of many formats
used in real documents and Calc should recognize and automatically convert the
datum to a currency datum with the right currency. This is important for when
copy-pasting prices from websites.
16. There should be a way to use conversion rates from a specific historical
date in the past. For example, =ASOF("1 day ago", C3) would display the
contents of C3 converted with the conversion rate from 1 day ago. This can be
used to track evolution of prices.
I will now describe a scenario in which I am using all these functions. This is
a scenario which I have encountered many, many times. The numbers in
parentheses correspond to the numbers above.
I would like to buy, for example, a special kind of paint. This paint is fairly
rare. I looked online and found it in the UK, in Germany, in France, and in the
US. I enter the maybe 30 prices I have found (1). I copy-paste the prices from
the websites of the various distributors. They are not formatted well but Calc
should recognize them (15). One of the UK sources said they would not ship
abroad, but they would let me arrange shipping that would pick it up. So even
if the paint price is in GBP, the shipping is in EUR. I would like to have a
column that shows me total price (paint + shipping + VAT if imported from the
US) (4). I would like to display each column of this price in EUR (3). I would
like to find the cheapest deal, so I need to sort. However, before I go finding
out the shipping on each item, I would like to find the 5 cheapest ones, so
that I don't lose time figuring out shipping for items that are obviously too
expensive. This means I would like to sort by the paint price column, which is
in multiple currencies (2). On the next day, I find another source, which sells
the paint in a different size can than the others. So to make comparison fair,
I need to calculate price per liter volume (6). I would also like to calculate
how much I get for my budget. If my budget is 185 EUR, I would like to divide
that number by price per volume. This should leave me with an amount of liters
(7). I would like to display the current exchange rate somewhere, so I know
where I am (8). If I am not sure whether the exchange rates are OK, I would
like to update them to make sure (9). Since I need the paint 3 months from now,
I would like to find out if the foreign sources are dropping in price or
increasing in price - if they are dropping in price I can wait for a few months
to save some margin, but if they are increasing in price I would need to buy
immediately to avoid overpaying (16). All other points from the list above are
used as a matter of course (5, 10, 11, 12, 13, 14).
This would be a huge productivity boost for me and other people using
spreadsheets for this kind of work. I think sourcing things and comparing
prices is the canonical example of what spreadsheets should be able to do, so
it is absolutely surprising that this functionality is not available in Calc.
Steps to Reproduce:
Use Calc for your work.
Actual Results:
Bad productivity having to hack around the fact the computer doesn't understand
money.
Expected Results:
Good productivity.
Reproducible: Always
User Profile Reset: No.
Additional Info:
User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:50.0) Gecko/20100101
Firefox/50.0
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
