Trying to copy currency with 6 digits to the right of the decimal point (Treasury Bill price) in an Excel spreadsheet using xlwings; I use 2 methods. I first insert a new row (row 2 post insertion) above the original row (with values to be copied) with formatting copied from original row (row 3 post insertion).
Method 1 in column A. Assign the value of the new cell (A2) to the value of the original cell (A3)
ws1.range("A2").value = ws1.range("A3").value
The currency value in the new cell is rounded to two digits to the right of the decimal place (SBF accounting?). THIS IS NOT DESIRED.
Method 2 in column B. Step 1. Copy the value of the the original cell (B3). Step 2. Paste the clipboard to the new cell (B2).
ws1.range("B3").copy()
ws1.range("B2").paste(paste="values")
The number is copied correctly.
In both columns in Excel the number format is Currency with $ symbol and 6 decimal places
My questions are:
The code is below:
import xlwings as xw
wb = xw.Book("copyFormula.xlsx")
ws1 = wb.sheets["oneRow"]
ws1.range("A2").value = ws1.range("A3").value # assign A2 to A3, this rounds the number
ws1.range("B3").copy()
ws1.range("B2").paste(paste="values") # These two steps copy the number correctly
The initial spreadsheet is:
| Column A | Column B |
|---|---|
| price | price |
| $97.689885 | $97.689885 |
After running the code the spreadsheet is
| Column A | Column B |
|---|---|
| price | price |
| $97.690000 | $97.689885 |
| $97.689885 | $97.689885 |
The value in A2 ($97.690000) is incorrect; it has been rounded to 2 decimal places.
I looked at Converters and Options but don't see a solution https://docs.xlwings.org/en/stable/converters.html
I dont know why this happens, I'm not aware its documented anywhere and may be a bug. It is an issue with the number formatted as currency or accounting. Perhaps you can raise as an issue with the dev team on github.
Therefore if you change the number_format of the values to say a number with 6 decimal places it should copy with all decimal places.
Or
Preferrably do this in the code. Just about any other number format should work, in the example code below the number format for now cell A3 is changed to 'General' before the A2 value is updated from A3. A2 number format is already currency from the row insert so then just need to change the A3 format back to currency.
import xlwings as xw
wb = xw.Book("copyFormula.xlsx")
ws1 = wb.sheets["oneRow"]
ws1.range('2:2').insert('down', copy_origin='format_from_right_or_below')
ws1.range("A3").number_format = '@' # change number format to general
ws1.range("A2").value = ws1.range("A3").value # assign A2 from A3,
ws1.range("A3").number_format = '$#,##0.000000' # Set number format back to currency
Another option is to use the formula attribute as this holds the value as a string so is copied across to A2 complete but again due to the insert copying the cell formatting from A3 the value will have the number format 'currency' in that cell already so doesn't need any change.
ws1.range('2:2').insert('down', copy_origin='format_from_right_or_below')
ws1.range("A2").value = ws1.range("A3").formula