1

now I have some data sets in pandas Series form. I want to create an Excel spreadsheet dashboard by using xlsxwriter. the first column is the name and the second column is Sparklines.

I see in xlsxwriter documentations if I want to add sparklines in excel, I have to add the data in excel sheet. is there any way to create a clean dashboard without adding data in the spreadsheet?

for example like: enter image description here

can anyone provide a brief example on how to create a dashboard like this?

my data is:

import pandas as pd
import quandl
import xlsxwriter

copper=quandl.get('CHRIS/CME_HG4',authtoken='4dJ-CbqBE8Kzp_H4T9qe',start_date='2019-01-01')['Settle']
gold=quandl.get("CHRIS/CME_GC1", authtoken="4dJ-CbqBE8Kzp_H4T9qe",start_date='2019-01-01')['Settle']
oil=quandl.get("CHRIS/CME_CL11", authtoken="4dJ-CbqBE8Kzp_H4T9qe",start_date='2019-01-01')['Settle']
copper=copper/gold
oil=oil/gold
print(copper)
print(oil)
Austin Jin
  • 141
  • 3
  • 11

1 Answers1

0

is there any way to create a clean dashboard without adding data in the spreadsheet?

No. The data needs to be somewhere in the workbook to plot the sparklines. I'd suggest hiding the column(s) or worksheet(s) that contain the data.

See the 'hidden' option in worksheet.set_column() or the worksheet.hide() method.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • Yes. You need to have the data in the excel if you want to use Sparklines as they are charts based on the data present. Alternatively you can create Sparklines using Python Packages like matplotlib, and then write them to the cell. – SaaSy Monster Sep 21 '20 at 09:56
  • how to write a graph into a cell by matplotlib? @AresZephyr – Austin Jin Sep 22 '20 at 00:08
  • Let me guide you to another stackover post [link] https://stackoverflow.com/questions/15177705/can-i-insert-matplotlib-graphs-into-excel-programmatically hope this helps. – SaaSy Monster Sep 22 '20 at 02:14
  • Adding another option to write to excel. [link] https://docs.xlwings.org/en/stable/matplotlib.html – SaaSy Monster Sep 22 '20 at 02:16