Tip -- 2.1b - Using the smfUpdateDownloadTable macro

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

Let me restate [Tip 2.1a] and give some visuals of creating a range to fill using the smfUpdateDownloadTable macro:

1. The upper left hand corner cell of the table needs to have a range name of "Ticker" (see green-shaded cell on diagram). The text in the cell can be anything you want it to be.
2. The cells below the "Ticker" cell should be filled in with Yahoo ticker symbols, one per cell (see yellow-shaded cells on diagram).
3. The cells to the right of the "Ticker" cell should be filled with column titles (see orange-shaded cells on diagram. The text in these cells can be anything you want them to be.
4. The cells above the column titles need to be filled in with SMF add-in formulas or element numbers (see blue-shaded cells on diagram). Use five tildas as a substitute for a ticker symbol. For example, any of the following text strings could be used to get "Market Capitalization" from Yahoo:
   
   941
   RCHGetElementNumber("~~~~~", 941)
   RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap")
   
   If you place an "X" in the cell of the element definition, it would tell the smfUpdateDownloadTable macro to skip it and leave that column alone. I do this when I want to create a column with my own formulas in, or to use as a divider column in the table.

The macro continues processing rows with ticker symbols (i.e. green-shaded cell) until it runs into an empty cell with no ticker symbol in it. It continues process across the row of element definitions (i.e. blue-shaded cells) until it runs into an empty cell with no element definition in it.

So, this is what it would look like after doing the four steps above:

	941	X	942
Heading0	Heading1	Heading2	Heading3
MMM
IBM
AA

After running the smfUpdateDownloadTable macro, it would look like this:

	941	X	942
Heading0	Heading1	Heading2	Heading3
MMM	58800000		61300000
IBM	1.73E+08		1.88E+08
AA	15230000		23800000

Tip -- 2.1a - Using the smfUpdateDownloadTable macro

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

I use the smfUpdateDownloadTable macro for such situations.  This would actually place values into the table, so no recalculation occurs.  When I want "fresh" values, I just rerun the macro.  Here is the documentation for the table setup from the macro's module:

1. The upper left hand corner cell of the table needs to have a range name of "Ticker"
2. The cells below the "Ticker" cell should be filled in with ticker symbols, one per cell
3. The cells to the right of the "Ticker" cell should be filled with column titles
4. The cells above the column titles need to be filled in with SMF add-in formulas or element numbers.  Use five tildas as a substitute for a ticker symbol.  For example, any of the following text strings could be used to get "Market Capitalization" from Yahoo:
   
   941
   RCHGetElementNumber("~~~~~", 941)
   RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap")

A recent addition to the process is that if the cell entry above a column title is an "x", that column is skipped from any update -- I use this for any calculation columns I want in the middle of my table.  In all of my workbooks, I assign the smfUpdateDownloadTable macro to a keyboard shortcut of Ctrl-Shift-J.

On 10/19/07, rjlabs <rick@...> wrote:
>
> I have a large table of =RCHGetElementNumber() that has historical
> quarterly info that I only need to update sporadically. It takes about
> 15 minutes to update (about 60 tickers x 150 fields).
>
> Is there a way I can control when those functions run and update? I
> want to be able to open the spreadsheet with perhaps stale data and
> not have it update. However I don't want to set calculation to manual
> because other parts of the workbook I want regularly calculating. (Any
> programmer guide or FAQ or embedded docs on this just point me to them.) 

Tip -- Using smfGetCSVFile() to grab Yahoo Historical quotes

It appears that Yahoo once again has an easy-to-grab CSV file of their historical quotes available directly via a URL. See the link below for an example, using the smfGetCSVFile() function. It does use the new EXCEL "spill" feature -- if you don't have that available, all you will see is "Date" for the function, as you would need to array-enter the function. The new "spill" feature automatically expands any array function to fill the range is has data for.

SMF-Sample-smfGetCSVFile-Yahoo-Historical-Quotes.xlsm

Alert -- RCHGetYahooQuotes() issue

When I wrote RCHGetYahooHistory() well over a decade ago, the default ending date was 2020-12-31 if no date information was passed.

You can either switch over and use the new smfGetYahooHistory(), or specify a different ending year (e.g. 2030) for RCHGetYahooHistory().

Alert -- Updates to Zacks Element Definitions

 Zacks recently made some changes to their quote web page, so some defined elements on smf-elements-6.txt needed to be updated.

New files on the web site: 

• smf-elements-6.txt
• Change-Log.html

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

Updated element definitions:

#	Usage	Source	Element	Template or Notes	Updated
13869	S	Zacks	Market Capitalization	SMF-Template-Zacks-Data.xls	2020-12-04
13871	S	Zacks	Current Year Estimate	SMF-Template-Zacks-Data.xls	2020-12-04
13872	S	Zacks	EPS Last Year	SMF-Template-Zacks-Data.xls	2020-12-04
13891	S	Zacks	Beta	SMF-Template-Zacks-Data.xls	2020-12-04
13892	S	Zacks	Forward P/E	SMF-Template-Zacks-Data.xls	2020-12-04
13896	S	Zacks	Sector	SMF-Template-Zacks-Data.xls	2020-12-04

 

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'}

 

Alert -- Issue with smfGetGuruFocusItem()

 Unfortunately, GuruFocus has changed the way HTML class labels are used on their web page, and those were the labels that the function uses as search terms. I made some simple changes that fixed many, but there were also many that they didn't make the same changes to, so then those broke. It seems like any fix I make to the current algorithm to fix one extraction breaks some other extraction.

It looks like they've also changed a few line item labels.

...Later...

I made a compromise and abandoned the old search tags, now keying off the TTM item. That was the way I originally did it, but there were issues with companies that didn't have enough quarterly items. But I'd rather have it work for everything else and not those than not work for anything.

It looks like the things I'm getting an outright "Error" on are a result of the line item simply not existing for the company.

Because I am a subscriber, I never use this function. The smfGetGuruFocusCSVItem() function is much faster at data retrieval and more comprehensive. 

Here is an interim XLA file until I decide what more I want to do, if anything.

Updated XLA file

You should exit EXCEL before updating the XLA file.