Delayed Spreadsheet Opening

[Andresmith76]

I've created a spreadsheet listing various stocks (approx. 80) with each stock's row containing 6 GETREALTIME statements. I am wanting to get real-time updates on each stocks performance (#21-Last Price, #7-Change, #69-PE Ratio, #90-Close, #98-Sector, #101-Ticker). In other cells, I've created equations that use the updated data to generate new data (stock's gain or loss based on the original purchase price, percentage change, etc.). After I open LibreOffice and then my spreadsheet, it takes a very long time (approx. 10-15 minutes) before the spreadsheet finally opens. While I am waiting, I see the text "Adapt Row Height" on the bottom next to the slowly incrementing green horizonal status bar. Does the program need this long time just to gather the data from all the GETREALTIME statements? Without reducing the number of GETREALTIME statements, is there a way to reduce the long wait time before my spreadsheet finally opens?

[jbourvic]

I do not have an answer to reducing the long wait time, but each GETREALTIME is a call function to scrape a specific piece of data from a website. So if you have 6 calls per row times 80 rows, that is a total of 480 calls. When I initiate a call for a single cell, it appears to take just under 3 seconds to update. so 480 times 3 seconds is 1440 sec. divided by 60 = 24 minutes!

PS, my list is the entire list of CEF stocks, 452 rows! I usually go make a pot of coffee, fry some eggs, and maybe even run to the store for some fresh orange juice.

[rassweiler]

I've had a similar issue with a large watchlist, I'm currently trying to have the data only update on the press of a button. This way the sheet opens and can be modified without waiting but the issue is calling the extension from BASIC:

REM  *****  BASIC  *****

Sub Refresh
    Dim stockPrive as Double
    Dim currentDate as Date
    Dim document as Object
    Dim watchlist as Object
    Dim dateCell as Object
    Dim currentCell as Object
    Dim currentTicker as String
    Dim test as Variant
    Dim i as Integer

    ' com.financials.getinfo.python.financialsimpl.getrealtime(A44,21,"YAHOO")
    document = ThisComponent
    watchlist = document.sheets.getByName("Watchlist")
    dateCell = watchlist.getCellRangeByName("Q2")

    for i = 2 to 10
        currentCell = watchlist.getCellRangeByName("J"+i)
        currentTicker = watchlist.getCellRangeByName("A"+i).getString()
        If Not isEmpty(currentTicker) then
            'test = com.financials.getinfo.financialsimpl.getrealtime(currentTicker,21,"YAHOO")
            'test = GETREALTIME(currentTicker,21,"YAHOO")
            test = document.GETREALTIME(currentTicker,21,"YAHOO")
            currentCell.setValue(test)
        End If
    next i

    currentDate = NOW()
    dateCell.setValue(currentDate)

End Sub

I can't figure out how to call the function from script, =GETREALTIME("AAL",21,"YAHOO") works from the cells but not from macros.

This method if I can figure it out should allow the sheet to open quickly and retain the data between updates.

Does anyone know how to go about triggering the extension from BASIC?

[cmallwitz]

Last question first: I have no idea if (and how) Libre Office would allow an extension to be called from Basic or another extension. But I haven't searched for any information on this either...

[cmallwitz]

Re: speed when using loads of symbols in one spread sheet

The extension has no context information on how LibreOffice is calling it - e.g. how many calls to expect or with which arguments. The only observation I have is: it seems to start at cell A:1 and then go down (A:2, A:3, etc) before going to B:1 (and B:2, B:3,etc) and the C:1

Naturally you would have your symbols top to bottom (at least I would). If you have a lot of symbols the caching inside the extension is a bit simple minded and could force additional data fetches for additional data pots on the same symbol.

I have had people reporting having symbols left to right with data points for same symbol top to bottom actually speeding things up.

I have an idea how to improve this a bit but haven't found the time to actually give it a try.

In the end having 500 symbols with a network round trip of 200-500 milliseconds would still require 2-4 minutes no matter what I do. I have found FT being faster for me from London than Yahoo but your mileage may vary from the US.