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.

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.

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 !

@Veritasdio

10 жыл бұрын

Thx man

@Irgendwear96

10 жыл бұрын

Thanks that really helped me as well. o7

@christerjohanzzon

9 жыл бұрын

Thx, I did not know that Google was this dumb. Your info helped me. :)

I LOVE YOU! I can't wait to do this over the weekend! So amazingly helpful! Thank the Gods to your inception!

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 :)

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.

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.

@SHdrummerguy08

9 жыл бұрын

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

Amazing videos, looking forward to more.

very nice tutorial! looking forward to others.

Great! You're awsome to share your knowledge about this subject so others can use it too.

Another excellent video, keep up the good work.

This is the best thing ever I always wanted to do something like this and now I can Thanks so much great job!

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!

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.

@thomascarpenter5979

9 жыл бұрын

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.

I found this very helpful, thank you!

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.

I always wondered how people could import live data into excel files. Thanks for sharing.

Brilliant instruction!

would love to see new buisness insider vids and tutorials for googlesheets with the new system now.

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 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?

you could also just highlight the row and press ctrl D but oh well putting it all in also works

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.

Thanks for doing this.

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?

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!

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.

Very nice, thx for the explanation.

Great tutorial, thank you!

@yorgieb8834

10 жыл бұрын

Thank you Chris Murray, also for your input for Excel 2013!

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 think google docs is broken. My sheet won't import values just sits there with "loading..."

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!

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 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?

Ca i do it just like you in exel, only changing the ImportXML thing?

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.

When I try to open it in the eve browser (as I made it outside in firefox) it crashes the browser every time :(

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?

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

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?

Any idea how to fix sheets? wont work anymore :(

For the total fees and taxes, is it broker sell + broker buy + sales tax?

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)...

Does the buy tax and sell tax not differ from each other?

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.

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?

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.

@leverloos

10 жыл бұрын

check the second video

Mine just says #NAME? when trying to look up the typeid sheets for the trade tab?

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.

Keep getting #N/A when im trying to set up the TypeID Column

Is this still working as intended?

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.

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.

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 ?

Having the same problem, Anyone solved this?

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 ?

does anyone have a fix for thisds in excel 2010?? ican seem to get it to work

Thank you!!!

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.

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?

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!!!

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?

@docogg50

7 жыл бұрын

Should be (.028 + .016)/2

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?

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)

Holy crap... that is cool

My C column in Trade says $200,000,000.00 for everything?

can anyone help me with working out the fee & tax based off having lvl 4 in broker relations & lvl 4 accounting?

I fixed this. Change the name of the 3rd sheet to Typeid (lowercase end), and do the same in the data cell

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.

@tomwhitcombe7621

8 жыл бұрын

+snowycarnivore Yeah i wrote a script to do it.

This doesnt work for me, I just get the #N/A Error

@camerongostin1409

8 жыл бұрын

The download file is an edit only, if you will need to make your own spreadsheet to do calculations

@garremp

6 жыл бұрын

eve-central.com is down for good. so far i tried alternatives but had no luck with them so far

@Rubax52

6 жыл бұрын

eve marketer has a compatible api to eve-central, see api.evemarketer.com/ec/swagger.yaml

how do you calculate taxes please? (awssommee video) i think its 1,5 by default. so x2?

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.

Same :( Anyone figure it out? I assume it's some combination of taxes & fees.

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"

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?

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?

@Erik-hc8wc

7 жыл бұрын

Pelang3 it tells you when you buy or sell literally anything

Hey, this does not work any more. they have changed the location of the information

Reviewed the excel program today does not work ? Hmm

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??

@HawkNutz84

8 жыл бұрын

+Rehpic Oh god i wish i could delete that comment... So dumb didnt realize i had to type in the names manually

How does this update as the market prices change? What triggers the command to update data from eve API?

@jghagemann

7 жыл бұрын

In your Excel ribbon, chose DATA and then Refresh All might help?

@jakezilli70

7 жыл бұрын

What if you are using Google Sheets

@jghagemann

7 жыл бұрын

Ive actually found out that if i select the formula again and press enter, it will update prices, used excel 2013 though

Why or how does it fill in C5 on with the single import statement in C4? Does it return an array of values?

@leifashley

9 жыл бұрын

Also a lot of sell min prices are showing up as 150mil isk, which everything in profit is showing as losses (negative)

@leifashley

9 жыл бұрын

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 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.

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.

@pachydermepathetique

7 жыл бұрын

I've got the same problem!

Is anyone else getting the error "not able to fetch URL" ? And if so, any idea how to fix it?

@andenschmitt987

8 жыл бұрын

+Monk Trading i am have you figured out how to fix it?

mine doesn't want to show in currency.... any idea why?

@Sepu1tra

8 жыл бұрын

+Timothy Brandenburg You have to format the numbers. If you're on GDocs, its just to the left of font.

What software was he using, excel; open office what version?

@User198303

10 жыл бұрын

Ignore this question its google drive. Im a dumbass

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?

@HiddenLotus9

9 жыл бұрын

***** Do you do multiple XML calls? Or are you using a different site?

@AriMenachem

9 жыл бұрын

I am getting URL error as well have you found a solution to this yet?

@AriMenachem

9 жыл бұрын

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.

@HiddenLotus9

9 жыл бұрын

***** How would coding a program help? Doesn't Excel already use multiple cores when calculating etc?

@Irgendwear96

9 жыл бұрын

Ari Menachem holding enter in my case

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.

@camerongostin1409

8 жыл бұрын

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

@dancinmike2001

7 жыл бұрын

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?

Change =((D8+C8)/2)*0.0225 to =((D8+C8)/2)*0.0175 assuming a neutral standing with caldari for l4 broker & accounting.

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

where can i find the updated typeid list?? help please

@snowycarnivore

8 жыл бұрын

+Connor Law The link he has for the typeid is up to date. I can find the Command Destroyers on the list of items.

@Paratroopin

8 жыл бұрын

+snowycarnivore the fuzzysteve list in the description is up to date?

@snowycarnivore

8 жыл бұрын

+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.

If you are having trouble importing the Typeid File, instead of replace, click the New Sheet. It will import all the typeid's

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")"

@bukszpryt_

7 жыл бұрын

i think it works only for google spreadsheet thingy, not in excel. in osme older comment someone found some other function for excel

@8bit_pineapple

7 жыл бұрын

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

@bukszpryt_

7 жыл бұрын

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.

@jghagemann

7 жыл бұрын

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

yea same im just geting #NAME? with both formulas :(

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")

@HiddenLotus9

9 жыл бұрын

"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

@christerjohanzzon

9 жыл бұрын

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.

great work making a video with half the content of half of a project

I dont know why it wont work, copied all your steps but it wont work

Vlookup failed for Naga, Drake & Ferox.

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.

@christerjohanzzon

9 жыл бұрын

Yes, a JOIN statement sounds better...could you please give us an example? Or could you do a video response with your suggestions? :)

@Col_Crunch

9 жыл бұрын

There is an example on this sheet: docs.google.com/spreadsheets/d/1SroLubzFJa0thhQisO_ADZwEDo6KJOkqphxK3kK3eMI/edit?usp=sharing ALL THE EXAMPLES ARE IN ROW 2

I am trying to fix the formula in this sheet docs.google.com/spreadsheet/ccc?key=0AjoJQTa8aebpdG8tV0V4dW9qZmZzY3dMZGNITFVwUVE#gid=0 Any help will be apreciated