Historical Fundamental functions not working

Discussion in 'Troubleshooting' started by Anjohn, Apr 16, 2018.

Historical Fundamental functions not working

  1. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hello,

    I just purchased the pro version and tried some of the functions in excel and a few of these are not working. Am I doing something wrong? The functions are:

    =hf_Filing_Date_to_Report_Period_Mapping("AAPL", 2010, 1) comes back with #VALUE!

    =hf_EPS_Growth("AAPL", 2010, 1) comes back with #VALUE!

    Some of the other functions I have tried do work like

    =hf_Earnings_per_Basic_Share("AAPL", 2010,1)

    So I know the connection is ok.

    Please could you let me know what I am doing wrong?
     
  2. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    I am checking on it.

    Thanks
    Ankur
     
  3. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi Ankur,

    How is this going?

    I would like to know as I specifically bought MarketXLS as you told me it had this functionality. I have only had this package for a day and it is not working.
     
  4. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    Ok, I checked this with our data vendor just now.

    Looks like these 2 are being deprecated from our data source/vendor since they can be calculated from other functions. Below is the complete list of what is available and what the data sources deprecated.

    =hf_Filing_Date_to_Report_Period_Mapping("AAPL", 2010, 1) comes back with #VALUE!
    =hf_EPS_Growth("AAPL", 2010, 1) comes back with #VALUE!

    But since, these are calculated, I will work on these to calculate many of these ourselves on the server and deliver them. Meanwhile something like this could be an option....

    =(hf_Earnings_per_Basic_Share_USD("MSFT",2010,2)-hf_Earnings_per_Basic_Share_USD("MSFT",2010,1))/hf_Earnings_per_Basic_Share_USD("MSFT",2010,1)

    You should be able to get EPS Quarter by Quarter growth using the function like shown above.

    ----------------------------------
    These seem to be the ones they deprecated recently.

    DILUTIONRATIO Share Dilution Ratio
    EPSDILGROWTH1YR EPS Diluted Growth
    EPSGROWTH1YR EPS Growth
    FILINGTYPE Filing Date to Report Type Mapping
    INTERESTBURDEN Financial Leverage
    LEVERAGERATIO Leverage Ratio
    NCFOGROWTH1YR NCFO Growth
    NETINCGROWTH1YR Net Income Growth
    REVENUEGROWTH1YR Revenue Growth
    SHARESWAGROWTH1YR Weighted Average Share Growth
    TAXEFFICIENCY Tax Efficiency ART,MRT

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

    Below is the complete list of the fields that should be available.................

    Income Statement
    INDICATOR CODE NAME

    REVENUE Revenues
    REVENUEUSD Revenues (USD)
    COR Cost of Revenue
    GP Gross Profit
    RND Research and Development Expense
    SGNA Selling, General and Administrative Expense
    OPEX Operating Expenses
    OPINC Operating Income
    EBIT Earning Before Interest & Taxes (EBIT)
    EBITUSD Earning Before Interest & Taxes (USD)
    INTEXP Interest Expense
    TAXEXP Income Tax Expense
    CONSOLINC Consolidated Income
    NETINCNCI Net Income to Non-Controlling Interests
    NETINC Net Income
    PREFDIVIS Preferred Dividends Income Statement Impact
    NETINCCMN Net Income Common Stock
    NETINCCMNUSD Net Income Common Stock (USD)
    NETINCDIS Net Income from Discontinued Operations
    EPS Earnings per Basic Share
    EPSUSD Earnings per Basic Share (USD)
    EPSDIL Earnings per Diluted Share
    SHARESWA Weighted Average Shares
    SHARESWADIL Weighted Average Shares Diluted
    DPS Dividends per Basic Common Share
    Cash Flow Statement
    INDICATOR CODE NAME
    NCFO Net Cash Flow from Operations
    DEPAMOR Depreciation, Amortization & Accretion
    SBCOMP Share Based Compensation
    NCFI Net Cash Flow from Investing
    CAPEX Capital Expenditure
    NCFBUS Net Cash Flow - Business Acquisitions and Disposals
    NCFINV Net Cash Flow - Investment Acquisitions and Disposals
    NCFF Net Cash Flow from Financing
    NCFDEBT Issuance (Repayment) of Debt Securities
    NCFCOMMON Issuance (Purchase) of Equity Shares
    NCFDIV Payment of Dividends & Other Cash Distributions
    NCFX Effect of Exchange Rate Changes on Cash
    NCF Net Cash Flow / Change in Cash & Cash Equivalents
    Balance Sheet
    INDICATOR CODE NAME
    ASSETS Total Assets
    ASSETSC Current Assets
    ASSETSNC Assets Non-Current
    CASHNEQ Cash and Equivalents
    CASHNEQUSD Cash and Equivalents (USD)
    RECEIVABLES Trade and Non-Trade Receivables
    INTANGIBLES Goodwill and Intangible Assets
    INVENTORY Inventory
    LIABILITIES Total Liabilities
    LIABILITIESC Current Liabilities
    LIABILITIESNC Liabilities Non-Current
    DEBT Total Debt
    DEBTUSD Total Debt (USD)
    DEBTC Debt Current
    DEBTNC Debt Non-Current
    DEFERREDREV Deferred Revenue
    DEPOSITS Deposit Liabilities
    INVESTMENTS Investments
    INVESTMENTSC Investments Current
    INVESTMENTSNC Investments Non-Current
    PAYABLES Trade and Non-Trade Payables
    PPNENET Property, Plant & Equipment Net
    TAXASSETS Tax Assets
    TAXLIABILITIES Tax Liabilities
    EQUITY Shareholders Equity
    EQUITYUSD Shareholders Equity (USD)
    RETEARN Accumulated Retained Earnings (Deficit)
    ACCOCI Accumulated Other Comprehensive Income
    Metrics & Ratios
    INDICATOR CODE NAME AVAILABLE DIMENSIONS
    ASSETTURNOVER Asset Turnover ART,MRT
    ASSETSAVG Average Assets ART,MRT
    BVPS Book Value per Share ARQ,MRQ,ARY,MRY
    CURRENTRATIO Current Ratio ARQ,MRQ,ARY,MRY
    DE Debt to Equity Ratio ARQ,MRQ,ARY,MRY
    DIVYIELD Dividend Yield
    EBITDA Earnings Before Interest, Taxes & Depreciation Amortization ARY,ARQ,ART,MRY,MRQ,MRT
    EBITDAUSD Earnings Before Interest, Taxes & Depreciation Amortization (USD) ARY,ARQ,ART,MRY,MRQ,MRT
    EBITDAMARGIN EBITDA Margin ART,MRT
    EBT Earnings before Tax ART,MRT
    EQUITYAVG Average Equity ART,MRT
    EV Enterprise Value
    EVEBIT Enterprise Value over EBIT ART,MRT
    EVEBITDA Enterprise Value over EBITDA ART,MRT
    FCF Free Cash Flow ARY,ARQ,ART,MRY,MRQ,MRT
    FCFPS Free Cash Flow per Share ARY,ARQ,ART,MRY,MRQ,MRT
    FXUSD Foreign Currency to USD Exchange Rate
    GROSSMARGIN Gross Margin ART,MRT
    INVCAP Invested Capital ARQ,MRQ,ARY,MRY
    INVCAPAVG Invested Capital Average ART,MRT
    MARKETCAP Market Capitalization
    NETMARGIN Profit Margin ART,MRT
    PE Price Earnings Damodaran Method ART,MRT
    PE1 Price to Earnings Ratio ART,MRT
    PS1 Price to Sales Ratio ART,MRT
    PS Price Sales Damodaran Method ART,MRT
    PB Price to Book Value ARQ,MRQ,ARY,MRY
    ROIC Retun on Invested Capital ART,MRT
    SPS Sales per Share ART,MRT
    PAYOUTRATIO Payout Ratio ART,MRT
    ROA Return on Average Assets ART,MRT
    ROE Return on Average Equity ART,MRT
    ROS Return on Sales ART,MRT
    TANGIBLES Tangible Asset Value ARQ,MRQ,ARY,MRY
    TBVPS Tangible Asset Book Value per Share ARQ,MRQ,ARY,MRY
    WORKINGCAPITAL Working Capital ARQ,MRQ,ARY,MRY
    Other
    INDICATOR CODE NAME
    PRICE Share Price (Adjusted Close)
    SHAREFACTOR Share Factor
    SHARESBAS Shares (Basic)
     
  5. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    The filing date of when the results were released to the public is important. When will the function/data be available?
     
  6. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi please can you tell me when the filing date will be available?
     
  7. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    Sorry for the delay. I am checking this for you. Filing date is available in our database, I will create a custom function for you and come back with a resolution. We have 4 date fields available in the database.

    Kind regards
    Ankur
     
  8. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Thanks Ankur,

    When will this custom function be available?

    Kind Regards

    Anjohn
     
  9. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi Ankur, Sorry to ask this again but please could you let me know when you intend to create this custom function for filing date?
     
  10. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    I am working on it today. Will report back later today. I think I will get a solution latest by monday.

    Kind regards
    Ankur
     
  11. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi Ankur, How is this going? Is the function working now and available to use?
     
  12. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    This fix is now ready in a new version. I will pm you shortly you should be able to use this today.

    Kind regards
    Ankur
     
  13. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    Ok, So I have now added 4 new functions in this version.

    These functions are all date functions....
    =hf_report_period
    =hf_last_updated
    =hf_date_key
    =hf_calendarDate

    You can uninstall your existing version by going to control panel and then install the latest version from the link here...

    Kind regards
    Ankur for MarketXLS
     
  14. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    And here is the description of these fields...

    Indicator: CALENDARDATE
    Title: Calendar Date
    Available Dimensions:
    Statement: Entity
    Description: Calendar Date is a column field available in the new datatable API which represents the normalized [REPORTPERIOD]. For example, if the report period is "2015-09-26", the calendar date will be "2015-09-30" for quarterly and trailing-twelve-month dimensions (ARQ,MRQ,ART,MRT), and "2015-12-31" for annual dimensions (ARY,MRY). This is useful when collating data across multiple companies that may have different fiscal periods.
    NA Value:
    Units:

    Indicator: DATEKEY
    Title: Date Key
    Available Dimensions:
    Statement: Entity
    Description: Date Key is a column field available in the new datatable API which represents the SEC filing date for AR dimensions (ARQ,ART,ARY), and the [REPORTPERIOD] for MR dimensions (MRQ,MRT,MRY). In addition, this is the observation date used for [PRICE] based data such as [MARKETCAP], [PRICE] and [PE].
    NA Value:
    Units:

    Indicator: REPORTPERIOD
    Title: Report Period
    Available Dimensions:
    Statement: Entity
    Description: Report Period is a column field in the new datatable API which represents the end date of the fiscal period. It is equivalent to value in the [FILINGDATE] datasets available under the old API.
    NA Value:
    Units:

    Indicator: LASTUPDATED
    Title: Last Updated Date
    Available Dimensions:
    Statement: Entity
    Description: Last Updated is a column field available in the new datatable API which represents the last date that this database entry was updated, which is useful to users when updating their local records.
    NA Value:
    Units:

    Kind regards
    Ankur
     
  15. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi Cannot find the PM with the latest version. Please can you send me a link with the latest version. Also please send me emails when new versions get released.
     
  16. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    Yes, you will get the email as soon as the new version 9 is out. I have sent a message now

    Thanks
     
  17. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi Ankur,

    I have been doing some tests and the "hf_date_key" formula does not seem to be working correctly. An example:

    I would like to obtain when the Q4 2015 results of Ticker "A" were released to the public;
    I am assuming I should use the function "hf_date_key" to give me the actual release date with "ART" or "ARQ" so use a the formula like this:
    =hf_Date_Key("A",2015,4,"ART") This returns the date 31-01-2016. This day was a Sunday which cannot be right. When I test this back further for other years of the same quarter I always get 31-01 of the following year. This looks like it is giving me the report period not the datekey. When I use the formula "hf_report_period" I get exactly the same dates which confirms that the "hf_date_key" formula is not working. I would expect the "hf_date_key" formula to return a date which is between 30 to 60 days after the report period date.

    Please can you look at this for me and confirm if I am doing something wrong or if the formula is not working and when the formula will be corrected.

    Thanks
     
  18. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Anjohn,

    The ART, ARQ etc should not be an argument. For the dates, we should not put any last argument. I need to check this for you. Please can you try the same without the last ART argument? I need to install the custom version which I created for this and then test it out. I will check asap.

    Let me know what you find without the ART and ARQ argument.

    Thanks
    Ankur
     
  19. Anjohn

    Anjohn New Member

    Joined:
    Mar 27, 2018
    Messages:
    11
    Likes Received:
    0
    Hi Ankur,

    I have tried the "hf_Date_Key" formula without "ART" or "ARQ" e.g. like this =hf_Date_Key("A",2016,4) and get exactly the same answer. Those arguments do not make any difference.

    Best Regards

    Anjohn
     
  20. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Eric,

    Thanks a lot for your constructive feedback. Point noted, many others have raised the same and we are absolutely not adding anything new but focusing on new and better data and fixes on this issue. I will check the server today and report back on this specific issue today.

    Thanks
    Ankur
     

Share This Page