Hi Henrik. I've struggled with this myself. I think the root cause of this
behavior is that Excel expects all values to be passed in the English
(United States) format. Once it has the value, Excel then formats it based
on the Windows user locale setting. So doing the right thing in your
.NET-based code can result in seemingly broken formatting in Excel.
I'm afraid that your best bet might indeed be to avoid currency formatting
in Excel.
Garrett McGowan [MSFT Developer International]
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--------------------
if the number symbolizes a monetary value, or just a number.
>We live and work in Sweden, and the default regional setting for Sweden are used.
>Our webpages deliver regional formatting using one of thre methods.
[quoted text clipped - 19 lines]
>b) There is a problem in Excel.
>It seems a bit strange to me however, that the formatting that the operating system is reporting as the standard default currency format, is
not used / valid in Excel.
>And I do use Office 2003 Pro with SP2 (SP 2 was released just 3 weeks or so ago).
>Any thoughts, anyone?
Hello Garret, and thanks for your response.
I had pretty much come to same conclusion myself, but wanted a second opinion.
The problem is that the "correct" way of writing currency values in Sweden is not much used, which compounded the problem.
The predominant way is unfortunately (at least in this aspect) the "Microsoft Excel Way".
That is, a currency value is mostly shown as "Number Format + currency label", that is, for example 9 500 kr (instead of 9.500 kr as it should be).
But since I was unsure if this idiosyncrocy of using Number Format to display currency is a mostly loalized Swedish behaviour or not, I thought I should at least ask around.
On pages where we explicitely write out the currency symbol, I have been formatting currency values as currencies, ie. "1.234,50 kr"
On some pages or forms, a currency symbold (kr usually) is sometimes not shown.
Either because there is a lack of space, a design decision, or just because it is self evident and thus a bit silly to explicitely write it out.
Is it then wrong to use a currency formatting if no symbol is shown?
That is, is "1.234,50" a valid currency? Or should it be written as "1 234,50" since kr is not shown?
My approach HAS been to use currency formatting even so, but now as I have begin to investigate the matter, I have found that most localized settings use the SAME group digit symbol for currencies as the one used for general numbers. So that seems to indicate that this is of rather limited use, and perhaps even wrong.
My greatest fear was that, since Excel could not understand the formatted value, it was not a valid number. But a quick test confirmed that .NET could parse back the formatted string value to a decimal value, although I had to use both NumberStyles and IFormatProvied in some cases:
decimal SomeNumber;
SomeNumber = decimal.Parse("123 345,00", System.Globalization.NumberStyles.Currency);
SomeNumber = decimal.Parse("123.345,00 kr", System.Globalization.NumberStyles.Currency, _RequestedCulture);
SomeNumber = decimal.Parse("123.345,00", System.Globalization.NumberStyles.Currency);
Any thoughts on xml formats?
Is it best to use just plain number formats here, as the below sample?
One generally DON'T want to use the requested cultures monetary symbol here.
A value and it's currency is fixed in "kr" (swedish krona, also known as SEK internationally, just as the american dollar is known as USD internationally), even though en-US has been requsted.
Of course "1.000.000,00" kr is not equal to "$ 1,000,000.00", since there is an exchange rate between the two.
<?xml version="1.0" encoding="iso-8859-1" ?>
<Instrument>
<InstrumentID>SE0000123456</InstrumentID>
<NumberOfStocks>22 500 000</NumberOfStocks>
<TradingLot>1 000</TradingLot>
<Prices>
<AskVolume>5 000</AskVolume>
<AskPrice>13,45</AskPrice>
<BidVolume>12 000</BidVolume>
<BidPrice>12,45</BidPrice>
<LatestQuote>13,50</LatestQuote>
<NextToLastQuote>13,20</NextToLastQuote>
<QuoteDate>2005-10-26</QuoteDate>
<NoOfTrades>484</NoOfTrades>
<TotalTradedValue>7 458 334,70</TotalTradedValue>
<TotalTradedVolume>554 450</TotalTradedVolume>
</Prices>
<FormatHelp>
<Currency>SEK</Currency>
<NumberDecimalSeparator>,</NumberDecimalSeparator>
<NumberGroupSeparator> </NumberGroupSeparator>
<CurrentCultureName>sv-SE</CurrentCultureName>
<LCID>1053</LCID>
</FormatHelp>
</Instrument>
Or should one parse the values as currencies if they ARE currencies and pass along currency format help?
<?xml version="1.0" encoding="iso-8859-1" ?>
<Instrument>
<InstrumentID>SE0000123456</InstrumentID>
<Ticker>TICK 1</Ticker>
<NumberOfStocks>22 500 000</NumberOfStocks>
<TradingLot>1 000</TradingLot>
<Prices>
<AskVolume>5 000</AskVolume>
<AskPrice>13,45</AskPrice>
<BidVolume>12 000</BidVolume>
<BidPrice>12,45</BidPrice>
<LatestQuote>13,50</LatestQuote>
<NextToLastQuote>13,20</NextToLastQuote>
<QuoteDate>2005-10-26</QuoteDate>
<NoOfTrades>484</NoOfTrades>
<TotalTradedValue>7.458.334,70</TotalTradedValue>
<TotalTradedVolume>554 450</TotalTradedVolume>
</Prices>
<FormatHelp>
<Currency>SEK</Currency>
<NumberDecimalSeparator>,</NumberDecimalSeparator>
<NumberGroupSeparator> </NumberGroupSeparator>
<CurrencyDecimalSeparator>,</CurrencyDecimalSeparator>
<CurrencyGroupSeparator>.</CurrencyGroupSeparator>
<CurrentCultureName>sv-SE</CurrentCultureName>
<LCID>1053</LCID>
</FormatHelp>
</Instrument>
This might be a bit confusing, especially if the currency value is so low that no grouping digits are shown, and a currency symbol is NOT displayed.
How can one then know if a value is a number or a currency (without resorting to xml attributes showing the type).
If one tries to figure then out without the benefit of documentation or so, then all the values in bold below can then be either type of value, which mught be kind of confusing, eh?
<?xml version="1.0" encoding="iso-8859-1" ?>
<Instrument>
<InstrumentID>SE0000123456</InstrumentID>
<Ticker>TICK 1</Ticker>
<NumberOfStocks>22 500 000</NumberOfStocks>
<TradingLot>70</TradingLot>
<Prices>
<AskVolume>500</AskVolume>
<AskPrice>13,45</AskPrice>
<BidVolume>700</BidVolume>
<BidPrice>12,45</BidPrice>
<LatestQuote>13,50</LatestQuote>
<NextToLastQuote>13,20</NextToLastQuote>
<QuoteDate>2005-10-26</QuoteDate>
<NoOfTrades>484</NoOfTrades>
<TotalTradedValue>970</TotalTradedValue>
<TotalTradedVolume>70</TotalTradedVolume>
</Prices>
<FormatHelp>
<Currency>SEK</Currency>
<NumberDecimalSeparator>,</NumberDecimalSeparator>
<NumberGroupSeparator> </NumberGroupSeparator>
<CurrencyDecimalSeparator>,</CurrencyDecimalSeparator>
<CurrencyGroupSeparator>.</CurrencyGroupSeparator>
<CurrentCultureName>sv-SE</CurrentCultureName>
<LCID>1053</LCID>
</FormatHelp>
</Instrument>
> Hi Henrik. I've struggled with this myself. I think the root cause of this
> behavior is that Excel expects all values to be passed in the English
[quoted text clipped - 57 lines]
> so ago).
>>Any thoughts, anyone?
Garrett McGowan[MSFT] - 03 Nov 2005 19:30 GMT
Thank you for the detailed information Henrik. I've started an internal
thread with the Excel team regarding the apparent formatting issue. I don't
know yet though whether this behavior is truly unexpected or if there's a
historical reason for the discrepency. I'll keep you updated.
Garrett McGowan [MSFT Developer International]
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--------------------
>From: "Henrik" <henrik_the_boss@hotmail.com>
>Subject: Re: Net Currency formatting not understood in Excel
[quoted text clipped - 7 lines]
>That is, a currency value is mostly shown as "Number Format + currency label", that is, for example 9 500 kr (instead of 9.500 kr as it should be).
>But since I was unsure if this idiosyncrocy of using Number Format to display currency is a mostly loalized Swedish behaviour or not, I thought I
should at least ask around.
>On pages where we explicitely write out the currency symbol, I have been formatting currency values as currencies, ie. "1.234,50 kr"
>On some pages or forms, a currency symbold (kr usually) is sometimes not shown.
>Either because there is a lack of space, a design decision, or just because it is self evident and thus a bit silly to explicitely write it
out.
>Is it then wrong to use a currency formatting if no symbol is shown?
>That is, is "1.234,50" a valid currency? Or should it be written as "1 234,50" since kr is not shown?
>My approach HAS been to use currency formatting even so, but now as I have begin to investigate the matter, I have found that most localized settings
use the SAME group digit symbol for currencies as the one used for general
numbers. So that seems to indicate that this is of rather limited use, and
perhaps even wrong.
>My greatest fear was that, since Excel could not understand the formatted value, it was not a valid number. But a quick test confirmed that .NET
could parse back the formatted string value to a decimal value, although I
had to use both NumberStyles and IFormatProvied in some cases:
> decimal SomeNumber;
> SomeNumber = decimal.Parse("123 345,00", System.Globalization.NumberStyles.Currency);
[quoted text clipped - 4 lines]
>One generally DON'T want to use the requested cultures monetary symbol here.
>A value and it's currency is fixed in "kr" (swedish krona, also known as SEK internationally, just as the american dollar is known as USD
internationally), even though en-US has been requsted.
>Of course "1.000.000,00" kr is not equal to "$ 1,000,000.00", since there is an exchange rate between the two.
><?xml version="1.0" encoding="iso-8859-1" ?>
[quoted text clipped - 54 lines]
>How can one then know if a value is a number or a currency (without resorting to xml attributes showing the type).
>If one tries to figure then out without the benefit of documentation or so, then all the values in bold below can then be either type of value,
which mught be kind of confusing, eh?
>
><?xml version="1.0" encoding="iso-8859-1" ?>
[quoted text clipped - 86 lines]
>> so ago).
>>>Any thoughts, anyone?