How to convert number (string format from scraping) to number in a cell with Xlsxwriter

user_13557713 0
Viewed 318

Purpose: to read the securities data from a sqlite database, extracting (scraping) quotations from the internet and inserting them in an excel sheet with xlswriter

import os
import sqlite3
import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell
from urllib.request import urlopen
from bs4 import BeautifulSoup

def GetPrice(myurl):
    html = urlopen(myurl)
    soup = BeautifulSoup(html,'lxml')
    return soup.select_one('.h-price').text

db_filename = 'getdata.db'
db_is_new = not os.path.exists(db_filename)
conn = sqlite3.connect(db_filename)
workbook = xlsxwriter.Workbook("isin.xlsx")
worksheet = workbook.add_worksheet()

if db_is_new:
    print('Need db schema')
else:
    cur = conn.cursor()
    curr_row=0

    worksheet.write_string(0,0,'Isin')
    worksheet.write_string(0,1,'Description')
    worksheet.write_string(0,2,'Url')
    worksheet.write_string(0,3,'Price')
    money = workbook.add_format({'num_format': '$#,##0'})

    for row in cur.execute('SELECT * FROM tbIsin'):
        curr_row += 1
        worksheet.write_string(curr_row,0,row[1])
        worksheet.write_string(curr_row,1,row[3])
        worksheet.write_url(curr_row,2,row[2], string='LINK')
        oldval = GetPrice(row[2])
        newval=oldval.replace('.', '')
        worksheet.write(curr_row,3,newval)
        cella = xl_rowcol_to_cell(curr_row, 3)
        formula = '=VAL(%s)' % cella
        worksheet.write(curr_row,4,formula)

conn.close()
workbook.close()

My problem: the price is in text format (example 9963.71 with decimal Italian format), I can't convert it to number format. Formula I enter in the cell is correct but it gives the result #NAME?.

The best solution would be to enter the number directly into the cell

Today I solved it with a double replacement:

oldval = GetPrice (row [2])
newval = oldval.replace ('.', '')
curval = float (newval.replace (',', '.'))
xlsxwriter beautifulsoup excel sqlite
1 Answers

If the input contains numbers as strings like this '9963.71' then you can use the XlsxWriter strings_to_numbers constructor option to have write() convert them automatically to numbers:

workbook = xlsxwriter.Workbook('isin.xlsx', {'strings_to_numbers': True})

Basically anywhere something like this would work:

>>> float('9963.71')
9963.71

However, if the numbers contain thousands separators, or a comma as the decimal separator then that won't work and you will need to pre-process the strings in some way.

>>> float('9,963.71')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: invalid literal for float(): 9,963.71
user_102380
Related