Refreshing data from a file live

hi all,

I got a little python script running that takes data from a website every 2-3s. This data is then transformed to just a small table 10x10 and saved in a csv form. This csv file is obviously being refreshed all the time.
Is there a good way to somehow link an excel file to this table so that I can see the changes live? I've thought that maybe i create a file with mere links to this one and vba as in:

Please Login or Register  to view this content.

compared to python though, this code is very performance heavy (takes one core of an i7) and blocks excel from doing anything else (throwing in doevents perhaps?). Perhaps any of you know of a more sophisticated way to get a live feed of that data into excel?

Thanks !

hi, first of all, why do you open and close the workbook for every update. if you want to watch the updates "live" just open the workbook and then only refresh the query from the csv file every 2-3 seconds.
However, given that you have external python script, it would be much better (efficient?) if you integrate the python with excel, i.e. python script to update the file itself, instead of writing to csv file.
Have a look at xlwings module or excelpython for example.

Here some links, that might help

https://www.pyxll.com/, http://pyxll.com/blog/tag/excelpython/
http://ericremoreynolds.github.io/excelpython/
http://xlwings.org/

Hi Buran,

thanks so much for your reply!

The reason why I'd like it to be refreshed in excel automatically is because the source file (updated by python) will be used by multiple people. Most of them don't know anything about python nor vba but its easier to send them an excel file than get a python script running
I tried the following: open a new workbook; in a formula enter: =ReferenceToPythonFileA1. This obviously worked. When I closed the pythonfile and refreshed it (it has to be closed to write data to it), the excel file does not update the values. Thus the idea about opening and closing the file.
I found that I can do it with a data connection in excel and simply push "RefreshAll" via vba. this works but is not live - it only refreshes when Refreshall is triggered.

I'mma read through the linkes provided and see whether I can use one of those. If anyone has a better idea then please dont hesitate

hi, of course, have a look at the links provided and I'm sure you can get some ideas.
I don't know how this =ReferenceToPythonFileA1 is supposed to work.

edited 05.03. the last part from my original post (follows) is not quite correct

regarding your current workflow:
1. I assume you have connection to the csv file where python scrip writes data. From Data -> GetExternal Data - From text
2. your workbook is opened and there is no need to close it. By clicking refresh all from RibbonX or using VBA code) you should be able to refresh the query data. No need to close/open file. it's sufficient to refresh the connection.
3. Meanwhile python script updates the csv file in the background.

Actually, I'm quite wrong. refresh of the connection will ask to select the csv file again, so it's not convenient.
Really sorry for this mistake

Hey Buran,

It actually works with refreshing a connection one of the properties in the data Connection tab is something like "confirm path file on refresh". I unlocked that and its semi-live then. Thanks so much for all your help!