Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Friday 1 October 2010

CurrencyPosFormat and CurrencyNegFormat settings in Schema.ini

There are 4 possible values for CurrencyPosFormat in a schema.ini file:
  • Currency symbol prefix with no separation ($1)
  • Currency symbol suffix with no separation (1$)
  • Currency symbol prefix with one character separation ($ 1)
  • Currency symbol suffix with one character separation (1 $)
However, they are not specified as ($1) and so forth, they are specified with an index number between 0 and 3
i.e. CurrencySymbol=£ and CurrencyPosFormat=1 will result in a setting of 1£.

There are 16 possible values for CurrencyNegFormat in a schema.ini file:
  • ($1)
  • –$1
  • $–1
  • $1–
  • (1$)
  • –1$
  • 1–$
  • 1$–
  • –1 $
  • –$ 1
  • 1 $–
  • $ 1–
  • $ –1
  • 1– $
  • ($ 1)
  • (1 $)
However, they are not specified as ($1) and so forth, they are specified with an index number between 0 and 15
i.e. CurrencySymbol=£ and CurrencyNegFormat=15 will result in a setting of (1 £).

So far as I can tell CurrencySymbol=£ and CurrencyPosFormat=1 will cope with either 1£ or just 1 in a data file.  However, writing to a file with CurrencySymbol=£ and CurrencyPosFormat=1 specified will result in 1£ format for currency output.

I hope this saves someone some heartache.

By the way, I've been unable to get the CurrencyThousandSymbol in schema.ini to work at all.  I've given up trying at the moment.  I'd love to hear if anyone else gets it working.

P.S. It's CurrencyThousandsSymbol not CurrencyThousandSymbol! The Microsoft documentation is wrong! This works for reading files but not writing them. I'll keep looking at this from time to time.  BTW the international thousands separator (a non-breaking space) doesn't seem to work.  Again I'll report back if I find otherwise.

2 comments:

  1. Thank you! Great info!

    ReplyDelete
  2. many thanks for info about CurrencyThousandsSymbol

    ReplyDelete