# Website scraping



## johnnyL (Mar 15, 2019)

Hi All!

I am trying to come up with some code that will 'scrape' a couple of values from a website and save those values into a couple of Excel cells, and possibly autoupdate those cells whenever the website updates those values that were scraped.

The site that I am trying to scrape from is https://www.poloniex.com/exchange#btc_dgb

I am trying to Capture the top value listed for 'price' under the "SELL ORDERS", and the top value listed for 'price' under the "BUY ORDERS", which are located about half way down the page.

The problem that I am encountering is that, as the list of 'buy' and 'sell' orders change, the code to capture the values that I seek also change. 

Sample code:

```
'
        Dim IE As New InternetExplorer
        Dim element As Object
'
'       Allow the Browser window, that we will be scraping values from, to be visible
        IE.Visible = True
'
        Application.StatusBar = "Loading website … https://www.poloniex.com/exchange#btc_dgb"        ' Update status bar to inform the user of what is occurring
'
'       Browser address that we will be scraping values from
        IE.navigate "https://www.poloniex.com/exchange#btc_dgb"
'
        Do While (IE.Busy Or IE.readyState <> READYSTATE_COMPLETE)
'           Allow mouse clicks and such while info is being scraped from internet ... Create a delay to allow the webpage to fully load before proceeding
            Application.Wait (Now + TimeValue("00:00:01"))                ' Delay for x seconds
            DoEvents
        Loop
'
        Dim Doc As HTMLDocument
        Set Doc = IE.document
'
        Application.StatusBar = "Gathering Data from website … https://www.poloniex.com/exchange#btc_dgb"    ' Update status bar to inform the user of what is occurring
'
'       Return SellPrice
        Range("poloniex_SellPrice_1").Value = Doc.getElementsByTagName("td")(3).innerText                ' <--- This line is InCorrect
'
'       Return BuyPrice
        Range("poloniex_BuyPrice_1").Value = Doc.getElementsByTagName("td")(2).innerText                 ' <--- This line is InCorrect
'
'-----------
'
```


Any help from the members here would be most greatly appreciated!


----------



## Domenic (Mar 16, 2019)

*Re: Requested Help with Website scraping*

Try...


```
Range("poloniex_SellPrice_1").Value = Doc.getElementById("sellOrderBookTable").getElementsByTagName("tr")(1).cells(0).innerText

Range("poloniex_BuyPrice_1").Value = Doc.getElementById("buyOrderBookTable").getElementsByTagName("tr")(1).cells(0).innerText
```

Hope this helps!


----------



## johnnyL (Mar 16, 2019)

*Re: Requested Help with Website scraping*



Domenic said:


