Brian Blanchett: Google Excel Forum


The purpose of this page is to attach documents I have mentioned in a post on the Google Group: Microsoft.Public.Excel.Programming Forum

Notes/Disclaimers:

* Every Excel tool on this page is not protected and is for educationally purposes only.

* Important: I have never taken any programming classes (I am an accountant/investment advisor) so practically everything I have learned is primarily due to the likes of the Excel MVPs (like Chip Pearson, John Walkenbach, Ron de Bruin, etc ...) as well as the many others who frequently post answers for the newbies like myself on the Google Programming Forum).

* The naming structure of the attachments will tell you the last time that I have updated/modified the Excel workbook. They will have some name then YYYY.MM.DD. I am incredibly anal about this ... which you (the user) should hopefully find helpful.

Oanda FX Template Web Tool: This template has two separate a relatively complicated features.

1) On the first worksheet there is a UDF that can extract the exchange rate (in USD) or cross-rate (e.g. - EUR to GBP) for any date that the user selects from Oanda.com database using an HTML link.

2) The second worksheet allows the user to extract the historical foreign exchange rates for any currencies for up to 2000 days.

[Add Attachment]

----------------

Yahoo! Finance Historical Adjusted Price Extraction Tool: This is a project that I have been working on with my twin brother (David) to extract historical ADJUSTED closing prices from Yahoo! Finance using multiple tickers and then automatically calculate the quarterly returns based upon the adjusted closing prices (still needs work!). This tool simply queries a CSV file from Yahoo! Finance based upon the selected criteria.

I am still working on integrating an additional tool to this template that will allow the user to enter a portfolio of tickers & the security weightings and then automatically run a regression analysis on the correlation of the returns. I will then use the historical correlations, returns, and different risk measures to create different MVO efficient portfolio and down-side risk optimized portfolio compared passively managed efficient portfolios (consisting of comparative iShare ETFs).

[Add Attachment]

----------------

Morningstar Style/Category: This tool queries Morningstar's web-site and can extract user specified information (e.g. - Style, Category, Foreign Holdings, Market Cap, etc ...) based on simply entering tickers. I am also working on adding additional intuitive code to automatically copy foreign country based investment % holdings.

[Add Attachment]

----------------

Excel Workbook Formula Comparison Tool: Using an API interface, this template will allow the user to select any two Excel workbooks and runs a looping macro to compare every formula, in every cell, on every worksheet in both workbooks. A comparison report is then created with a detailed listing of all exceptions. This tool has been strength tested against workbooks containing over 20 worksheets and in excess of 100,000 formulas per workbook and it only took this template about 10 seconds to compile the differences (it is incredibly efficient).

[Add Attachment]

----------------