RTD: Span over more than 1 cell

[gitrpbrme]

Hi Cunnane,
Great job you made wit XLOIL.
I'm building a monitoring dashboard that we extract data, save to MySQL and real-time visualization is going to be on excel.

I'm trying to read data with RTD, however data is a dataframe that spans over more than 1 cell.
It brings a list of data above a threshold, so it's 10-20 rows worth of data.

With xloil, I can only bring function data for 1 cell, nothing further.

What might I be doing wrong?

def readvendasDePut(update = 5):

    sqlEngine       = create_engine('mysql+pymysql://root:[email protected]/opcoesgraph', pool_recycle=3600)
    dbConnection    = sqlEngine.connect()
    data = pd.read_sql('SELECT AtivoBase, PrecoAcao, Ativo, Vencimento, Strike,PrecoBid, PrecoAsk, Premio, Moneyness, Lotes_10000Acoes,(Premio-Moneyness) Prop1, (Premio/Moneyness) Prop2 FROM DadosOpcoes WHERE Premio <> 0  ORDER BY Prop2 DESC', sqlEngine)
    return data

_rtdServer = xloil.RtdServer()

@xloil.func(local=False, rtd=True)  
async def pyGetData() -> PDFrame() :
    while True: 
        dados = readvendasDePut() 
        yield dados
        await asyncio.sleep(2)

if I change the return to yield dados.to_string() it brings all data but in 1 cell, if I change a to_list() I get only 1 record.
The workaround would be to have a function and on every cell I set which data to be recovered, however I guess it would run many SQL Statements which would clog my spreadsheet.

On Excel, I run pyGetData().

Please advise.

PS: I tried searching for the equivalent on pyxll autoresize=True feature, but couldn't. E.g. (video at the right second) https://youtu.be/wNRuB7QlcJo?t=385

[cunnane]

Thank you! I guess you're using an older version of Excel prior to Office 365? In Excel 365, Microsoft introduced dynamic arrays, so an a returned array will magically grow to the required size. Prior to this, if you wanted to return an array, you needed to pre-size the space with Ctrl-Shift-Enter.

Some addins (e.g. Bloomberg, pyxll) introduced workarounds for the lack of dynamic arrays in the 20-odd years before they were introduced. The workarounds paste data into the sheet after the Excel calculation cycle and inevitably have some drawbacks (e.g. formula tracing doesn't work, potential to overwrite data in the sheet). Because of the drawbacks and because the feature is available in the latest Excel I'm reluctant to add add an auto-resize feature.

[gitrpbrme]

Hi Steven, thanks for the quick reply.
I'm using excel 2019.
My expectation when entering for example on B2 the command =pyGetData() was that if it's a dataframe, that would fill B3, B4, B5, B6.
However, I can't find a way to make it happen.
Does xlOil support that?
Should I make it return a dataframe from the @xloil.func function decorator?
The only way I'm able to show data is by setting the df.to_string(), but it doesn't fit my needs.

Thanks again for the help.

[cunnane]

You need to use a newer version of Excel for that to happen automatically. In Excel 2019 and before you need to pre-size the space by pressing ctrl-shift-enter when you create the formula.

It would be possible to build support for a form of auto-resize in legacy Excel versions, but it has a number of drawbacks.

[gitrpbrme]

it worked Flawlessly.
In order for XlOIL to return a dataframe that I could show on Excel I did the following (for future referente if anyone needs this):

1. Instead of returng a Dataframe, I return a list(dataframe.values)
2. On Excel, I select all rows I need data, start typing the formula and hit Ctrl+Shift+Enter
3. Voila.

It worked.

Thanks for the help and much success for xlOil and you mate.

[cunnane]

Glad it worked!

xlOil can return dataframes: you need to import xloil.pandas to enable support, then use the PDFrame decorator. There are some parameters to control how the dataframe is ouput (e.g. with or without the index). Examples