Extract data in different currencies

[osx99]

I have the following code which extracts a webpage to excel. However, the price is a mix of Euros, Pounds and dollars, how do I identify what currency these are to allow a conversion?

Sub Extract()

    Dim USD As Long
    Dim EUR As Long
    Dim GBP As Long
    
    Dim MaxSale As Currency
    Dim MinSale As Currency
    Dim AvgSale As Currency
    Dim theRange As Range
    
    Set theRange = Range("d:d")
            
    EUR = 0.677861      ' Set exchange rates for your country - your own currency should equal 1
    USD = 0.497392      ' Set exchange rates for your country - your own currency should equal 1
    GBP = 1             ' Set exchange rates for your country - your own currency should equal 1


Worksheets("Test Sheet").Activate
    Range("A:z").Select
    Selection.ClearContents
 
          With ActiveSheet.QueryTables.Add(Connection:="URL;[URL unfurl="true"]http://www.discogs.com/sell/list?release_id=82871",[/URL] _
              Destination:=Range("a1")) 'write web-page to sheet

              .BackgroundQuery = True
              .TablesOnlyFromHTML = False
              .Refresh BackgroundQuery:=False
              .SaveData = True


          End With
 
 
MaxSale = Application.Max(theRange)
MinSale = Application.Min(theRange)
AvgSale = Application.Average(theRange)



 MsgBox MaxSale & " " & MinSale & " " & AvgSale


End Sub

 

[WalkerEvans]

I am going to presume for the moment that you have products with a standard base-price per item in one of these currencies. If that is indeed the case you should be able to determine by the amount being charged for one item which curreny is involved. For example, if you sell Twidgets for GBP 2.00 each, any being sold for a unit price of 4.02 would be sold using USD rates. Likewise, any being sold for 2.95 each would sold using EUR rates.

Unless I missed something this should yield the result you need.

"A committee is a life form with six or more legs and no brain." -- L. Long

 

[PHV]

Some notes:

Sub Extract()
Dim USD As [!]Currency[/!]
Dim EUR As [!]Currency[/!]
Dim GBP As [!]Currency[/!]
...
End With
[!]Dim c As Range
For Each c In theRange
  Select Case Left(c, 1)
  Case "€": c.Offset(0, 1) = EUR * Val(Mid(c, 3))
  Case "$": c.Offset(0, 1) = USD * Val(Mid(c, 3))
  Case "£": c.Offset(0, 1) = GBP * Val(Mid(c, 3))
  End Select
Next
Set theRange = Range("E:E")[/!]
MaxSale = Application.Max(theRange)
MinSale = Application.Min(theRange)
AvgSale = Application.Average(theRange)
MsgBox MaxSale & " " & MinSale & " " & AvgSale
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[osx99]

The above works fine, now my problem is that on these particular web page extracts

Dollars output as Format numbers General with a $ in the cell
Pounds output as Format number currency with no £ in the cell
Euros output as Format number General with no € in the cell

So the above code only finds the Case "$" and pounds and euros are the same but with different formatting

Is it possible to test for the cell format or is there a better way?

Thanks

 

[PHV]

Have a look at the NumberFormat property of the Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886