New update soon to arrive!

I have a new version just about ready to release.  It was delayed today as during testing I found an interesting quirk with the sort order of the data.

Since the data is now being retrieved from Yahoo oldest to newest, the original resort function has a problem.

For example if you request years worth of data, but only provide say 100 rows in your array entered formula... it was giving us the 100 rows of data from the start date, so even resorting it only resorted the 100 rows.

So I have added a function to sort the data into newest to oldest and then process it to return.  So now if you select resort, it will resort the newest data that fits in your array.

 

Hope that made sense...

 

So the good news here are some of the fixes in the new version:

 

Issue# 11: The order should now be like it was

 

Issue# 10: I have fixed other issues like Paul's item he reported about doing math such as average on the returned data.  (It was being returned as a string)

 

Issue# 16: Fixed error if blank date sent to RCHGetYahooHistory.  Excel was defaulting it to 0 not NULL.

I plan to have the add-in put together and posted tomorrow.

Thanks,

Mel Pryor

Tip! For Excel 365 you need to change trust settings

For the SMF Add-In to work in newer versions of Excel such as Office 365 you will need to change the default Trust Settings to allow macros to run.

Change macro settings in the Trust Center

Macro settings are located in the Trust Center. However, if your device is managed by your work or school the system administrator might prevent anyone from changing settings.

Important: When you change your macro settings in the Trust Center, they are changed only for the Microsoft 365 program that you are currently using. The macro settings are not changed for all your Microsoft 365 programs.

1. Click the File tab.

2. Click Options.

3. Click Trust Center, and then click Trust Center Settings.

4. In the Trust Center, click Macro Settings.
   
    

   Make the selections that you want (Enable all macros), then click OK.

   Here is the full article:

   https://support.microsoft.com/en-us/office/enable-or-disable-macros-in-microsoft-365-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6

    
   

Note! Samples and Templates are being updated

 I have created an updated version of spreadsheet for the RCHGetElementNumber() function elements.

It is RCHGetElementNumber-Element-Definitions.xlsx 

I will add it to the website.

I have also created new versions of the Template files as samples of which parts are working.

For now they are on my GitHub site and can be downloaded from: https://github.com/ClimberMel/SMF_Add-in/tree/main/samples

I will try to get the Templates page updated with link to the files there.

Tip! Sort order for returned data

For RCHGetYahooHistory()

The parameters are as follows:

=RCHGetYahooHistory( Ticker, [Start Year], [Start Month], [Start Day], [End Year], [End Month], [End Day], [Period], [Data Items], [Header Line], [Adjust], [Resort] )

If you have this on your spreadsheet

You could use this array entered formula to get that values from the table

=RCHGetYahooHistory(C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13)

If the start and end dates are left black, the program uses default values and those may not be what you want.  Currently the Start Date is 1970-1-1, but there is data for some companies older than that.  I believe 1962 is the earliest data Yahoo has.  2020-12-31 is the end date for the last version of the Add-In, but that will be updated in the next release to 2024.

Alert! Yahoo Finance change has caused multiple issues to multiple platforms.

 On Dec. 16, 2022 yahoo finance made changes to its web pages.  This has caused a number of issues to the SMF Add-in, but it has also caused errors with yFinance python module.

The web site for the SMF Add-In

    https://climbermel.github.io/SMF_Add-in/

The GitHub for code changes for SMF Add-In

    https://github.com/ClimberMel/SMF_Add-in

The GitHub for yFinance

    https://github.com/ranaroussi/yfinance     

 The PyPi install for yFinance

    https://pypi.org/project/yfinance/

Alert -- Updates to Zacks Element Definitions

 

Zacks recently changed the web page name where the earnings estimates were located, so elements on smf-elements-8.txt needed to be updated.

New files on the website: 

• smf-elements-8.txt

The best practice is to exit EXCEL when updating SMF add-in files.

Tip: Data retrieval with Python

 

If you're not familiar with Python, you should learn. It's a powerful programming language that can extract data from websites. And there are many code libraries for doing that retrieval. 

The Google Colaboratory website allows you to easily run Python code from the browser:

https://colab.research.google.com/notebooks/intro.ipynb#recent=true

A good introduction to retrieving Yahoo data is here:

https://medium.com/@realmistic/use-your-computer-to-make-informed-decisions-in-stock-trading-practical-introduction-part-2-832e6f67f590

For example, a program as simple as:

!pip install yfinance

import yfinance as yf

pfe = yf.Ticker('PFE')

pfe.info

...will allow access to all of this data:

