Forum Discussion
I need Help with the web import in excel
Hi Manuel,
I guess, you imported the data from a text file or a CSV file? It looks like that the values have been imported as text ($ 123...). Therefore, when adding the values by using a formula, you get an error. So, please transform the values from text to values.
An easy way to import data from text is to use the Get & Transform features (Daten abrufen und transformieren) from your Excel version. For example, in the Data tab, click on From Text/CSV (Daten / Aus Text/CSV) and select your text file or CSV file. Then, a dialog opens which looks like the following sample screenshot in German:
In this window, click on Edit (Bearbeiten) for opening the query editor (Abfrageeditor). Now you have a lot of options to transform your data, for example changing the data format and so on.
Once you are done, click on Close & Load (Schließen & laden) to import the data on a sheet. And then, you can perform your calculations.
Best,
Mourad
Hallo Mourad, thanks for helping me,
Ne is it not a TEXT/CSV Date i want to Import the Live Price form https://www.worldcoinindex.com/
to Excel, and make a Poertfolio so i can see how much i have earn with my BTC Coins.
I dont want to put everytime the Prive munuell in Excel
Best regards & thx
Manuel
Hello,
the Get & Transform features lets you import data from websites too. I attached a sample, where I did some formattings in the query editor. You may do additional formattings, e.g. replace M and B by their meanings. Note: I did this in Excel German, so the steps are in German.
Best,
Mourad
Ich bin aus Österreich, also können wir auch Deutsch schreiben, ich frage mich wie du das gemacht ?
Ich hab hab es selbst nicht hinbekommen, aber die Excel datei die du mir zugeschickt hast funktionier nice.
Ich würde gern wissen wie das geht? Du hast mir die Top 100 Coins Kurse geschickt, ich brauch aber die top 200, also 100 weitere.
Kannst du mir bitte veraten wie du das gemacht hast ?
Danke
Manuel
Hello Manuel,
this in an English speaking community. Therefore, I think, the main language should be English. If I would only answer in German, this would not be useful for other readers who don't speak German.
So, my answer in English first: on the Data Tab, you'll find the command Queries & Connections, which opens the corresponding task pane. A double click on a query opens the query editor in a separate window, as shown in my screenshot in my reply above. You said, that you need more data to retrieve from the webpage. The first page shows the first 100 rows and, if you navigate to the next page, shows the next 100 rows. The second page is another url on the website, but has the same layout. So you can create 2 queries in PowerQuery (Get & Transform) and combine the two queries into one query. I have uploaded a new sample file, where you can see how it is made.
Ok, jetzt noch in Deutsch: wenn Du im Menüband auf den Reiter Daten gehst und dort den Befehl Abfragen und Verbindungen aufrufst, erscheint ein Aufgabenbereich mit allen Abfragen. Doppelklicke eine dieser Abfragen und Du gelangst zum PowerQuery-Fenster. Und dort kannst Du dann alle entsprechenden Operationen durchführen. Die von Dir genannte Website listet die Einträge ab Zeile 101 auf einer weiteren Seite auf, die genauso aufgebaut ist. In PowerQuery kannst Du 2 Abfragen kombinieren, was ich mal in einer neuen Beispieldatei für Dich gemacht habe.
Best,
Mourad
Thank you thank you thank you so much Bro now it works! how do you do this ?
Thank you Bro ;)
Mourad, as a comment - here is always two digits in decimal part in right columns, thus we may replace first dot on nothing and after that M and B on 4 and 7 zeroes accordingly plus apply Whole Number type finally. If do step by step in UI.