> Try...
> 
> 
> ```
> ...



Domenic, Thank You So much for your Suggestion. In all of my googling, I never realized that combining was allowed. I will test your suggestion out in the near future, rest assured. Any thoughts on code for autoupdating cells? Thank you again for your input!


----------



## Domenic (Mar 17, 2019)

*Re: Requested Help with Website scraping*

It looks like you can either continue accessing the data using the HTTP method that you're currently using, or you can use APIs.  For the former, you can simply manually run the code as desired or you can automatically have your code run at set intervals using the OnTime method of the Application object (you can search Google for examples).  For the latter, have a look at the following link...

https://docs.poloniex.com/#introduction


----------



## johnnyL (Mar 17, 2019)

*Re: Requested Help with Website scraping*



johnnyL said:


> Domenic, Thank You So much for your Suggestion. In all of my googling, I never realized that combining was allowed. I will test your suggestion out in the near future, rest assured. Any thoughts on code for autoupdating cells? Thank you again for your input!



Domenic, sorry for the late response back.

I tested your above suggested code out today and it worked marvelously, after I introduced a delay into the code to allow it to load fully! I also had a chance to look at how it works, as I understand it, and I was able to adapt it to a few other websites that I was wanting to scrape from, again, Thanks to you for that !!!

There remains one other site currently that I am having difficulty with scraping data from. That website is https://www.livecoin.net/en/trading/DGB_BTC

I would be most grateful if you, or another member, would assist me in scraping the top value listed for 'price' under the "SELLING", and the top value listed for 'price' under the "BUYING" .

In the meantime I am going to check out the link you suggested in your last post to see if I can figure out the "autoupdating" portion of my question.

Again, Thank you so much for your time, and all of the other members here that take the time, to read my questions!


----------



## BrandonBerner (Mar 17, 2019)

*Re: Requested Help with Website scraping*

Take this with a grain of salt, but as for the "autoupdating" feature, what if you set a timer for your scrip to run ever 30 minutes (or whatever interval you prefer). 

This would would ensure that you always have an updated value every 30 min.

You could take it a step further and add a condition: IF the newly scraped numbers are the same as whats currently in the worksheet, dont copy. 

_Ps. Im brand new, so use caution when taking my advise. If it did help you, dont forget to "*Thank you for posting this*" and or "*Like this post*" _


----------



## BrandonBerner (Mar 17, 2019)

*Re: Requested Help with Website scraping*

As for the website https://www.livecoin.net/en/trading/DGB_BTC it could be blocking your request. Some websites dont like people scraping their information, and therefor will implement speed bumps to make it harder to scrape.

You could try reverse engineering the website in question, by understanding How to prevent crawlers, and scrapers on a website.

_Ps. Im brand new, so use caution when taking my advise. If it did help you, dont forget to "*Thank you for posting this*" and or "*Like this post*" _


----------



## johnnyL (Mar 17, 2019)

*Re: Requested Help with Website scraping*

Domenic, Thank you for that link, But I am sad to say that I would not know how to begin to use that information. That is above my knowledge/paygrade at this point in time.


----------



## johnnyL (Mar 17, 2019)

*Re: Requested Help with Website scraping*



BrandonBerner said:


> Take this with a grain of salt, but as for the "autoupdating" feature, what if you set a timer for your scrip to run ever 30 minutes (or whatever interval you prefer).
> 
> This would would ensure that you always have an updated value every 30 min.
> 
> ...



BrandonBerner, Thank you so much for your reply! 

A timer is one approach, as you suggested, although it would need to be much quicker than you suggested. I was hoping that Excel would have a more elaborate approach. I have never coded a timer executed code before, but I think I could google that with relative ease. I have experience with delays and such, so I am assuming it would involve similar coding.

Thank you so much for your suggestion.

Edit: I would like to add, by the time all of the cells updated, I am sure that the initial timer set would have expired, so basically no need for a timer, as such, basically it would be constantly looping to update.


----------



## johnnyL (Mar 17, 2019)

*Re: Requested Help with Website scraping*



BrandonBerner said:


> As for the website https://www.livecoin.net/en/trading/DGB_BTC it could be blocking your request. Some websites dont like people scraping their information, and therefor will implement speed bumps to make it harder to scrape.
> 
> You could try reverse engineering the website in question, by understanding How to prevent crawlers, and scrapers on a website.
> 
> _Ps. Im brand new, so use caution when taking my advise. If it did help you, dont forget to "*Thank you for posting this*" and or "*Like this post*" _



BrandonBerner, I don't believe that my requests are being blocked because I can scrape one of the values that I am seeking, I just can't seem to to scrape the second value that I seek from that site.


----------



## johnnyL (Mar 15, 2019)

Hi All!

I am trying to come up with some code that will 'scrape' a couple of values from a website and save those values into a couple of Excel cells, and possibly autoupdate those cells whenever the website updates those values that were scraped.

The site that I am trying to scrape from is https://www.poloniex.com/exchange#btc_dgb

I am trying to Capture the top value listed for 'price' under the "SELL ORDERS", and the top value listed for 'price' under the "BUY ORDERS", which are located about half way down the page.

The problem that I am encountering is that, as the list of 'buy' and 'sell' orders change, the code to capture the values that I seek also change. 

Sample code:

```
'
        Dim IE As New InternetExplorer
        Dim element As Object
'
'       Allow the Browser window, that we will be scraping values from, to be visible
        IE.Visible = True
'
        Application.StatusBar = "Loading website … https://www.poloniex.com/exchange#btc_dgb"        ' Update status bar to inform the user of what is occurring
'
'       Browser address that we will be scraping values from
        IE.navigate "https://www.poloniex.com/exchange#btc_dgb"
'
        Do While (IE.Busy Or IE.readyState <> READYSTATE_COMPLETE)
'           Allow mouse clicks and such while info is being scraped from internet ... Create a delay to allow the webpage to fully load before proceeding
            Application.Wait (Now + TimeValue("00:00:01"))                ' Delay for x seconds
            DoEvents
        Loop
'
        Dim Doc As HTMLDocument
        Set Doc = IE.document
'
        Application.StatusBar = "Gathering Data from website … https://www.poloniex.com/exchange#btc_dgb"    ' Update status bar to inform the user of what is occurring
'
'       Return SellPrice
        Range("poloniex_SellPrice_1").Value = Doc.getElementsByTagName("td")(3).innerText                ' <--- This line is InCorrect