{'52WeekChange': 0.025267482,

 'SandP52WeekChange': 0.16119564,

 'address1': '235 East 42nd Street',

 'algorithm': None,

 'annualHoldingsTurnover': None,

 'annualReportExpenseRatio': None,

 'ask': 36.55,

 'askSize': 4000,

 'averageDailyVolume10Day': 82095557,

 'averageVolume': 31018400,

 'averageVolume10days': 82095557,

 'beta': 0.641488,

 'beta3Year': None,

 'bid': 36.52,

 'bidSize': 1800,

 'bookValue': 11.744,

 'category': None,

 'circulatingSupply': None,

 'city': 'New York',

 'companyOfficers': [],

 'country': 'United States',

 'currency': 'USD',

 'dateShortInterest': 1604016000,

 'dayHigh': 37.42,

 'dayLow': 36.3,

 'dividendRate': 1.52,

 'dividendYield': 0.0422,

 'earningsQuarterlyGrowth': -0.714,

 'enterpriseToEbitda': 13.11,

 'enterpriseToRevenue': 5.206,

 'enterpriseValue': 253279617024,

 'exDividendDate': 1604534400,

 'exchange': 'NYQ',

 'exchangeTimezoneName': 'America/New_York',

 'exchangeTimezoneShortName': 'EST',

 'expireDate': None,

 'fiftyDayAverage': 35.149593,

 'fiftyTwoWeekHigh': 39.83871,

 'fiftyTwoWeekLow': 26.451612,

 'fiveYearAverageReturn': None,

 'fiveYearAvgDividendYield': 3.62,

 'floatShares': 5548891741,

 'forwardEps': 2.84,

 'forwardPE': 12.788733,

 'fromCurrency': None,

 'fullTimeEmployees': 88300,

 'fundFamily': None,

 'fundInceptionDate': None,

 'gmtOffSetMilliseconds': '-18000000',

 'heldPercentInsiders': 0.00046999997,

 'heldPercentInstitutions': 0.70032,

 'industry': 'Drug Manufacturers—General',

 'isEsgPopulated': False,

 'lastCapGain': None,

 'lastDividendDate': 1604534400,

 'lastDividendValue': 0.36053,

 'lastFiscalYearEnd': 1577750400,

 'lastMarket': None,

 'lastSplitDate': 1605571200,

 'lastSplitFactor': '1054:1000',

 'legalType': None,

 'logo_url': 'https://logo.clearbit.com/pfizer.com',

 'longBusinessSummary': 'Pfizer Inc. develops, manufactures, and sells healthcare products worldwide. It offers medicines and vaccines in various therapeutic areas, including cardiovascular metabolic and pain under the Eliquis, Chantix/Champix, and Premarin family brands; biologics, small molecules, immunotherapies, and biosimilars under the Ibrance, Sutent, Xtandi, Xalkori, Inlyta, Braftovi + Mektovi brands; and sterile injectable and anti-infective medicines under the Sulperazon, Medrol, Vfend, and Zithromax brands. The company also provides medicines and vaccines in various therapeutic areas, such as pneumococcal disease, meningococcal disease, and tick-borne encephalitis under the Prevnar 13/Prevenar 13 (pediatric/adult), FSME-IMMUN, Nimenrix, and Trumenba brands; biosimilars for chronic immune and inflammatory diseases under the Xeljanz, Enbrel, Inflectra, and Eucrisa brands; and amyloidosis, hemophilia, and endocrine diseases under the Vyndaqel/Vyndamax, BeneFIX, Genotropin, and Refacto AF/Xyntha brands. In addition, the company is involved in the contract manufacturing business. It serves wholesalers, retailers, hospitals, clinics, government agencies, pharmacies, and individual provider offices, as well as disease control and prevention centers. The company has collaboration and/or co-promotion agreements with Bristol-Myers Squibb Company and Astellas Pharma US, Inc.; a licensing agreement with Akcea Therapeutics, Inc; a strategic alliance with Verily Life Sciences LLC; collaboration agreements with Merck KGaA and Valneva SE; a clinical trial collaboration and supply agreement with IDEAYA Biosciences, Inc.; a material transfer and collaboration agreement with BioNTech SE; a clinical supply collaboration with Jiangsu Alphamab Biopharmaceuticals Co., Ltd; a research collaboration and license agreement with BioInvent International AB; and a multi-target drug discovery collaboration with Sosei Group Corporation. Pfizer Inc. was founded in 1849 and is headquartered in New York, New York.',

 'longName': 'Pfizer Inc.',

 'market': 'us_market',

 'marketCap': 201881092096,

 'maxAge': 1,

 'maxSupply': None,

 'messageBoardId': 'finmb_162270',

 'morningStarOverallRating': None,

 'morningStarRiskRating': None,

 'mostRecentQuarter': 1601164800,

 'navPrice': None,

 'netIncomeToCommon': 8686000128,

 'nextFiscalYearEnd': 1640908800,

 'open': 36.93,

 'openInterest': None,

 'payoutRatio': 0.96769994,

 'pegRatio': -12.73,

 'phone': '212 733 2323',

 'previousClose': 36.04,

 'priceHint': 2,

 'priceToBook': 3.092643,

 'priceToSalesTrailing12Months': 4.1497483,

 'profitMargins': 0.17854,

 'quoteType': 'EQUITY',

 'regularMarketDayHigh': 37.42,

 'regularMarketDayLow': 36.3,

 'regularMarketOpen': 36.93,

 'regularMarketPreviousClose': 36.04,

 'regularMarketPrice': 36.93,

 'regularMarketVolume': 62576363,

 'revenueQuarterlyGrowth': None,

 'sector': 'Healthcare',

 'sharesOutstanding': 5558400000,

 'sharesPercentSharesOut': 0.0083,

 'sharesShort': 45925049,

 'sharesShortPreviousMonthDate': 1601424000,

 'sharesShortPriorMonth': 45163995,

 'shortName': 'Pfizer, Inc.',

 'shortPercentOfFloat': 0.0083,

 'shortRatio': 1.93,

 'startDate': None,

 'state': 'NY',

 'strikePrice': None,

 'symbol': 'PFE',

 'threeYearAverageReturn': None,

 'toCurrency': None,

 'totalAssets': None,

 'tradeable': False,

 'trailingAnnualDividendRate': 1.5,

 'trailingAnnualDividendYield': 0.041620422,

 'trailingEps': 1.538,

 'trailingPE': 23.615084,

 'twoHundredDayAverage': 34.76005,

 'volume': 62576363,

 'volume24Hr': None,

 'volumeAllCurrencies': None,

 'website': 'http://www.pfizer.com',

 'yield': None,

 'ytdReturn': None,

 'zip': '10017'}