Excel Connect Functions Overview

Excel Connect allows users to leverage Alpha Theory formulas in Excel to both upload and retrieve data to and from their Alpha Theory environment. Some of the most commonly-used formulas are described below:

Asset Functions

• =ASSET_LOOKUP(“fundName”, “tickerName”)

  • You can now use the Alpha Theory ticker, but you can still use “AssetID” to ensure that we’re connecting your Excel file to the exact right asset in Alpha Theory. This function looks up the Asset ID for the name you are trying to work with inside of Excel Connect.

  • EXAMPLE: =ASSET_LOOKUP(“Standard Fund”, “GE”) = 71497

  • AssetID is shared across funds if research is shared

  • AssetID is unique if research is restricted

  • NOTE: You can double check the Asset ID in Alpha Theory by going to the column chooser and adding “Asset ID” to your view.

• =ASSET_SET(“fundName”, “tickerName”, “assetProperty”, valueToSet)

  • This function is used to set Fund Profile Adjustments and Asset Overrides in the application, directly from Excel Connect.

  • Accepted Asset Properties that can be used with this function can be accessed here.

• =ASSET_GET(“fundName”, “tickerName”, “assetProperty”)

  • This function pulls asset properties from Alpha Theory into Excel.

  • Example: shares, sector tag, analyst, etc.

  • Identifier=AT ticker

  • Accepted Asset Properties that can be used with this function can be accessed here.

• =CUSTOMFIELD_SET("tickerName", "customFieldName", valueToSet, "Rationale", "fundName")

  • This function allows users to update their confidence checklists and other custom fields in Alpha Theory directly from their Excel workbook.

  • Users can specify the custom field name, and then input the value they would like to set in the application.

    • NOTE: The custom field name and values MUST match exactly what they are in Alpha Theory.

  • This function uploads to a specific asset in a user's fund, using the unique Alpha Theory ticker.

  • The rationale field points to the notes of the custom field - users will find this under the custom field name when in the application.

  • Value is set once the user clicks the "Upload Data" button on the Alpha Theory ribbon.

• =CUSTOMFIELD_GET("tickerName", "customFieldName", "customFieldProperty", "fundName")

  • This function allows users to pull checklist and other custom field values directly from the application into Excel.

  • To do so, users simply provide the Alpha Theory ticker or asset ID and the name of the custom field

    • The optional customFieldProperty field defaults to the value, so all Excel Connect needs is the Alpha Theory ticker or asset ID and the name of the Checklist item or custom field

  • The custom field name must match the name of the field in the application

  • Other custom field properties can be retrieved using this function - for a list of available properties, refer here.

• =TRADE_SET("fundName","tickerName", Shares, unitCost)

  • This function allows user to set share counts directly from Excel Connect.

  • This function will override trades currently in the application, and apply the input shares for the applicable asset in Alpha Theory.

  • NOTE: The asset for which a trade is being set must already exist in the application.

    • For information on adding an asset to a portfolio, click here.

• =SHOW_ASSETS_TABLE(“fundName”)

  • An Array Function (press ENTER to access), to show the assets per fund, including status.

  • NOTE: You can find all Alpha Theory tickers for your assets using this function, which can then be used for all other functions in this article.
• =SHOW_CUSTOMFIELDS_TABLE()
  • An Array Function (press ENTER to access), to show all custom fields (like checklist and other custom field items) present per fund.

Scenario Functions

• =SCENARIO_ENTRY_FULL("tickerName", "scenarioName", priceTarget, probability, occurrenceDate, "rationale", valuationMethod, estimate, multiple, estimateDate, sharesOutstandingMillion, netDebtMillion, calcPriceTargetFlag, "fundName")

  • This function can be used to send both standard SCENARIO_ENTRY fields and PT_BUILDER fields.

  • It will perform either SCENARIO_ENTRY or PT_BUILDER upload based on the value input for calcPriceTargetFlag.

  • calcPriceTargetFlag is a boolean variable, looking for either a TRUE or FALSE value.

  • Based on the setting of the flag, either the required SCENARIO_ENTRY fields will be required, or the PT_BUILDER fields will be required

  • Note: this function is the default used if multiple Scenario Upload functions are present in a workbook, and no preference is set.

