• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

converting currency

thanks Dave, is there anyway that it can automatically update based on the daily rate or would this be to complicated?
 
What do you mean by automatically update?


If you mean manually type the conversion rate in each day you can keep the column with the euro values and put a new column for the sterling values then base the calculation on the cell containing the conversion rate.


eg. Euros in column A (starting at A2), Sterling in B(starting at B2), conversion rate in cell C2


format column B to currency and £ as the symbol


in cell B2
Code: 
=(A2*C$2)


drag down


If you mean let the spreadsheet find the conversion rate itself it become more complex. You will have to write a vba code to obtain the rate from a compatible website and then feed this into your calculations
 
Thanks Dave it does sound a bit complex so I'll do my bit as of todays rate and let the powers that be decide if they want this changed before quoting. If I was to convert the opposite way, ie £ to Euro would I divide the current rate rather than multiply?
 
There is another way to automate it.


Open a new sheet on your workbook.


Excel 2003 : go to "Data" - "Import External Data" - "New Web Query"


Excel 2007+ : click the "Data" tab. Select "From Web" in the Get External Data group


A browser will open


go to: http://uk.moneycentral.msn.com/investor/market/rates.asp?Symbol=


Make sure British Pound is selected on the drop down


At the top left of the table you will see a Yellow arrow. Click that then click on import.


It will ask what cell to import into. $A$1 should be fine.


It will then import all exchange rates


You can then point your calculation to the relevant cell in this table


if you click in one of the cells in this table you should get the external data toolbar. You can then select properties and change the refresh time (i.e. every 5 minutes)
 
Hi, Safequip!


I've widely using techniques like described by DaveTurton. If you use the search feature at top right of the page, you'd find a diverse menu like:

http://chandoo.org/forums/topic/macros-web-query-huge-headache-can-anyone-help-me

http://chandoo.org/forums/topic/specific-data-and-html-beyond-excels-capabilities

http://chandoo.org/forums/topic/help-please-need-code-macro


It's just a matter to choose the more suitable, then check if your wanted website exposes data in tables or in another way, and get your hands dirty with the code.


Just advise if any issue.


Regards!
 
You must log in or register to reply here.
Back
Top