hf_Function data missing

Discussion in 'General Questions' started by Lloyd, Nov 9, 2017.

hf_Function data missing

  1. Lloyd

    Lloyd Member

    Joined:
    Nov 6, 2017
    Messages:
    37
    Likes Received:
    4
    As a new Pro user, one of my drivers for MarketXLS is the hf_functions.
    The hf_function calls provide an opportunity to reduce my overhead in dealing with financial updates.

    However, there appear to be some issues with the hf_functions..
    The financial data for a large number of US stocks are returning errors on the hf_function call.
    I would appreciate a fairly quick response
    as I must decide on keeping MarketXLS Pro or downgrading to only the 'real time' functions.
    And there is always the 'is it me or the program?" issue with new users of programs.

    The attached spreadsheet has the tests that I ran against some US traded stocks.
    The primary issue is the returned errors, i.e. blank cells based on an IFERROR response.
    The financial data is blank over the entire 20 year period called for some 200 of the 350 stocks.
    Standard data requested, e.g. EPS, Shares Basic, Shares Diluted, PE ratio, EV, FCF and Book.
    I have also provided tabs of independent quarterly financial data for three stocks for the hf_function data called.
    GWW where MarketXLS returned reasonably accurate data.
    NDSN and XOM where limited to no data (errors) were returned.

    A secondary issue is the speed. I hope this will improve.
    The spreadsheet is not the most efficient, i.e. it took 5 hours to populate seven hf_function calls for each of some 350 stocks over a 20 year quarterly period (some 200,000 hf_function calls).
    The data feed was between 1K and 2K bps over the five hours for the update during the trading day. (Only MarketXLS running and MS admin background limited to < 100 bps.
    I generally download data at a 5+ MBps during the trading day and greater after hours.
    But as this type of spreadsheet would only be updated once per quarter and overnight after hours, I am not overly concerned about the how long it takes.

    Another 'bothersome' issue is that Excel needs to recalculate the entire spreadsheet, including function calls, when any edits are down to calculations in the spreadsheet, i.e. another 5 hours for an update.
    But this can be resolved by linking to the MarkXLS spreadsheet rather than doing non-MarketXLS calculations in the MarketXLS historic financial data spreadsheet.
    A key learning for this new user is make sure the Stop Auto Refresh box is ticked.

    Thank you in advance for clarifications and resolution.
     

    Attached Files:

  2. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,087
    Likes Received:
    10
    I am testing this one for you Lloyd
     
  3. Lloyd

    Lloyd Member

    Joined:
    Nov 6, 2017
    Messages:
    37
    Likes Received:
    4
    Thank you.
     
  4. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,087
    Likes Received:
    10
    Hi Lloyd,


    1) NDSN and XOM where limited to no data (errors) were returned. - I think a better way will be to use this button below...

    upload_2017-11-9_17-11-45.png

    To download all fundamental quarterly values at once, instead of doing it with functions. This can also help validate what values are missin.g

    2) Speed :

    I think you can safely turn this data into values. I mean the ones which are in MarketXLS Data sheet. As they will never change. Now, once these values are cached in a temp file in your PC, all these should not even be going to server for an update . Copy and paste all cells as values, as this is history and will never change.

    When we get the group mode working this should be better. But i think to get data in bulk I would think the button option should be best. If you have like 20 symbols, it could mean 20 clicks to get historical quarterly fundamental for all of them (one per sheet). You can turn the data placement setting to new sheet in the settings form.

    3) Yea, one option is to turn the calculations to manual mode, so you can do refresh selected to refresh only the ones you need. Another for all this historical data to be turned into values. I think with version 8.0.0.7 all these values will turn into a bin file in c:\programdata\marketxlscachedump.bin file. So, it should be snappy the next time you load it.

    Kind Regards
    Ankur
     
  5. Lloyd

    Lloyd Member

    Joined:
    Nov 6, 2017
    Messages:
    37
    Likes Received:
    4
     
  6. Lloyd

    Lloyd Member

    Joined:
    Nov 6, 2017
    Messages:
    37
    Likes Received:
    4
    Thank you, Ankur.
    Your suggestions have been helpful.

    1. Only one stock symbol is allowed per click of the 'All Fundamental Data Quarterly' button
    333 clicks and sheets of data does not reduce my overhead in managing analysis of the fundamental data.
    And the sheet created for the stock financial data does not have the tab labeled with the stock symbol.
    Neither is the stock symbol on the data sheet.

    And the button download data is not complete.
    Well, let me quality that.
    A. The button download does not provide all the data that the hf_function call does.
    Example: for XOM, the button download data starts 2001 Q2. The hf_function call starts 1997 Q4.
    B Only about 77 of the 104 financial data expected are imported with the button (based on the 104 hf_functions on the marketxlsfunctions.xls listing).
    [This is common for all stocks]
    I would counsel MarketXLS to review the hf-function call listing.

    A particular problem this created in my spreadsheet was
    hf_Weighted_Average_Shares
    hf_Weighted_Average_Shares_Diluted
    apparently do not exist.
    These data are missing from the one button download.
    As such, a call to the hf_functions produced errors. And I suspect query delays on the server.

    2. I am running 8.0.0.7.

    Issue Resolved
    Once the two hf_functions above were removed, the spreadsheet is importing all the stocks 20 yr quarterly historic financial data.
    Download speed seems to have increased by a factor of 10.
    I created a proxy for the two hf_Share functions needed by dividing EPS diluted with EPS basic function calls.
    Essentially the basic-to-diluted share count ratio.
    There is a share ratio, hf function, but documentation is missing as to what that means.
     
  7. Lloyd

    Lloyd Member

    Joined:
    Nov 6, 2017
    Messages:
    37
    Likes Received:
    4
    Ankur, on further inspection, the hf_functions are delivering spotty results.
    I suspect there is something wrong with the hf_function code..
    Please advise on how to get refund for MarketXLS Pro.
    I will then get MarketLXS Basic.

    Some stocks work well, some stocks not at all, some stocks skip quarters and years.
    I tried removing "TTM" to replicate single button "All fundamental Data Quarters".
    No change is the performance.
    I reduced the number of calls to single stocks. Same performance.

    Attached is the spreadsheet I have for my documentation.
    You may want to investigate the hf_function code.
    When the these hf_functions work as advertised, I will reconsider a Pro membership.
     
  8. Lloyd

    Lloyd Member

    Joined:
    Nov 6, 2017
    Messages:
    37
    Likes Received:
    4
    Whoops. attached file
     

    Attached Files:

  9. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,087
    Likes Received:
    10
    I see the issue. I am working on it LLoyd.

    It will be fixed in coming release in 5-10 days. I see the issue.

    Kind regards
    Ankur
     
  10. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,087
    Likes Received:
    10
    ok, this should now be okay.

    Thanks
     

Share This Page