I’m running into an error with =ANACONDA.CODE(“py”,…) that I’m not running into with =PY().
For example, this correctly return a python object “</> dict” in excel:
=ANACONDA.CODE("py", "### Do Not Edit this formula directly. Use Anaconda Code to modify.
retdata = {'a': [], 'b': []}
for _ in range(5):
retdata['a'].append(list(range(0,350)))
retdata['b'].append(list(range(0,350)))
retdata
", 3)
but changing that to 50 elements, for example:
...
for _ in range(50):
...
results in a #VALUE! error and I can no longer reference the data in that cell. is there any work-arounds for this?
This is an interesting case. For reference I’ve found that any combination of x and y that results in more than 7000 elements in the flattened lists gives the same problem.
retdata = {'a': [], 'b': []}
x = 21
y = 334
for _ in range(x):
retdata['a'].append(list(range(0,y)))
retdata['b'].append(list(range(0,y)))
print((x)*y)
retdata
We will definitely look into why that’s happening when displayed in Excel. The good news is that it doesn’t affect the object itself. You can still reference it without problem in the cell in which the dictionary is created. Or you can reference it outside of that cell using Linked Mode. On the original cell, ensure Linked Mode is selected (the toggle is under the script). When Linked Mode is active, it will say “Run Linked”.
happy to see that I’m not crazy and the issue is reproducible.
My original code was based on the =PY() functions “built into” Excel, but I found that even with a paid Python in Excel license, I was still having my monthly access cut off (apparently the paid license only increases the amount of “premium” cloud time, but total time is not unlimited). I tried modularizing my code so that each cell had a subset of the data I was calculating, and so I would reference that cell to use its data; though Excel was still re-calculating everything every time something changed and it was just chewing through my cloud allotment.
I’m in the process of doing exactly what you suggested, however, and reverting my code back to using global variables that I reference directly since there shouldn’t be those same caps when executing locally.
Ok sounds good. Let me know if you run into any difficulties.
Regarding premium compute on Python in Excel - please make sure that the files you’re opening are stored under the same account as the premium license is applied to. Some months ago I had an issue where I had a file stored in a OneDrive account that didn’t have a premium compute license, and I was opening it from another M365 account that did have a premium license, thinking that I could just use the premium compute on that file.
I found that if the file is not stored in the same account, the premium compute is not used. Since I’ve started keeping all my Python in Excel files on the account that has the premium compute license, I’ve never run into any messages about running out of compute.
It should automatically update. The add-in is a deployed webpage, so we can update that without you needing to make any change in Excel.
Sometimes there are caching issues where Excel uses a cached version of the add-in rather than downloading the latest, but I think it’s been long enough since the deploy that you should have the latest version.
@Jim_Kitchen, I’m still getting the same error on the sample code pasted at the top of this thread, despite rebooting and everything. I’m not sure how this would be possible, but I suspect my IT group here has somehow set it up with a static version because I had to jump through a few hoops to get the add-in enabled for me in the first place. Is there a way I can check the version # to confirm? I’m seeing version 1.0.1 (125b) under my profile link in case that’s it.
Edit: I suspect that’s somehow the case. If I open the web-based version of Excel I see this:
The icons on the right side sort of work, but the Runtime environment never initializes. it just spins forever. So it only seems to work on my Desktop version of Excel. I’ll have to check with my IT group and see if that’s something they know about
@Jim_Kitchen Ah, there we go - that solved it. Thanks!
This question is probably out-of-scope for this thread but it came out of this same demo code.
Is there a way to get the same sort of functionality out of anaconda.code() and py() when in “Excel Mode”?
For example, if I make it even simpler and just do this:
retdata = {'a': []}
for _ in range(5):
retdata['a'].append(list(range(0,5)))
retdata
if I enter that in =PY() in, say, “C5” “Excel Mode” I can then have a separate cell that references it as such: “=C5.a” and it will show the contents of retdata[‘a’] as a 5x5 table.
but if I enter that in =ANACONDA.CODE() in, say, “C4”, I get the result “</> dict” for both “Excel Values” and “Python Object” mode, and in either case I get a “#FIELD!” error if I try to access it in the same way as above. eg “=C4.a”
That functionality is something we can add to =ANACONDA.CODE(), we just haven’t done it yet.
If you want to watch my PyData tutorial about custom reprs, it shows you how to accomplish this (warning - it’s a 1.5 hour tutorial):
The tutorial is for making custom reprs with Python in Excel. Attempting to do the same for Anaconda Code won’t work right now as we try to show a preview of the dict rather than detecting that it could be used by Excel directly. That will be fixed at some point in the future.
Thanks @Jim_Kitchen - I watched the entire thing; was very interesting and I’m glad I did because you’re right - none of that is documented elsewhere as far as I can tell.
Unfortunately though, as you note above I can’t make custom reprs for Anaconda Code at the moment, and I’m trying to get away from using =PY() – unless I can figure out way to get the two to talk to each other (ie have AC do all the “hard” work, and then just use PiE for the output). Or failing that, perhaps just changing my approach. For example where I said “=C4.a” above, instead of having that as a cell reference, I could just do something like REF(“C4”)[‘a’] in AC instead. I’ll have to play around with it.
Nonetheless I’m excited at the doors this opens once I figure out how to leverage it fully for large datasets
