Build & Test Status	License	Version	Wiki	Changelog

FFE (Financial Functions for Excel)

FFE is an Excel Add-in and adds new financial functions to Microsoft Excel.

The FFE Add-in currently provides the following functions:

1. Query of stock information (e.g. price) from different stock data providers and websites.

   • Alpha Vantage

   • Consorsbank

   • justETF

   • Yahoo Finance

2. Validation and calculation of IBAN and bank data (e.g. BIC) via the Openiban webservice. (ℹ New in version 1.2.0)

Highlight: With FFE, new websites and thus new user-defined functions can be easily added by editing a text file (SSQ Function).

"QueryInformation": {
    "Name": "QYF",
    "Description": "Yahoo finance website query.",
    "Author": "Leland Grunt",
    "Author Email": "leland.grunt@gmail.com",
    "Version": "1.0",
    "Version Date": "2019-12-29",
    "Help Topic": "https://github.com/LelandGrunt/FFE",
    "Help Link": "https://github.com/LelandGrunt/FFE",
    "Provider": "finance.yahoo.com",
    "Enabled": true,
    "ExcelArgNameStockIdentifier": "Ticker",
    "ExcelArgDescStockIdentifier": "Yahoo-Ticker",
    "Comment": ""
},
"QueryParameter": {
    "Url": "https://finance.yahoo.com/quote/{TICKER}",
    "StockIdentifierPlaceholder": "{TICKER}",
    "CssSelector": ".Fz\\(36px\\)",
    "Locale": "en-US",
    "Parser": "AngleSharp"
}

The FFE Add-in currently adds in detail the following user-defined functions:

Group	Function	Description
AVQ	QAVQ	Query of stock values via the Alpha Vantage GLOBAL_QUOTE API.
AVQ	QAVID	Query of stock values via the Alpha Vantage TIME_SERIES_INTRADAY API.
AVQ	QAVD	Query of stock values via the Alpha Vantage TIME_SERIES_DAILY API.
AVQ	QAVDA	Query of stock values via the Alpha Vantage TIME_SERIES_DAILY_ADJUSTED API.
AVQ	QAVW	Query of stock values via the Alpha Vantage TIME_SERIES_WEEKLY API.
AVQ	QAVWA	Query of stock values via the Alpha Vantage TIME_SERIES_WEEKLY_ADJUSTED API.
AVQ	QAVM	Query of stock values via the Alpha Vantage TIME_SERIES_MONTHLY API.
AVQ	QAVMA	Query of stock values via the Alpha Vantage TIME_SERIES_MONTHLY_ADJUSTED API.
AVQ	QAVTS	Wrapper for all Alpha Vantage time series APIs (except GLOBAL_QUOTE).
CBQ	QCB	Query of stock prices from the Consorsbank website.
CBQ	QCBF	Query of stock prices from the Consorsbank website with currency format.
SSQ	QJE	Query of stock prices from the justETF website.
SSQ	QYF	Query of stock prices from the Yahoo Finance website.
Openiban	QBIC	Query of BICs (Business Identifier Codes).
Openiban	QIBAN	Query of IBANs (International Bank Account Numbers).
Openiban	QCOUNTRIES	Query of country names and codes supported by the function QIBAN.

FFE uses the Excel-DNA library under the hood. Excel-DNA brings the full power of .NET to Excel.

Table of Content

• Installation
• Usage Examples
• Known Issues
• Support
• Documentation
• Roadmap
• Contributing
• Changelog
• License
• Credits
• Legal Statement

Installation

The installation is as simple as copying a file. FFE itself is just one file (FFE[x32|x64].xll) which Excel can load as an Add-in. You can load the Add-in file in two ways:

1. Copy the FFE Add-in file (FFEx32.xll or FFEx64.xll) to the folder %USERPROFILE%\AppData\Roaming\Microsoft\AddIns\ or %APPDATA%\Microsoft\AddIns\. In this case, Excel automatically loads the FFE Add-in on startup. Choose FFEx32.xll for a Excel version in 32-bit and FFEx64.xll for a Excel version in 64-bit. This is the easiest way.
2. In Excel via File | (Excel) Options | Add-ins | Excel Add-Ins (< Excel 2013) or via Ribbon Developer | (Excel) Add-ins (>= Excel 2013). If Developer Ribbon is not available, add via File | Options | Customize Ribbon and then enable Developer in the Customize the Ribbon list. Change your Trust Center Settings, if necessary.

Hint: The folder where the FFE file is located is used as a root folder for FFE. Log files (if logging is enabled) are created, as well as a customized SsqUdf.json file is searched, in this folder.

Additional steps for the AVQ function group (optional):

For the Alpha Vantage provider, a API key is mandatory.

1. Get a free Alpha Vantage API Key here (only e-mail is required).
2. Set the Alpha Vantage API Key via the Set API Key button in the new ribbon tab FFE.

The FFE Add-in was tested with:

• Excel 2003 (32-bit)
• Excel 2007 (32-bit)
• Excel 2010 (32-/64-bit)
• Excel 2013 (32-/64-bit)
• Excel 2016 (32-/64-bit)
• Excel 2019 (64-bit)

For Excel 2003 no ribbon interface is available. Use and edit the FFE.config file to set the FFE options (incl. Alpha Vantage API Key). Save the file in the FFE root folder where the FFE Add-in (FFEx32.xll|FFEx64.xll) is located.

AutoUpdate/New Version Check

FFE has a check for a new update on Excel startup. The automatic update check can be disabled via ribbon button FFE | Options | Check Update on Startup. Check manually via FFE | Info | Check for Update. The update may contain new FFE user-defined functions and/or updates existing ones.

Usage Examples

Use FFE in your personal Excel based asset reporting to update your current stock values.

