These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Market Discussions

 
  • Topic is locked indefinitely.
 

Dynamic Spreadsheet Market Question....
Author
Previous Topic Next Topic
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#1 - 2017-02-26 23:32:45 UTC  |  Edited by: Sassums
So I created a dynamic spreadsheets from the EVE Billionaire videos.....and every time I shut the page down and open it at a later time I get an #N/A in the cells that are pulling the prices from EVE.....I have to go in edit the cell to intentionally break it....let it save, then go back and correct it to get the correct data to be pulled..

The error just says "Could not fetch url: (eve api URL here)

Any ideas?
Skia Aumer
Planetary Harvesting and Processing LLC
#2 - 2017-02-27 11:55:45 UTC
Sassums wrote:
So I created one of those dynamic spreadsheets from the EVE Billionaire videos....

Which are those?
Zad Murrard
Frozen Dawn Inc
Frozen Dawn Alliance
#3 - 2017-02-27 14:44:20 UTC
Feeling too lazy to google eve billionaire.
Guessing you are using importxml, replace it with importxml2, works much better.
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#4 - 2017-02-27 21:33:07 UTC
Skia Aumer wrote:
Sassums wrote:
So I created one of those dynamic spreadsheets from the EVE Billionaire videos....

Which are those?


Sorry - It was the 4th episode about creating spreadsheets that import market data from EVE's API.
NeoIsTaken
Deep Space Combat Academy
#5 - 2017-02-27 22:27:43 UTC
From the video's comment section:

Nite Hawk10 months ago
This doesnt work for me, I just get the #N/A Error
Cameron Gostin8 months ago
The download file is an edit only, if you will need to make your own spreadsheet to do calculations
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#6 - 2017-02-27 22:37:19 UTC
NeoIsTaken wrote:
From the video's comment section:

Nite Hawk10 months ago
This doesnt work for me, I just get the #N/A Error
Cameron Gostin8 months ago
The download file is an edit only, if you will need to make your own spreadsheet to do calculations


Have my own sheet - still doesnt auto update.
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#7 - 2017-02-27 23:23:25 UTC
Or if anyone could help with the function to get it to work in excel....would be great.
NeoIsTaken
Deep Space Combat Academy
#8 - 2017-02-28 18:26:07 UTC
Only a guess at this point... do you have macro's enabled and the workbook is trusted?
I know MS likes to limit worksheets from running macro's at startup
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#9 - 2017-03-04 12:27:18 UTC
NeoIsTaken wrote:
Only a guess at this point... do you have macro's enabled and the workbook is trusted?
I know MS likes to limit worksheets from running macro's at startup


I am using Google Docs so no macros...just the formulas that were listed in the video.

I cant get the page to update all of the API pulls automatically...or I'm unfamiliar with how its done.
Deja Blue
Orion VII Inc.
#10 - 2017-03-04 16:11:07 UTC  |  Edited by: Deja Blue
WoW, uncanny. I have the same issue with my spreadsheet:
https://docs.google.com/spreadsheets/d/1yKjKl2h5n3D0-bcJR8W4LUMNbQP8-hd_x2mYM8TlOGs/edit#gid=0

Here's my forum post:
https://forums.eveonline.com/default.aspx?g=posts&t=513047&find=unread

Quote:
Hey guys ;)

I just got back into EVE. I have played off and on for years and have an old spreadsheet:
https://docs.google.com/spreadsheets/d/1yKjKl2h5n3D0-bcJR8W4LUMNbQP8-hd_x2mYM8TlOGs/edit#gid=0

The EC API call formulas are on the far right side, you have to side scroll, and are in the Arkonor cell at the top of each column.

Once in a while it works, but usually it won't update and shows all 0s. I have plugged in each ore individually, instead of the list in one formula, to no avail as it too usually shows 0s:
https://docs.google.com/spreadsheets/d/1QLbAI5jxuk8fKbmDiRu_u-nK5fwQ57-_2ACVon9cqws/edit#gid=0

Is this an API throttle from Eve Central?

I can force it to update by changing anything in the formula and then fixing it. I usually change the end from "//buy/avg" to "/buy/avg", removing one slash "/" and then back to the original "//buy/avg" adding the slash back. But you can remove anything, hit enter, it will try to load, and then put it back to the original formula, hit enter again and it loads the whole list fine.

Thanks for your time and any assistance you can provide.

