New ZIP file -- Version 3.0.2023.02.24

 New release of SMF Add-In

The recent release is available on https://climbermel.github.io/SMF_Add-in/ 

I have changed the Home page a bit to modernize it somewhat.  I have also added two versions of the Add-In now.  This will be in case the newest one causes issues, it will be easier to get the last stable version in case you didn't save it.

 

 Modules updated:

Update smfGetYahooHistory.xlsx

    smfGetYahooHistory close / adjclose using wrong pItem codes #19

Update smfUtilities_.bas

    Duplicate Excel 2010 Function #18

Update modGetYahooHistory.bas

    Fix RCHGetYahooHistory function to match Item codes with smfGetYahooHistory see Issue #19

     RCHGetYahooHistory missing funtionality #13

Update smfGetYahooHistory.xlsx

    Added sample spreadsheet to Templates

Update modGetElementNumber.bas

    Update version

Other Issues fixed with new version 3.0.2023.02.24

    smfGetYahooPortfolioView possible incorrect data returned #9

 

New Release in Progress

 I have added these issues to the next release:

    - smfGetYahooPortfolioView possible incorrect data returned #9

    -  smfGetYahooHistory close / adjclose using wrong pItem codes #19

    -  Duplicate Excel 2010 Function #18

 

There was no issue with smfGetYahooPortfolioView.  The user was also calling smfGetYahooHistory and that is where they were getting unadjusted intead of adjusted close

Issue# 18 is some unused code in the add-in that matched an Excel internal function and will be deleted.  It may have been originally written before Excel added the IFERROR() function.

The following modules will be updated:

RCHGetYahooHistory() & smfGetYahooHistory to change the Items back to C for adjusted close and U for unadjusted close.  A will now be allowed as well, but simply be converted to use C for adjusted.

smfUtilities_ to remove function IfError().

modGetElementNumber to update version number

Duplicate Excel 2010 Function #18

 Issue submitted to GitHub by Joey

There is a routine in the smfUtilities_ module name IfError. There is a built-in Excel 2010 function with the same name.

There are no calls to it from any module. I am going to guess it was a formula that Randy used for something. He had a few routines that were for his testing and also for older versions of Excel. You can't call it in Excel 2010. I changed the name slightly so that I could call it and it returned 0 for a division be zero error but didn't display the error message.

I see no benefit to keeping unused code, so I will look at adding it to the next release as a deletion from smfUtilities_

smfGetYahooHistory close / adjclose using wrong pItem codes #19

 Interesting mix up with the items list.
Marek mentioned that the Adjusted/Unadjusted closes were mixed up. I was using A for adjusted and C for unadjusted close.

I went back through the original code (2017) and smfGetYahooHistory used
pItems As String = "tdohlcvufgxs"
"Ticker,Date,Open,High,Low,Close,Volume,Unadj,Div Adj,Split Adj,Dividend,Split"
So Randy used Close (C) for adjusted close and (U) for Unadjusted Close in the smfGetYahooHistory.

What threw me off was that I started with RCHGetYahooHistory which accepts A for adjusted Close and C for unadjusted close but did a swap before calling smfGetYahooHistory.

The old smfGetYahooHistory didn't have a "A" item. But RCHGetYahooHistory did...

So I now have to figure out the best way to deal with this as I have tried to keep the original formulas working... so I will see about switching smfGetYahooHistory back to using C for adjClose and U for Close (unadjusted)

I'm not planning on trying to do the individual split adjusted and div adjusted calculations since yahoo provides the adjusted close now, I see no reason to attempt to try split them out. Especially since yahoo doesn't provide the splits and divs with the rest of the data.

I'll let everyone know when the new version gets posted and I will test to make sue it doesn't mess up what is already working...

New ZIP file -- Version 3.0.2023.02.15

 This update has fixes to a number of issues.

From GitHub

 

Release RCH_Stock_Market_Functions-3.0.2023.02.15

Change Log

Module    |    Function

modGetElementNumber    Function RCHGetElementNumber

Update version number (kVersion) and update comments for changes.

RCH_Stock_Market_Functions-3.0.2023.02.15

smfGetYahooHistory_    smfGetYahooHistory_

Issue#10 Fix data being returned as string instead of decimal

Issue#11 Data order

Issue#17 Data getting truncated

Changed default start date from 1970 to 1950 if no date provided

modGetYahooHistory    Function RCHGetYahooHistory

Issue #16 If empty date elements sent, you get an error due to 0/0/0 being invalid date.

Fix data order to return as it did in the past to avoid confusion.

Milestone fix is:

RCH_Stock_Market_Functions-3.0.2023.02.15

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.