Function	Excel Formula	Result
QAVQ	=QAVQ("MSFT")	Returns the latest stock price of Microsoft Corporation.
QAVID	=QAVID("MSFT","volume")	Returns the latest trading volume of Microsoft Corporation.
QAVD	=QAVD("MSFT","high",-2)	Returns the "high" stock quote of Microsoft Corporation from two days ago.
QAVDA	=QAVDA("MSFT","open",5)	Returns the 5th "open" stock quote from the Alpha Vantage query result of Microsoft Corporation.
QAVW	=QAVW("MSFT","volume",,"2020-01-03")	Returns the trading volume of Microsoft Corporation of 2020-01-03 from the TIME_SERIES_WEEKLY API.
QAVWA	=QAVWA("MSFT",,,"2020-01-01",TRUE)	Returns the "close" stock quote of Microsoft Corporation of 2019-12-27. Hint: No new year's day stock quote is available, latest available stock quote is then 2019-12-27 (best_match = TRUE).
QAVM	=QAVM("MSFT")	Returns the recent "close" stock quote of Microsoft Corporation from the TIME_SERIES_MONTHLY API.
QAVMA	=QAVMA("MSFT","high")	Returns the recent "high" stock quote of Microsoft Corporation from the TIME_SERIES_MONTHLY_ADJUSTED API.
QAVTS	=QAVTS("MSFT","high","weekly",-2)	Returns the "high" stock quote of Microsoft Corporation from the TIME_SERIES_WEEKLY API from two weeks ago.
QCB	=QCB("US5949181045")	Returns the current stock ask price of Microsoft Corporation from the Consorsbank website.
QCB	=QCB("US5949181045","GAT")	Returns the current stock ask price of Microsoft Corporation of the stock exchange Tradegate (=GAT) from the Consorsbank website.
QJE	=QJE("IE00B4L5Y983")	Returns the current stock price of ETF "iShares Core MSCI World UCITS ETF USD (Acc)" from the www.justetf.com website.
QYF	=QYF("MSFT")	Returns the current real time stock price of Microsoft Corporation from the Yahoo Finance website.
QBIC	=QBIC("DE89370400440532013000")	Returns the BIC (Business Identifier Code) of the IBAN DE89370400440532013000.
QIBAN	=QIBAN("DE", "37040044", "0532013000")	Returns the IBAN (International Bank Account Number) for given bank code and account number for the given country.
QCOUNTRIES	=QCOUNTRIES()	Returns an array of country names and codes supported by the function QIBAN.

Known Issues

Excel starts without opening the start screen or a blank workbook. This is a bug in Excel (at least Office 365 MSO version 16.0.11328.20468) whenever any .xll is installed. The issue appears to be fixed in version 16.0.12013.20000.

Lost FFE settings The FFE settings are saved in the %LOCALAPPDATA%\Microsoft_Corporation\FullTrustSandbox(Excel-DN_Path_<Id>\<Excel Version>\user.config file. If a new Office/Excel version with a new build number is installed, then a new sub folder is created, and the existing setting/config file is not found. Since FFE version 1.2.0 the FFE settings are portable and saved directly in the FFE root folder where also the FFE Add-in (FFEx32.xll|FFEx64.xll) is located.

Support

Via Issues or Discussions.

Please check the known issues and the existing issue entries first before creating a new one.

Documentation

Go to Wiki

Roadmap

• AVQ: Addition of other available Alpha Vantage APIs (e.g. CURRENCY_EXCHANGE_RATE).

• FFE Framework: Plugin interface for custom user-defined functions/methods.

• FFE Framework: AutoUpdate of FFE Add-in (not only check).

• FFE Framework: User-defined functions as asynchronous calls.

• FFE Framework: Replacement of ribbon icons by nicer and more meaningful ones.

• FFE Framework: Addition of a JSON parser.

• FFE Framework: Addition of RTD-based functions/RTD support.

• SSQ: Adding of additional providers (voting via GitHub reactions).

• SSQ: Adding of other web scraping libraries (e.g. OpenScraping).

• SSQ: Grouping of SSQ functions with same website/provider.

• SSQ: User interface for adding and editing SSQ query definitions (editing of SsqUdf.json).

• SSQ: Implementing type converter for returned value.

• SSQ: Selection by index for XPath and CssSelector query methods (like RegExMatchIndex).

• New UDF: https://frugalisten.de/rechner/ via MS Solver Foundation.

Contributing

Contributors are welcome!

Project contributing is possible in several ways:

1. Add new user-defined functions by add new web query providers via the FFE SSQ function (for IT Experts). This is the easiest way and requires no (deeper) programming skills. Just edit the SsqUdf.json file and define declaratively which value should be extracted from a website and how the query should be displayed in Excel. Go to SSQ for a detailed description and to SsqUdf.json for a how-to description. Example is available.
2. Add new user-defined functions by writing new C# (static) methods (for Developers). The method must return a type that Excel can interpret (usually simple types such as string or double). Go to Excel-DNA for more information and a getting-started guide. FFE and Excel-DNA attributes are necessary to register the methods/user-defined functions in the FFE context in Excel. Example is available.
3. Extend the FFE functionality (for Developers). See Roadmap.
4. Analyze and resolve issues or answer questions (for FFE Experts). See Issues or Discussions.

Changelog

Go to Changelog.

License

FFE is licensed under the MIT license.

Credits

Major dependencies are:

• Excel-DNA

• HtmlAgilityPack

• AngleSharp

• Serilog

Legal Statement

FFE use web scraping to extract data from websites. Web scraping may be illegal in specific/your countries or prohibited by the terms of use of the website. Do not bypass the web scraping anti-block-techniques of the websites. Only use FFE for your private and do not extract data in mass.