Forum Discussion

nikhil0511's avatar
nikhil0511
Icon for Advocate I rankAdvocate I
5 years ago

Getting 52 week high and Low Currency exchange rate

Hello,

 

I have data with Products and their Prices in USD. I like to convert the prices into EUR.

As the prices can be dynamic, I like to calculate average of 52 week -high and low rates, and again averaging it with live current rate to get a appropriate euro price for my products.

 

I can get the Live Exchange rates to my data, and unable to get the rates of 52 week period.

 

MSN provides that information but unable to get that data into Power BI tables.

 

https://www.msn.com/en-gb/money/currencyconverter

 

Please help me on this

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Here is one way to do it using the Web connector and your url (choosing Table 1 in the popup).  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  You could also switch to the US url.  With this one, you can ration the US and EUR rows in a measure or column if needed.

     

    let
    Source = Web.BrowserContents("https://www.msn.com/en-gb/money/currencyconverter"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".cntrycol *"}, {"Column2", ".cnvrsncol *"}, {"Column3", ".chng-value *"}, {"Column4", ".chp-value *"}, {"Column5", ".cntrycnvrsn + *"}, {"Column6", ".chcpclub + *"}, {"Column7", ".mjcurrncs-data .rng52wkcol + *"}}, [RowSelector=".mcrow"]),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Table From Html",{{"Column1", "Currency"}, {"Column2", "Conversion"}, {"Column3", "Change"}, {"Column4", "Percent"}, {"Column5", "Price"}, {"Column6", "High"}, {"Column7", "Low"}})
in
    #"Renamed Columns"

      

    Pat

     

  • nikhil0511's avatar
    nikhil0511
    Icon for Advocate I rankAdvocate I
    5 years ago

    Hello again,

    I would like to write and get the same table into the excel file, 

     

    I tried to insert the code you have provided on in my excel power query, but it shows an error stating module missing.

     

    Can you help me by rewriting the same code suitable for the Excel power query?