Reading XLS files

Sometimes data is provided as the XLS file.

Example: Borrowing rates at Lithuanian Bank is provided as statistics page. It
has data structured as table, but it is always recommended to use structured data, in this case download as XLS.

The best strategy to fetch data is to use Python pandas library, as it has quite advanced XLS reading capabilities. Here is example how to read structured data for this example:

import requests
import json
import pandas as pd
from pprint import pprint

excel_url = "https://www.lb.lt/lt/m_statistika/t-paskolu-palukanu-normos/?export=xlsx"
df = pd.read_excel(excel_url)

selected_data = df.iloc[12:, [0, 6]]

selected_data.columns = ['date', 'value']
json_data = selected_data.to_dict(orient='records')

results = [{
  'date': row.get('date').replace(' ', '') + '-01',
  'value': row.get('value'),
} for row in json_data]

print(json.dumps(results, indent=2))