Cheers!


I thought maybe EC was throttling?

I will try Zad's suggestion: importxml2

Cheers!

I'll give them heroics...and when everyone is super...no one will be.
Deja Blue
Orion VII Inc.
#11 - 2017-03-04 16:29:49 UTC
Zad Murrard I tried using importxml2 but it returns #ERROR!

Any ideas?

https://docs.google.com/spreadsheets/d/1yKjKl2h5n3D0-bcJR8W4LUMNbQP8-hd_x2mYM8TlOGs/edit#gid=0

Cells, R16, S16, T16

Cheers!

I'll give them heroics...and when everyone is super...no one will be.
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#12 - 2017-03-04 21:36:13 UTC
I haven't found a solution other than to use Excel 2013 or later which does have a key command to update all the API calls.
Zad Murrard
Frozen Dawn Inc
Frozen Dawn Alliance
#13 - 2017-03-06 10:46:00 UTC  |  Edited by: Zad Murrard
Deja Blue wrote:
Zad Murrard I tried using importxml2 but it returns #ERROR!

Any ideas?

https://docs.google.com/spreadsheets/d/1yKjKl2h5n3D0-bcJR8W4LUMNbQP8-hd_x2mYM8TlOGs/edit#gid=0


In the sheet you are linking you are still using importxml instead of importxml2.

For importxml2 there are two options:
1) http://www.chrisle.me/importxml2/ (have not tried but I assume it works)
2) http://pastebin.com/3zN6VwMj ( I will fix bugs, implement more xpath features if someone requests)

Example of the 2nd one
=IMPORTXML2("https://www.fuzzwork.co.uk/api/mapdata.php?solarsystemid=30004279&format=xml","//row[itemid='40270989']/itemname", 2)

Ie. it differs by taking a random last argument which you can update to something random to force it retrigger the fetch.

Edit: I took a copy of your script, took option 2 into use and changed one of the cells to

=IMPORTXML2("...copy-paste-shortening...","//buy/median", 3)

-> = started working
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#14 - 2017-03-07 15:08:26 UTC
What is the difference between importXML and importXML2?

Does one auto update?
Zad Murrard
Frozen Dawn Inc
Frozen Dawn Alliance
#15 - 2017-03-08 07:27:24 UTC
Difference

ImportXML - provided by google, works unreliably
ImportXML2 - provided by other people, works more reliably, might not support all xpath querys

You could make the pastebin version of importxml2 autoupdate with timers in gdocs I think. Haven't tested.
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#16 - 2017-03-11 00:30:51 UTC
Zad Murrard wrote:
Difference

ImportXML - provided by google, works unreliably
ImportXML2 - provided by other people, works more reliably, might not support all xpath querys

You could make the pastebin version of importxml2 autoupdate with timers in gdocs I think. Haven't tested.


Thanks not familiar with how to do that though! Excel will work for now I Guess!
Slemtiger
The Nordic Associates
Fraternity.
#17 - 2017-03-21 10:41:06 UTC
I have same issue. I gave up using that sheet from the video.

Would you mind letting us know how to use excel to do this? Maybe you have a basic example file we can download and modify for own use? Thanx in advance :)
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#18 - 2017-03-22 17:22:08 UTC
Slemtiger wrote:
I have same issue. I gave up using that sheet from the video.

Would you mind letting us know how to use excel to do this? Maybe you have a basic example file we can download and modify for own use? Thanx in advance :)


I have a basic one set up for T3 Production and it works just like the video's only a bit easier.
Soldarius
Dreddit
Test Alliance Please Ignore
#19 - 2017-03-23 20:25:32 UTC  |  Edited by: Soldarius
google sheets importXML function is utter trash. It breaks constantly. Do not use.

Use this from Steve Ronuken. It works great.

How do?

  1. Open Script Editor in your google sheet.
  2. Copy and Paste the above linked script into the script editor.
  3. Save
  4. Now use the two functions as you would any google sheets function.
  5. Profit?


Personally, I use a single function that can tell the difference between a region id and a system id (hint: regions start with 1 and systems start with 3) so I wouldn't have to choose between them in the sheet.

http://youtu.be/YVkUvmDQ3HY
Sassums
Dark Venture Corporation
Kitchen Sinkhole
#20 - 2017-03-27 18:24:45 UTC
Thanks ill try it out!