• =SCENARIO_ENTRY("tickerName", “scenarioName”, priceTarget, probability, occurrenceDate, “rationale”, "fundName")

  • Sends scenario data from Excel to the user’s Alpha Theory environment.

  • This function uploads scenarios into Alpha Theory for a specific Alpha Theory ticker or Asset ID.

  • Required: Alpha Theory ticker or AssetID, ScenarioName, PriceTarget, Probability

  • Optional: OccurenceDate, Rationale

  • Each function uploads a single scenario for a single Asset (one formula needed per scenario, per asset).

  • Click insert function button ‘fx’ next to formula bar to link cells.

  • Link cells for all scenarios. Probabilities must sum to 100%.

• =SCENARIO_GET("tickerName", "scenarioName", "scenarioProperty", "fundName")

  • This function allows users to pull in their scenario research from Alpha Theory directly into Excel.

    • For example, users can pull in the Price Target or Probabilities for various scenarios for their assets.

  • Accepted Scenario Properties that can be used with this function can be accessed here.

• =SCENARIOS_DISPLAY("tickerName", "fundName")

  • This function displays the Scenario Names, Price Targets, and Probabilities that currently exist for the asset

  • Users can leverage this prior to using the SCENARIO_GET function to get the proper names for each individual scenario

• =PT_BUILDER("tickerName",  "scenarioName", probability, occurrenceDate, valuationMethod, estimate, multiple, estimateDate, sharesOutstanding, netDebt, "rationale", "fundName")

  • This function will send data from Excel into Alpha Theory using the fields for the Price Target Builder.

  • Each function uploads a single scenario for a single Asset (one formula need per scenario, per asset).

  • Required: Alpha Theory ticker or assetID, scenarioName, probability, method, estimate, multiple, estimateDate.

  • Optional: occurrenceDate, sharesOutstanding, netDebt, rationale.

  • Probabilities for all scenarios for a particular asset need to add up to 100%.

  • Pushes data from Excel to Alpha Theory upon user clicking the "Upload Data" button.

• =SET_PREFERRED_PT_TYPE(priceTargetPreference)

  • With this function, users can specify whether to use SCENARIO_ENTRY or PT_BUILDER as the default upload method for asset scenarios.

  • This allows users to leverage both SCENARIO_ENTRY and PT_BUILDER functions within the same workbook.

  • Based on the set preference in this function, Excel Connect will push the data from the SCENARIO_ENTRY to Alpha Theory OR from the PT_BUILDER functions.

• =STORED_SCENARIOS_DISPLAY("tickerName", "fundName")
  • This function will display the Scenario Names and Probabilities that are currently saved in memory for a specific asset.

Tag Functions

• =ASSET_TAG_SET("fundName", "tickerName", "tagGroup", "tagName")

  • This function is used to assign assets to a particular tag in a tag group.

  • If the tag group or tag doesn't exist in Alpha Theory, this function will create the tag group/tag and then assign it to the selected asset.

• =TAG_EXPOSURE_SET("fundName", "tagGroup", "tagName", "field", value)

  • This function provides the ability to set the various tag-level constraints available in Alpha Theory directly from Excel.

    • For example, users can set the max gross and net exposures for a tag, or the minimum and preferred return for assets assigned to a particular tag.

  • Accepted Tag Properties that can be used with this function can be accessed here.

• =ASSET_TAG_GET("fundName", "tickerName", "tagGroup")

  • This function is used to inform users of the assigned tag in a tag group for a particular asset.

  • If the asset does not have a tag for the select tag group, the function will return a message instructing users to use ASSET_TAG_SET to assign a tag to the asset.

• =TAG_EXPOSURE_GET("fundName", "tagGroup", "tagName", "field")

  • This function provides the ability to pull in various tag-level constraints from Alpha Theory directly into Excel.

    • For example, users can view the current max gross and net exposures for a tag, or the minimum and preferred return for a particular tag.

  • Accepted Tag Properties that can be used with this function can be accessed here.

• =SHOW_TAGS_TABLE()
  • An Array Function (press ENTER to access), to show all tags per fund, including the tag group, tag type and it’s designated fund if applicable.

Fund Functions

• =FUND_GET(“fundName”, “fundProperty”)

  • This function pulls in fund properties from Alpha Theory into Excel.

  • Examples: max long position size, fund size, fund owner, etc.

  • Accepted Fund Properties that can be used with this function can be accessed here.

• =FUND_SET(“fundName”, “fundProperty”, valueToSet)

  • This function allows users to set various fund properties in Alpha Theory directly from Excel.

  • Examples: max long position size, fund size, fund owner, etc.

  • Accepted Fund Properties that can be used with this function can be accessed here.

• =DISPLAY_FUNDS()
  
  • This function will display a dynamic vertical list of all funds saved in your Alpha Theory platform, press ENTER to access.