I have a very similar spreadsheet to this, but it's for manufacturing and calculating the cost vs. sell of Ships. When I did my import I used just a simplistic join statement to get the first value in and then all subsequent values I used the CONTINUE statement, and it saves a whole bunch of code in the cells below the top one. Awesome job at putting this together, and thanks for the link to items, that is a HUGE help.
Great video. Here's a trick - cell address anchoring. At 14:40 you were switching the cell to the original lookup location. If you write the equation like =ImportXML(CONCATENATE(Math!$B$3...) see the $ character? That means "don't change the value RIGHT AFTER when copied". So If you want something to always look at the 3rd row, you'd just use B$3 and copy down. Or if you want to copy across but always look in the B column it's $B3 and so forth.
Wonderful video! Getting into trading was one of the reasons I wanted to get back into EVE in the first place and videos like this are very helpful. Also: If you want to copy a formula down a column (in the same fashion as click-dragging from the corner) select the formula-containing cell and the rest of the range and hit Ctrl+D. This saves me so much time in my day-to-day work.
For those who have problems with ERROR or N/A, make sure that in your country the code is not different. For exemple in France, i had to replace "," by ";" but there is another way to counter this problem : you can set the Regional parameter of the sheet on USA ( File -> Sheet's parameter ). If you want to go the first way, type the command vlookup per exemple, but nothing after that, you'll see a pop-up with the punctuation you need to use. Thanks for the video, that's a very great tool !
I'm using Google Sheets and works perfectly. I have been doing things the hard way for months, now this is the way to station trade. Thank you very much for this tutorial.
Thomas Carpenter BTW: leave the bottom item in each data set to change the value. I opened it up this morning and nothing was working, but the second I changed a item to another item the data refreshes and all the numbers were there.
you need to go and add the additional rows before you import the csv. Delete columns D onwards as it caps you at 400 000 cells, this will allow more rows.you 20 853 rows all together but add a few extra incase. Then import and you'll see them all there!
Yes, evemon feeds into eve-central for market uploads. They also have their own software called contribtastic, see eve-central for more details on both. So yeah, if you're in a low population system or looking at rarely traded stuff (but then why are you trading in low pop systems/low volume stuff?), there might be a significant difference between the actual market and the last time someone uploaded a market snapshot.
Thank you very much for this! You did a good job at explaining structure and usage of such a spreadsheet. I had to resort to using yours since mine did not work even though I did everything the exact same way in the exact same rows and columns but I'll get to it :)
Currently this code works: =FILTERXML(WEBSERVICE(CONCATENATE(Math!B$3, B4, Math!C$3)),"//sell/min") However, this requires individual queries for each cell, and the following code: =FILTERXML(WEBSERVICE(CONCATENATE(Math!B3, JOIN("&typeid=", B4:B27), Math!C3)),"//sell/min") ...returns #NAME?... rather than querying once and populating the entire column as appeared to work just fine with the old importXML command.
By default, the TypeID sheet will only have 100 rows, so you need to manually add more to fit all of the data from the imported file. I think 22 000 rows are sufficient. I deleted all columns after C so that I didn't go over the cap of 400k cells while doing this. After that you can go ahead and import the XML and you should get all of the data this time.
Try making the 20,000 rows the data asks for first. You may need to delete some cells to stay within the 400,000 cell limit in google spreadsheets. That's how I did it.
Also note that there might be any number of in-game reasons for a disconnect between the two: wars, people manipulating the market through scams/speculation/bulk transactions (e.g. Burn Jita, Hulkageddon), pre and post patch speculation (e.g. when PI was first introduced people were buying POS towers in bulk and refining them into their components as there was a huge margin on POS components due to a CCP oops)...
Well, it's been a while since I messed with any of that, but when I did use it a lot, it never took more than a couple of minutes to update, and that was on a slow day. Was usually much faster than that. Maybe check your evemon settings to ensure that it a) is set to upload market data and b) knows where to look for said market caches? Other than that I'm afraid I can't help you (don't have an active sub any more). Good luck.
I work with PI a lot and am setting up a business in my alliance. I want to be able to have a spreadsheet that alliance members are able to go to that tells them how much stock of a certain PI material I have and how much I am buying that for say 80% of jita. This video rocks but my knowledge is 0 on this so still a lot I would not know how to do. I don't even know if a spreadsheet can pull cuantity of something I have and wether I am wanting to buy or have to sell more. Great Vid!!!
After fighting with this in Excel 2013 I got it. Replace importxml formula with: =FILTERXML(WEBSERVICE(CONCATENATE( Math!B$3,B4, Math!C$3)),"//sell/min") The $ in the cell number prevents the number from changing. This will allow you to autofill by dragging the bottom corner. Works.
ImportXML is a gdocs specific function, not available in Excel unfortunately. However, I've put a link in the description to a way around it which you can use in Excel if you like. Thanks for the question!
The strange thing is I was running Evemon while logged into Eve and looking at the markets so I should have been sending a snapshot. As an example, I used the API and saw that someone in Rens was buying O2 at 250 each. I open the market window in Eve and check while parked in Rens and the highest offer is 200. I was under the assumption that Evemon market uploader worked out of the box but this says otherwise....or there is an update delay? I checked the market an hour later - was the same.
thanks for this guide. another viewer recommended it. when i first used it, it was for ore prices..well some smart-ass put a buy order for 1 million units of Bistot at 0.01 each..i'm serious. for whatever reason now, it shows "$0.00"...so that confused me. thought it was a bug, but apparently it's not. so thank for this tutorial; works well.
I know that this is quite an old guide now, but just tried it with limited success. Everything seems to work for a single line entry, however when adding the additional typeID's it will not auto populate the lower cells. Anyone else seen this issue?
Great video mate! keep it up :) Can you point us in the right direction to get "all the item IDs" for imports, and other systems other than Jita or if it is possible to get a region instead of a system. Regards.
Hi there. I solved this by adding the necessary number of rows (10,000+) to the sheet before importing. Seems Google won't automatically create the new rows for some reason. But if you add them, it will populate correctly.
If the set language of google drive is Danish the importXML imports a non usable number !! Have to change to English and rewrite the import (in danish ; is used while in english , is used to separate commands)
Still challenged with #NAME? error on the Trade page in the "C" column. Has there been a correction to the =ImportXML(CONCATENATE( Math!B3,B4, Math!C3), "//sell/min") formula?
Hey, great videos. Can you explain more the column with taxes? If I have in eve a broker's fee of 0.75% i should add in the formula 0.075 instead of 0.0225? Keep up the good work!
This works rather well, however it only provide the info for Jita with the "&usesystem=" statement, where can the other major hub ID numbers be found. I have seen different type of api information but cant seem to find this.
Hey, really enjoyed your tutorial. I have one question though. Is there a convenient way to update all the data in a single category or all of them? So instead of changing one item in the category - which updates the list, how about having a separate update button? Also I noticed when I changed an item in the list to update data, it updated the data, but if I reverted the item back to what it was it "updated" back to the old data.
When I go to import the csv file I downloaded in the description it only imports the first 100 items. This is probably why its not working for those of us that followed step by step.
Every time I log into the sheet the calculation cells are "Error" and I have to delete some text and then readd the text to get the figures to calculate. Any suggestions?
Can you explain why I am seeing a disconnect between the api and the actual market? Does Evemon feed this api? Is there setup/config or another market uploader that we need to run to help maintain the api?
How am I supposed to work out my Taxes from my current skills? Do I use the actual skill level of the % given by the skills? Does anybody have a formula I can use to work this out?
i have this issue with making google docs highlight cells that match. i use the =max command to find the max number out of the pool i am trying to view. but now i want google docs to highlight which specific cell it was , with a colour, so i can see it in a glance
im confused about setting up my spreadsheet... i have no idea about the percentage of my taxes, since i have 2.8% brokers plus the 1.6% tax for sales and thats just for sale... the buy order is 2.8% on top of that as well so what should i put as my tax?
This is awesome! Still works great and as intended. I just cant figure out the math for the taxes based on my skill level...I have Accounting skill level at 3, which reduces sales tax by 10% per level. Whats the math to figure this out?
Some item types work for me like omen for others like the caracal i get the N/A at the prices ? it says for example caracal: could not fetch typeid 621, anyone got any tips?
This is amazing! Thank you so much for this. I just have a question though: Sometimes my imported value would just be '0.00'. How do I get around that? Is that EvE-central just not having a price?
Hi, your video is great, i'm currently trying to replicate such a spreadsheet. But I live in France and a few things are different. And that's the reason I'm writing a commentary, I have an issue with the very simple substraction for the margin, google doc tells me it doesnt recognize the content of the compartment. It's not a number for the program, but it very much is. I clicked on the euro sign for the column but it doesnt put an euro symbol. I'm kinda stuck. Any idea how to solve it ?
on my google spread sheet it is telling me import internal error. it works some of the time but not all the time. can some one help me or point me in the right direction.
Guys , spreadsheet search all data correctly. It turns out that the data is in text format. Even I click to switch to currency format , they do not change . So you can not perform any form of calculation. Can anyone help ? Thank you.
I am very new to this and may have done something wrong but anter i entered the info under the math tab, When i go back into trade the info under column A does not show. When i enter the info for the first step after going into trade my column just reads #N/A Any help for a dumb noob??
your coing doesn't work right, I had to edit it a bit for it to work. before editing, I went over your video and its coding a few times and I had it all down, also it wont save your =importXML when you give it to people you need to have the code as just written in your description
Don't think it works as well as it does in google, I can import the min but cannot specify whether it takes the information from buy or sell; "sell/min"
when i try to put in the first =vlookup for the abaddon i get an error message stating that it has problems when analyzing i am using google docs can anyone help me ?
Instead of typing all of those &typeid=, I did a JOIN instead. Following your example, this would look something like this. =ImportXML(CONCATENATE(Math!B3, JOIN("&typeid=", B4:B27), Math!C3),"//sell/min")
"Yes, a JOIN statement sounds better...could you please give us an example? Or could you do a video response with your suggestions? :)" "Instead of typing all of those &typeid=, I did a JOIN instead. Following your example, this would look something like this. =ImportXML(CONCATENATE(Math!B3, JOIN("&typeid=", B4:B27), Math!C3),"//sell/min")" I like you
HiddenLotus9 If you look at the code, you'll see the JOIN statement in it. :) Also, beware that Excel will have some issues on updating data from EVE Markets API too often.
Your numbers are not loading anymore. Since December 23rd I think. Neither my own spreadsheet. Can't figure out the problem. Someone who has an idea? Maybe a problem with Eve Market?
I figured it out. Since Google released the new version of Spreadsheets, if your working on the old Spreadsheets ImportXML is Broken. Create a new Spreadsheet and copy and paste all your data by clicking on the arrow to the right of each sheet name. When i did this the named ranges got messed up and i had to delete any reference to them and replace with a random number so i didnt get an error code when i hit enter, then retype or paste the reference back make sure you hit enter first. hope this helps good luck guys.
Ok - so few issues, if you misspell usesystem as usersystem, eve central will not warn you that something was wrong. Second issue, I had used C4 instead of C3 on the Math tab which means it wasn't even appending it. All working now. :)
The sell value keeps coming up with #VALUE! and "error: The data could not be retrieved. Please check the URL. The URL is entered exactly as it is in the video. Does anyone else have this problem?
as far as typeID is concerned, why not use the evemarketdata API to get them? And in column C&D, use a JOIN statement ffs. It will do all the work for you. And for E-H, use array formulas, then if you want to add rows you dont have to fiddle with dragging, just edit the top cell, and you are good to go.
There is an example on this sheet: docs.google.com/spreadsheets/d/1SroLubzFJa0thhQisO_ADZwEDo6KJOkqphxK3kK3eMI/edit?usp=sharing ALL THE EXAMPLES ARE IN ROW 2
Hey dont know if you got a answer for this. But you have to change the number in "$usesystem=" so to find the correct id for the system. Put this in your webbrowser: "api.eveonline.com/eve/CharacterID.xml.aspx?names=SOLARSYSTEMNAME" and change SOLARSYSTEMNAME for the system you want. Then you will see a value amongst the lines characterID="30000142" that is your id. Im beginner with this so cant use importxml to help you directly sorry
+Connor Law It is for me, I just clicked it this morning and I could find all of the new items implemented in the last patch. Edit: Jsut download it and use the search command (ctrl + f) to look for the Stork or something similar.
Hey, don't know if you still need this. But I wrote a custom function on excel , =EveGetJitaPrice ( id, TRUE) , that grabs the Jita price from Eve central (True receives buy orders, False returns sell orders). To add it to excel: *1.* Go to "Developer", "Visual Basic". *2.* On the left side find your worksheet name, right click, insert, module. *3.* A window for writing code will pop up and you can copy/paste my code below. Afterwards in your excel sheet you'll be able to use my =EveGetJitaPrice function. To refresh prices on your excel sheet you can use the excel shortcut ctrl+alt+f9 Function EveGetJitaPrice(sName As String, Optional toSell As Boolean) As Variant Dim oHttp As New MSXML2.XMLHTTP60 Dim xmlResp As MSXML2.DOMDocument60 Dim result As Variant On Error GoTo EH typeid = "typeid=" & sName jita = "&usesystem=30000142" sURL = "api.eve-central.com/api/marketstat?" sItem = "typeID" 'open the request and send it oHttp.Open "GET", sURL & typeid & jita, False oHttp.Send 'get the response as xml Set xmlResp = oHttp.responseXML ' get Item If toSell Then EveGetJitaPrice = xmlResp.getElementsByTagName("max").Item(0).Text Else EveGetJitaPrice = xmlResp.getElementsByTagName("min").Item(1).Text End If ' Examine output of these in the Immediate window Debug.Print sName Debug.Print xmlResp.XML CleanUp: On Error Resume Next Set xmlResp = Nothing Set oHttp = Nothing Exit Function EH: GetData = CVErr(xlErrValue) GoTo CleanUp End Function
awesome. i will try that later today. it would be really great if you could provide some simple explanation what which part of the code does, so we could learn a little bit of something new.
Hey, I'm on Excel 2016. Wondering if you could maybe provide an example as to how we use it? I'm very interested. When I use the function in a cell and input the type id and TRUE or FALSE between parenthesis, it takes me to the VBA screen and the first line in the function code is yellow and it doesn't work
I am assuming you mean the Jita Buy/Sell valuesUse this equation for Excel (2016 at least) =FILTERXML(WEBSERVICE(CONCATENATE(Math!B$3, B4, Math!C$3)),"//sell/min")Your boxes (Math!B$3, B4, Math!C$3) might be different
LOL, this would be an awesome and helpful tutorial, IF googledocs wouldn't be as crappy as it is! the syntax of most formulas change with the language. In Germany, formating numbers does not work properly, as can be seen in ziilion googlegroups threads. To separate arguments, semicolons are used instead of commas, all I can think is COLON!!! Nice Vid though!
I have a very similar spreadsheet to this, but it's for manufacturing and calculating the cost vs. sell of Ships. When I did my import I used just a simplistic join statement to get the first value in and then all subsequent values I used the CONTINUE statement, and it saves a whole bunch of code in the cells below the top one. Awesome job at putting this together, and thanks for the link to items, that is a HUGE help.
Great video. Here's a trick - cell address anchoring. At 14:40 you were switching the cell to the original lookup location. If you write the equation like =ImportXML(CONCATENATE(Math!$B$3...) see the $ character? That means "don't change the value RIGHT AFTER when copied". So If you want something to always look at the 3rd row, you'd just use B$3 and copy down. Or if you want to copy across but always look in the B column it's $B3 and so forth.
Wonderful video! Getting into trading was one of the reasons I wanted to get back into EVE in the first place and videos like this are very helpful. Also: If you want to copy a formula down a column (in the same fashion as click-dragging from the corner) select the formula-containing cell and the rest of the range and hit Ctrl+D. This saves me so much time in my day-to-day work.
For those who have problems with ERROR or N/A, make sure that in your country the code is not different. For exemple in France, i had to replace "," by ";" but there is another way to counter this problem : you can set the Regional parameter of the sheet on USA ( File -> Sheet's parameter ).
If you want to go the first way, type the command vlookup per exemple, but nothing after that, you'll see a pop-up with the punctuation you need to use.
Thanks for the video, that's a very great tool !
Thx man
Thanks that really helped me as well. o7
Thx, I did not know that Google was this dumb. Your info helped me. :)
I'm using Google Sheets and works perfectly. I have been doing things the hard way for months, now this is the way to station trade. Thank you very much for this tutorial.
Thomas Carpenter BTW: leave the bottom item in each data set to change the value. I opened it up this morning and nothing was working, but the second I changed a item to another item the data refreshes and all the numbers were there.
you need to go and add the additional rows before you import the csv.
Delete columns D onwards as it caps you at 400 000 cells, this will allow more rows.you 20 853 rows all together but add a few extra incase.
Then import and you'll see them all there!
Yes, evemon feeds into eve-central for market uploads. They also have their own software called contribtastic, see eve-central for more details on both. So yeah, if you're in a low population system or looking at rarely traded stuff (but then why are you trading in low pop systems/low volume stuff?), there might be a significant difference between the actual market and the last time someone uploaded a market snapshot.
Thank you very much for this! You did a good job at explaining structure and usage of such a spreadsheet. I had to resort to using yours since mine did not work even though I did everything the exact same way in the exact same rows and columns but I'll get to it :)
Currently this code works:
=FILTERXML(WEBSERVICE(CONCATENATE(Math!B$3, B4, Math!C$3)),"//sell/min")
However, this requires individual queries for each cell, and the following code:
=FILTERXML(WEBSERVICE(CONCATENATE(Math!B3, JOIN("&typeid=", B4:B27), Math!C3)),"//sell/min")
...returns #NAME?... rather than querying once and populating the entire column as appeared to work just fine with the old importXML command.
J. Edward I think you might be forgetting to put (Math!B$3...). That dollar sign makes a big difference, i believe, but I'm far from an expert
By default, the TypeID sheet will only have 100 rows, so you need to manually add more to fit all of the data from the imported file. I think 22 000 rows are sufficient. I deleted all columns after C so that I didn't go over the cap of 400k cells while doing this. After that you can go ahead and import the XML and you should get all of the data this time.
I LOVE YOU! I can't wait to do this over the weekend! So amazingly helpful!
Thank the Gods to your inception!
Try making the 20,000 rows the data asks for first. You may need to delete some cells to stay within the 400,000 cell limit in google spreadsheets. That's how I did it.
very nice tutorial! looking forward to others.
Also note that there might be any number of in-game reasons for a disconnect between the two: wars, people manipulating the market through scams/speculation/bulk transactions (e.g. Burn Jita, Hulkageddon), pre and post patch speculation (e.g. when PI was first introduced people were buying POS towers in bulk and refining them into their components as there was a huge margin on POS components due to a CCP oops)...
Well, it's been a while since I messed with any of that, but when I did use it a lot, it never took more than a couple of minutes to update, and that was on a slow day. Was usually much faster than that. Maybe check your evemon settings to ensure that it a) is set to upload market data and b) knows where to look for said market caches? Other than that I'm afraid I can't help you (don't have an active sub any more). Good luck.
would love to see new buisness insider vids and tutorials for googlesheets with the new system now.
Great! You're awsome to share your knowledge about this subject so others can use it too.
I always wondered how people could import live data into excel files. Thanks for sharing.
I work with PI a lot and am setting up a business in my alliance. I want to be able to have a spreadsheet that alliance members are able to go to that tells them how much stock of a certain PI material I have and how much I am buying that for say 80% of jita. This video rocks but my knowledge is 0 on this so still a lot I would not know how to do. I don't even know if a spreadsheet can pull cuantity of something I have and wether I am wanting to buy or have to sell more. Great Vid!!!
This is the best thing ever I always wanted to do something like this and now I can Thanks so much great job!
After fighting with this in Excel 2013 I got it. Replace importxml formula with:
=FILTERXML(WEBSERVICE(CONCATENATE( Math!B$3,B4, Math!C$3)),"//sell/min")
The $ in the cell number prevents the number from changing. This will allow you to autofill by dragging the bottom corner. Works.
ImportXML is a gdocs specific function, not available in Excel unfortunately. However, I've put a link in the description to a way around it which you can use in Excel if you like.
Thanks for the question!
Amazing videos, looking forward to more.
you could also just highlight the row and press ctrl D but oh well putting it all in also works
The strange thing is I was running Evemon while logged into Eve and looking at the markets so I should have been sending a snapshot.
As an example, I used the API and saw that someone in Rens was buying O2 at 250 each. I open the market window in Eve and check while parked in Rens and the highest offer is 200.
I was under the assumption that Evemon market uploader worked out of the box but this says otherwise....or there is an update delay? I checked the market an hour later - was the same.
Another excellent video, keep up the good work.
thanks for this guide. another viewer recommended it. when i first used it, it was for ore prices..well some smart-ass put a buy order for 1 million units of Bistot at 0.01 each..i'm serious. for whatever reason now, it shows "$0.00"...so that confused me. thought it was a bug, but apparently it's not. so thank for this tutorial; works well.
I know that this is quite an old guide now, but just tried it with limited success. Everything seems to work for a single line entry, however when adding the additional typeID's it will not auto populate the lower cells.
Anyone else seen this issue?
Great video mate! keep it up :) Can you point us in the right direction to get "all the item IDs" for imports, and other systems other than Jita or if it is possible to get a region instead of a system. Regards.
I fixed this. Change the name of the 3rd sheet to Typeid (lowercase end), and do the same in the data cell
Hi there. I solved this by adding the necessary number of rows (10,000+) to the sheet before importing. Seems Google won't automatically create the new rows for some reason. But if you add them, it will populate correctly.
If the set language of google drive is Danish the importXML imports a non usable number !! Have to change to English and rewrite the import (in danish ; is used while in english , is used to separate commands)
Still challenged with #NAME? error on the Trade page in the "C" column. Has there been a correction to the =ImportXML(CONCATENATE( Math!B3,B4, Math!C3), "//sell/min") formula?
Hey, great videos. Can you explain more the column with taxes? If I have in eve a broker's fee of 0.75% i should add in the formula 0.075 instead of 0.0225?
Keep up the good work!
This works rather well, however it only provide the info for Jita with the "&usesystem=" statement, where can the other major hub ID numbers be found. I have seen different type of api information but cant seem to find this.
check the second video
If you are having trouble importing the Typeid File, instead of replace, click the New Sheet. It will import all the typeid's
I think google docs is broken. My sheet won't import values just sits there with "loading..."
Hey, really enjoyed your tutorial. I have one question though. Is there a convenient way to update all the data in a single category or all of them? So instead of changing one item in the category - which updates the list, how about having a separate update button?
Also I noticed when I changed an item in the list to update data, it updated the data, but if I reverted the item back to what it was it "updated" back to the old data.
I found this very helpful, thank you!
When I go to import the csv file I downloaded in the description it only imports the first 100 items. This is probably why its not working for those of us that followed step by step.
Every time I log into the sheet the calculation cells are "Error" and I have to delete some text and then readd the text to get the figures to calculate. Any suggestions?
Ca i do it just like you in exel, only changing the ImportXML thing?
Can you explain why I am seeing a disconnect between the api and the actual market? Does Evemon feed this api? Is there setup/config or another market uploader that we need to run to help maintain the api?
How am I supposed to work out my Taxes from my current skills? Do I use the actual skill level of the % given by the skills? Does anybody have a formula I can use to work this out?
i have this issue with making google docs highlight cells that match. i use the =max command to find the max number out of the pool i am trying to view. but now i want google docs to highlight which specific cell it was , with a colour, so i can see it in a glance
I love the concept but for the life of me cant get your spreed sheet to work does the url you pull data from still exist?
Brilliant instruction!
im confused about setting up my spreadsheet... i have no idea about the percentage of my taxes, since i have 2.8% brokers plus the 1.6% tax for sales and thats just for sale... the buy order is 2.8% on top of that as well so what should i put as my tax?
Should be (.028 + .016)/2
This is awesome! Still works great and as intended. I just cant figure out the math for the taxes based on my skill level...I have Accounting skill level at 3, which reduces sales tax by 10% per level. Whats the math to figure this out?
Pelang3 it tells you when you buy or sell literally anything
Great tutorial, thank you!
Thank you Chris Murray, also for your input for Excel 2013!
Hey, this does not work any more. they have changed the location of the information
For the total fees and taxes, is it broker sell + broker buy + sales tax?
Very nice, thx for the explanation.
Some item types work for me like omen for others like the caracal i get the N/A at the prices ? it says for example caracal: could not fetch typeid 621, anyone got any tips?
This is amazing! Thank you so much for this.
I just have a question though: Sometimes my imported value would just be '0.00'. How do I get around that? Is that EvE-central just not having a price?
Thanks for doing this.
Hi, your video is great, i'm currently trying to replicate such a spreadsheet. But I live in France and a few things are different. And that's the reason I'm writing a commentary, I have an issue with the very simple substraction for the margin, google doc tells me it doesnt recognize the content of the compartment. It's not a number for the program, but it very much is. I clicked on the euro sign for the column but it doesnt put an euro symbol. I'm kinda stuck. Any idea how to solve it ?
on my google spread sheet it is telling me import internal error. it works some of the time but not all the time. can some one help me or point me in the right direction.
Guys , spreadsheet search all data correctly. It turns out that the data is in text format. Even I click to switch to currency format , they do not change . So you can not perform any form of calculation. Can anyone help ? Thank you.
I've got the same problem!
great work making a video with half the content of half of a project
Mine just says #NAME? when trying to look up the typeid sheets for the trade tab?
Very great tutorial! i like it ;)
i've a problem: when i import the price it is like "4962.84.00". How can i erase that .00 at the end?
I am very new to this and may have done something wrong but anter i entered the info under the math tab, When i go back into trade the info under column A does not show.
When i enter the info for the first step after going into trade my column just reads #N/A
Any help for a dumb noob??
+Rehpic Oh god i wish i could delete that comment... So dumb didnt realize i had to type in the names manually
Does the buy tax and sell tax not differ from each other?
When I try to open it in the eve browser (as I made it outside in firefox) it crashes the browser every time :(
Holy crap... that is cool
This doesnt work for me, I just get the #N/A Error
The download file is an edit only, if you will need to make your own spreadsheet to do calculations
eve-central.com is down for good. so far i tried alternatives but had no luck with them so far
eve marketer has a compatible api to eve-central, see api.evemarketer.com/ec/swagger.yaml
your coing doesn't work right, I had to edit it a bit for it to work. before editing, I went over your video and its coding a few times and I had it all down, also it wont save your =importXML when you give it to people
you need to have the code as just written in your description
Is there a way to automatically build that long formula? I was trying to figure out a way to pull all the market data in 1 giant pull.
+snowycarnivore Yeah i wrote a script to do it.
Keep getting #N/A when im trying to set up the TypeID Column
Don't think it works as well as it does in google, I can import the min but cannot specify whether it takes the information from buy or sell; "sell/min"
when i try to put in the first =vlookup for the abaddon i get an error message
stating that it has problems when analyzing
i am using google docs
can anyone help me ?
How does this update as the market prices change? What triggers the command to update data from eve API?
In your Excel ribbon, chose DATA and then Refresh All might help?
What if you are using Google Sheets
Ive actually found out that if i select the formula again and press enter, it will update prices, used excel 2013 though
Change =((D8+C8)/2)*0.0225 to =((D8+C8)/2)*0.0175 assuming a neutral standing with caldari for l4 broker & accounting.
Instead of typing all of those &typeid=, I did a JOIN instead. Following your example, this would look something like this.
=ImportXML(CONCATENATE(Math!B3, JOIN("&typeid=", B4:B27), Math!C3),"//sell/min")
"Yes, a JOIN statement sounds better...could you please give us an example?
Or could you do a video response with your suggestions? :)"
"Instead of typing all of those &typeid=, I did a JOIN instead. Following your example, this would look something like this.
=ImportXML(CONCATENATE(Math!B3, JOIN("&typeid=", B4:B27), Math!C3),"//sell/min")"
I like you
HiddenLotus9 If you look at the code, you'll see the JOIN statement in it. :)
Also, beware that Excel will have some issues on updating data from EVE Markets API too often.
My C column in Trade says $200,000,000.00 for everything?
Your numbers are not loading anymore. Since December 23rd I think. Neither my own spreadsheet. Can't figure out the problem. Someone who has an idea? Maybe a problem with Eve Market?
*****
Do you do multiple XML calls? Or are you using a different site?
I am getting URL error as well have you found a solution to this yet?
I figured it out. Since Google released the new version of Spreadsheets, if your working on the old Spreadsheets ImportXML is Broken. Create a new Spreadsheet and copy and paste all your data by clicking on the arrow to the right of each sheet name. When i did this the named ranges got messed up and i had to delete any reference to them and replace with a random number so i didnt get an error code when i hit enter, then retype or paste the reference back make sure you hit enter first. hope this helps good luck guys.
*****
How would coding a program help? Doesn't Excel already use multiple cores when calculating etc?
Ari Menachem holding enter in my case
Why or how does it fill in C5 on with the single import statement in C4? Does it return an array of values?
Also a lot of sell min prices are showing up as 150mil isk, which everything in profit is showing as losses (negative)
Ok - so few issues, if you misspell usesystem as usersystem, eve central will not warn you that something was wrong. Second issue, I had used C4 instead of C3 on the Math tab which means it wasn't even appending it.
All working now. :)
does anyone have a fix for thisds in excel 2010?? ican seem to get it to work
The sell value keeps coming up with #VALUE! and "error: The data could not be retrieved. Please check the URL. The URL is entered exactly as it is in the video. Does anyone else have this problem?
as far as typeID is concerned, why not use the evemarketdata API to get them?
And in column C&D, use a JOIN statement ffs. It will do all the work for you.
And for E-H, use array formulas, then if you want to add rows you dont have to fiddle with dragging, just edit the top cell, and you are good to go.
Yes, a JOIN statement sounds better...could you please give us an example?
Or could you do a video response with your suggestions? :)
There is an example on this sheet: docs.google.com/spreadsheets/d/1SroLubzFJa0thhQisO_ADZwEDo6KJOkqphxK3kK3eMI/edit?usp=sharing
ALL THE EXAMPLES ARE IN ROW 2
if i wanted to pick a different trading hub how would i do that?
Hey dont know if you got a answer for this. But you have to change the number in "$usesystem=" so to find the correct id for the system. Put this in your webbrowser: "api.eveonline.com/eve/CharacterID.xml.aspx?names=SOLARSYSTEMNAME" and change SOLARSYSTEMNAME for the system you want. Then you will see a value amongst the lines characterID="30000142" that is your id. Im beginner with this so cant use importxml to help you directly sorry
thank you!
how do you calculate taxes please? (awssommee video)
i think its 1,5 by default. so x2?
What software was he using, excel; open office what version?
Ignore this question its google drive. Im a dumbass
where can i find the updated typeid list?? help please
+Connor Law The link he has for the typeid is up to date. I can find the Command Destroyers on the list of items.
+snowycarnivore the fuzzysteve list in the description is up to date?
+Connor Law It is for me, I just clicked it this morning and I could find all of the new items implemented in the last patch.
Edit: Jsut download it and use the search command (ctrl + f) to look for the Stork or something similar.
Seeing as I am a complete idiot when it comes to codes and commands, is there any way you would be willing to simply share the ones you jut made?
Any idea how to fix sheets? wont work anymore :(
Hi
Does this still work, I am having issues and it was done in excel
i get #NA when using "=importXML(CONCATENATE(Math!B3,B4,Math!C3), "//Sell/Min")"
i think it works only for google spreadsheet thingy, not in excel. in osme older comment someone found some other function for excel
Hey, don't know if you still need this. But I wrote a custom function on excel , =EveGetJitaPrice ( id, TRUE) , that grabs the Jita price from Eve central (True receives buy orders, False returns sell orders).
To add it to excel:
*1.* Go to "Developer", "Visual Basic".
*2.* On the left side find your worksheet name, right click, insert, module.
*3.* A window for writing code will pop up and you can copy/paste my code below.
Afterwards in your excel sheet you'll be able to use my =EveGetJitaPrice function. To refresh prices on your excel sheet you can use the excel shortcut ctrl+alt+f9
Function EveGetJitaPrice(sName As String, Optional toSell As Boolean) As Variant
Dim oHttp As New MSXML2.XMLHTTP60
Dim xmlResp As MSXML2.DOMDocument60
Dim result As Variant
On Error GoTo EH
typeid = "typeid=" & sName
jita = "&usesystem=30000142"
sURL = "api.eve-central.com/api/marketstat?"
sItem = "typeID"
'open the request and send it
oHttp.Open "GET", sURL & typeid & jita, False
oHttp.Send
'get the response as xml
Set xmlResp = oHttp.responseXML
' get Item
If toSell Then
EveGetJitaPrice = xmlResp.getElementsByTagName("max").Item(0).Text
Else
EveGetJitaPrice = xmlResp.getElementsByTagName("min").Item(1).Text
End If
' Examine output of these in the Immediate window
Debug.Print sName
Debug.Print xmlResp.XML
CleanUp:
On Error Resume Next
Set xmlResp = Nothing
Set oHttp = Nothing
Exit Function
EH:
GetData = CVErr(xlErrValue)
GoTo CleanUp
End Function
awesome. i will try that later today.
it would be really great if you could provide some simple explanation what which part of the code does, so we could learn a little bit of something new.
Hey, I'm on Excel 2016. Wondering if you could maybe provide an example as to how we use it? I'm very interested. When I use the function in a cell and input the type id and TRUE or FALSE between parenthesis, it takes me to the VBA screen and the first line in the function code is yellow and it doesn't work
Reviewed the excel program today does not work ? Hmm
Anyone know how to do this for excel? The part I'm wondering about is pulling data from eve-central in a similar fashion thats done in this video.
I am assuming you mean the Jita Buy/Sell valuesUse this equation for Excel (2016 at least) =FILTERXML(WEBSERVICE(CONCATENATE(Math!B$3, B4, Math!C$3)),"//sell/min")Your boxes (Math!B$3, B4, Math!C$3) might be different
I am sorry for asking but, what do you mean by "Your Boxes"? I am sure you are talking about cells in excel but which cells?
can anyone help me with working out the fee & tax based off having lvl 4 in broker relations & lvl 4 accounting?
Is anyone else getting the error "not able to fetch URL" ? And if so, any idea how to fix it?
+Monk Trading i am have you figured out how to fix it?
Is this still working as intended?
Thank you!!!
Vlookup failed for Naga, Drake & Ferox.
mine doesn't want to show in currency.... any idea why?
+Timothy Brandenburg You have to format the numbers. If you're on GDocs, its just to the left of font.
Stop making my source of isk more competitive!
Same :( Anyone figure it out? I assume it's some combination of taxes & fees.
LOL, this would be an awesome and helpful tutorial, IF googledocs wouldn't be as crappy as it is! the syntax of most formulas change with the language. In Germany, formating numbers does not work properly, as can be seen in ziilion googlegroups threads.
To separate arguments, semicolons are used instead of commas, all I can think is COLON!!! Nice Vid though!
Having the same problem, Anyone solved this?