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
Fertile ground as an extension (to do the internal and external mash-ups using SDK and external tools). But no way this should be considered for core functionality--too many moving pieces that would lead to stability issues for "normal" spread sheet usage. IMHO a WONTFIX
Hi Stuart, thanks for reading. Could you please elaborate what this means? "stability issues for "normal" spread sheet usage." Thank you
Existing ODF table cell "value" content is text, alphanumeric, or numeric (either fp or int). Beyond the display formatting of cells in some currency format/label the cell content has no awareness it is currency. Performing mashups-- tracking exchange rate(s) and applying dynamic conversions to cell values based on some preferred working currency would likely require extending the class of fp numeric cell content with a set of "currency" functions. Establishing a numeric-currency would require extending ODF (making any extended .ODS documents non-standard). Handling arbitrage and currency exchange natively is simply too specialized usage to merit the effort, or divergence from ODF. This can of course be done programmatically to our existing fp cell "value" content with the SDK and external helpers (e.g. wget) applying to templated sheets, and otherwise can be implemented with a proper mix of sheets/cells/formulas and conversion rates. So either extension or template--just not in the core.
Hi Stuart, Could this functionality (eg points 2 and 4) be executed using a plug-in that will not require cumbersome syntax for aruthmetic operations and will integrate with or extend nkrmal sorting?
(In reply to cheater00 from comment #4) > Could this functionality (eg points 2 and 4) be executed using a plug-in IIUC if you can do it manually now in the GUI it can be coded with the APIs provided in the SDK, and then packaged as an .oxt extension. [1] For Calc there is even the ability to add-in the extension as a service, i.e. implementing new FUNCTIONs But here, I still don't think you'd be able to overload the cell "value" making it currency aware. You still would need to use additional columns/sheets to perform the conversions and replace the fp numeric currency "value" with its conversion and sort accordingly. You probably also need to accommodate LibreOffice treatment of currency symbol defaults[2][3] for the locale, and OS system input locale-language. LibreOffice uses ISO 4217 code for Currency and Bank--these are just for styling the display when the cell Value is set to currency. =-ref-= [1] https://wiki.documentfoundation.org/Development/Extension_Development [2] http://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/i18n/Currency.idl [3] http://opengrok.libreoffice.org/xref/core/i18npool/source/localedata/data/
I didn't test but maybe the https://extensions.libreoffice.org/extensions/numbertext-1 could help in someway.
(In reply to V Stuart Foote from comment #5) > (In reply to cheater00 from comment #4) > > Could this functionality (eg points 2 and 4) be executed using a plug-in > > IIUC if you can do it manually now in the GUI it can be coded with the APIs > provided in the SDK, and then packaged as an .oxt extension. Thanks for the links guys. How would you perform currency-aware sort on a column using the gui? I don't see a way. You could add a column that converts everything to a single currency and then sort by that. But this is exactly the kind of hack I'm trying to avoid. For each row you have to manually enter the conversion rate or at least a reference to it. Thatt's prone to error and takes a lot of time. Is there a less crappy way of doing things?
(In reply to cheater00 from comment #7) > But this is exactly the kind of hack I'm trying to avoid. > For each row you have to manually enter the conversion rate or at least a > reference to it. Thatt's prone to error and takes a lot of time. Is there a > less crappy way of doing things? Sorry, not a "hack" as that *is* the "normal" way to build a spread sheet. Making the work flow robust is up to the you as the user--the needed tools are provided. We just can't overload the floating point numbers in cells to give them attributes of currency--so your spread sheet has to provide for that. You have to make each value a tuple(pair) of amount and currency (using either two cells, or within one alphanumeric cell with tagging) to then be able to apply the desired exchange rate and convert the value for sorting. Using additional sheets to hold the converted values in scratch columns and the retrieved exchange rates would reduce clutter on your main sheet. And of course capturing this to a template would reduce errors and allow reproducible results.
Stuart, thanks for your comment. I am well aware that what you described *is* standard operating procedure. However, I strongly believe that it is not the most desirable state of things. Basically we currently have a language with a few primitive data types. It is desirable to extend those to support newer types. As spreadsheets are almost always used to hold money it is difficult to understand why no data type special to mobey has been introduced. I can only imagine this stems from historical heritage, from times of DOS spread sheets where every byte came at a cost. But if you were to design a language or tool *today* to calculate money accounts and transactions, would you explicitly build it so that it
....so that it explicitly does not understand money? Only integers and floating point numbers and strings? I think you would be wiser than to do such a thing. P.S. sorry about splitting this into two comments, the bugzilla interface is quite broken on mobile chrome.
Hmm, so scratch what I said about needing to extend ODF to hold currency cell values. Its already there -- at least as the tuple(pair) of fp value and string for assigned currency. Poked at the OASIS ODF 1.2 docs and seems we actually do have table:table-cell (9.14) and office:value-type "currency" (19.385) with values of office:currency (19.369) defined by a "string" (18.2) [1][2]. Then tested a bit with a calc session in 5.3.0.3, and in LO we provide the string value used for the office:currency cell values from the i18n localedata as XML data [2][3] That is, rather than for just style/formatting as I'd believed, each currency formatted cell of fp data office:value will also have an associated office:currency annotated with its currency string. Looked in the ODF output from a calc session, and the XML is present in the content.xml for the archive. The default currency for a cell with currency format will be set by locale, but every currency format cell could have a non-default currency assigned--manually or programmatically. The GUI even has the "Format as currency" split button with drop list of all the currencies that a cell could be assigned--it will indicate what currency is assigned to the active cell. Meaning can search by cells for a specific currency string. And since it could be found by its currency format the fp value could be directly converted using a retrieved exchange rate, and the cell reassigned a new currency format. Bit of a rub at the moment as I can not figure out how to search against what becomes the office:currency string on save to .ODS--some of the localizations do not use the currency on canvas and the Find & Replace dialog will only find the value in the "formatted display". Probably something simple, but seems like there should be some way to search for office:currency while in the document and not in the ODF. =-ref-= [1] http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#attribute-office_currency [2] http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#refTable13 [3] http://opengrok.libreoffice.org/xref/core/i18npool/source/localedata/data/
Great find, Stuart. So this makes me think two things: Auto-format could automatically change the currency to what's been pasted in. Recognize formats like 100 €, €100, EUR 100, 100 EUR. I think it should. Sorting currently assumes the currency format cells are just usual numbers. They are not and without knowing how to convert different currencies sorting should not be done. It also seems that the majority of the other points is within reach as your objections due to stability seem to be addressed - support for the features necessary to implement the original report is specified by ODF. Am I right to think so?
I'm pushing this to NEW and letting UX deal with it. I agree, this should be WONTFIX but they can deal with it. Also @Cheater - in the future I highly recommend avoiding writing a novel in a bug report or enhancement request. It makes it so probably 1 in 200 people will actually read it and makes the chance of implementation basically 0.
(In reply to Joel Madero from comment #13) > I'm pushing this to NEW and letting UX deal with it. I agree, this should be > WONTFIX but they can deal with it. > > Also @Cheater - in the future I highly recommend avoiding writing a novel in > a bug report or enhancement request. It makes it so probably 1 in 200 people > will actually read it and makes the chance of implementation basically 0. Thanks for the tip. Would you like me to break it up into multiple small feature requests? Would that be better?
(In reply to cheater00 from comment #14) > (In reply to Joel Madero from comment #13) > > I'm pushing this to NEW and letting UX deal with it. I agree, this should be > > WONTFIX but they can deal with it. > > > > Also @Cheater - in the future I highly recommend avoiding writing a novel in > > a bug report or enhancement request. It makes it so probably 1 in 200 people > > will actually read it and makes the chance of implementation basically 0. > > Thanks for the tip. Would you like me to break it up into multiple small > feature requests? Would that be better? Any time a bug or enhancement can be broken up into a single manageable task that is preferred. You can create a meta bug tag and link all of the related issues together. If you're going to do that though, I suggest writing one, waiting to see how the community responds, and then go from there. Else you risk wasting a ton of time only to have the enhancement requests closed as WONTFIX. You can also get live feedback from our community here: https://webchat.freenode.net/ For channel put in either #libreoffice-design (preferred) or #libreoffice-qa (more active) and any nick works
tl,dr; sorry, no, this is nothing for Calc core. Write a Calc Add-In extension that does all the exchange rate thing and tracking and conversion and display formatting. Closing wontfix.
@Eike, * So I'm OK if we don't pursue this in core and seek development of extension. However, in looking at it, am I missing something because it seems we do not expose the currency cells within the UI to be able to query/change against the associated currency? We can query the display format--but that is hit or miss. The export filters import from and export to its ODF 1.2 defined office:value-type and office:currency strings, and we retain that awareness in the GUI. Where we can assign it as a type in GUI[1][2]--but how can we search against it? Without hooks for that no chance for an extension. =-ref-= [1] .uno:NumberFormatCurrencySimple [2] .uno:NumberFormatCurrency and comment 11