'
'       Return BuyPrice
        Range("poloniex_BuyPrice_1").Value = Doc.getElementsByTagName("td")(2).innerText                 ' <--- This line is InCorrect
'
'-----------
'
```


Any help from the members here would be most greatly appreciated!


----------



## BrandonBerner (Mar 17, 2019)

*Re: Requested Help with Website scraping*



johnnyL said:


> BrandonBerner, Thank you so much for your reply!
> 
> A timer is one approach, as you suggested, although it would need to be much quicker than you suggested. I was hoping that Excel would have a more elaborate approach. I have never coded a timer executed code before, but I think I could google that with relative ease. I have experience with delays and such, so I am assuming it would involve similar coding.
> 
> Thank you so much for your suggestion.



*What about:
*
1. Create a new Sheet in your workbook called (EX: Scrape Log) with 3 columns.
     - Column A - BUY
     - Column B - SELL
     - Column C - Time Stamp

2. Scrape website for Top Buy|Sell on a loop.
     - Every few seconds your Scrape Log will have another entry. 

3. Create a table, and filter your log based on newest date to oldest date.
     - Now you have a log which will always show you the current BUY and SELL on A2:B2


We could take it a step further, and assume you want to only display the most recent BUY and SELL price.

1. In another sheet, you could create a formula
     - 
	
	
	
	
	
	



```
='Scrape Log'! A2:C2
```
          - This will print out the most recent collected data only, and will auto update.


----------



## Domenic (Mar 17, 2019)

*Re: Requested Help with Website scraping*

For your other website, try...


```
Range("A2").value = Doc.getElementsByClassName("orderBookList___2mrBk")(0).querySelector("div.orderBookLeft___1D43u").innerText

Range("B2").value =  Doc.getElementsByClassName("orderBookList___2mrBk")(1).querySelector("div.orderBookLeft___1D43u").innerText
```


----------



## johnnyL (Mar 18, 2019)

*Re: Requested Help with Website scraping*



Domenic said:


> For your other website, try...
> 
> 
> ```
> ...



Domenic, Once again you are spot on perfect in assisting me! TY so much once again!


----------



## johnnyL (Apr 3, 2019)

*Re: Requested Help with Website scraping*



johnnyL said:


> ...
> 
> There remains one other site currently that I am having difficulty with scraping data from. That website is https://www.livecoin.net/en/trading/DGB_BTC
> 
> ...



It seems that I have ran into another problem with scraping data from that site.  

Domenic's suggested code worked flawlessly to scrape the buy and sell prices from that site, but I, for the life of me, can't seem to figure out the code that is required to scrape the values to the right of both of those values, ie the top values under the 'amount' columns on that page.  I have figured out the code needed to do this for the other pages that I requested help from.  But this page is being difficult.

Another lifeline would be most appreciated.


----------



## johnnyL (Apr 3, 2019)

*Re: Requested Help with Website scraping*

WEEEEEEEE!!!! I finally figured it out! After messing with it some more today I finally got it.


```
Range("livecoin.net_SellPrice_2").Value = num(Doc.getElementsByClassName("orderBookList___2mrBk")(0).querySelector("span").innerText)
```

Again, Thank you so much for everyone that has participated in this thread, as well as others.


----------



## rdrew83 (Apr 12, 2019)

*Re: Requested Help with Website scraping*

Hey Guys,
I'm new to this, but I was wondering if someone could assist me with the vba to extract the inner data and add the value to a cell?
Below is the html that I am trying to get the data from.

For Example, if I just wanted to get the "*$8,153.00 / Remaining*"

<td colspan="3">$10,000.00 / Contract<span style="margin-left:15px;">(In Plan Network)</span><br><span class="sectionHeader">Message</span>: EMBEDDED
</td>
<td colspan="3">$8,153.00 / Remaining<span style="margin-left:15px;">(In Plan Network)</span><br><span class="sectionHeader">Message</span>: EMBEDDED
</td>
<td colspan="3">$3,153.00 / Remaining<span style="margin-left:15px;">(In Plan Network)</span><br><span class="sectionHeader">Message</span>: NON-EMBEDDED
</td>


----------



## johnnyL (Apr 16, 2019)

*Re: Requested Help with Website scraping*



rdrew83 said:


> Hey Guys,
> I'm new to this, but I was wondering if someone could assist me with the vba to extract the inner data and add the value to a cell?
> Below is the html that I am trying to get the data from.
> 
> ...



Got the webpage that you are trying to scrape that from?


----------

