[R-sig-ins] Currency/Accounting formats in Excel ... was pasteFromExcel

[David Winsemius]

On Oct 21, 2014, at 2:10 PM, Dan Murphy wrote:

> Thanks, David.
> 
> I was able to find euro and other optional currency denominations
> under Format after installing the French language.
> 
> Thanks for the spreadsheet too. Could you please do me a favor? Format
> your -124 euros with parentheses. And then enter -1000 and format it
> in dollars with parentheses. On my machine the Euro symbol is inside
> the parentheses and the dollar symbol is outside. Is that the case on
> your machine?
> 

I wasn't quite able to figure out what you wanted. The Excel "Custom format" options are available. I can make a custom format string that will insert a fixed number of spaces (2) at the thousands and millions breaks when numbers are greater , and wrap parentheses around negative numbers:

[$€-2] # ##0_);([$€-2] # ### ##0)

So a -1200000000 looks like:

(€ 1240 000 000)

This screenshot shows how you get access to custom formats:

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PastedGraphic-1.tiff
Type: image/tiff
Size: 59944 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-insurance/attachments/20141022/d71835d3/attachment-0001.tiff>
-------------- next part --------------


I'm sure there must be websites out there that have details on the exact syntax for this
-- 
David.

> Finally, I was never able to format in the French method of spaces (I
> can get the decimal comma) -- e.g. € 123 456,89 -- so I don't know if
> that is a valid Excel option I need enable in pasteFromExcel.
> 
> Thanks for your persistence!
> 
> On Tue, Oct 21, 2014 at 1:41 PM, David Winsemius <dwinsemius at comcast.net> wrote:
>> I was the poster who offered those options and have replied to Dan on some followup questions on Rhelp, but the last question seemed to revolve around how Excel handle currency formatting and that is definitely off-topic on Rhelp. I wondered, however, whether it would be seen as on-topic for this mailing list?
>> 
>> (Despite my medical degree, I'm a longtime user of Excel and a medium-term user of R.)
>> 
>> --
>> David Winsemius, MD, MPH
>> Medical Director, Life Underwriting
>> AXA
>> Financial Services Section, US
>> 
>> On Oct 20, 2014, at 10:35 AM, Dan Murphy wrote:
>> 
>>> This is a follow-up to the tread originally started by Edward Roche on Sep 29.
>>> 
>>> Sorry for cross-posting. Meant to post here but sent to R-Help
>>> inadvertently. But I can't complain because I already received a
>>> suggestion for my locale question (Sys.getlocale("LC_MONETARY") and
>>> options("OutDec")).
>>> 
>>> 
>>> ---------- Forwarded message ----------
>>> From: Dan Murphy <chiefmurphy at gmail.com>
>>> Date: Sun, Oct 19, 2014 at 11:18 PM
>>> Subject: pasteFromExcel
>>> To: "R-help at r-project.org" <r-help at r-project.org>
>>> 
>>> 
>>> To Users of Excel:
>>> 
>>> Following advice from Brian and Markus, I created an RMarkdown
>>> "vignette" that shows an example of how the pasteFromExcel function in
>>> the excelRio package on github could be used by an actuary to transfer
>>> a triangle from Excel to R. See today's post at
>>> http://trinostics.blogspot.com/
>>> 
>>> Unfortunately, if you are located outside the US, the demonstrated
>>> functionality will not work for you because the currency regex
>>> implemented assumes the dollar sign ($) and comma/decimal punctuation
>>> of the form 999,999.00.
>>> 
>>> If anyone is interested in contributing currency regex expressions
>>> that work in your locale, I would be happy to try to incorporate them
>>> in the package. If anyone knows how best to determine the user's
>>> locale (might "timezone" suffice?), I'd appreciate that help too.
>>> 
>>> Thanks a lot.
>>> 
>>> Dan
>>> 
>>> _______________________________________________
>>> R-SIG-insurance mailing list
>>> R-SIG-insurance at r-project.org
>>> https://stat.ethz.ch/mailman/listinfo/r-sig-insurance
>> 
>> David Winsemius
>> Alameda, CA, USA
>> 

David Winsemius
Alameda, CA, USA