Python for SMF?

Could I utilize Python for SMF?

I have done some work with calling python modules from within Excel.  My issue has been that I have been unable to write to the currently open workbook.

For myself, I use python to get market data and data from IB (Interactive Brokers) and I write some of that data to Excel files.  It is quite easy to for python to read, create and modify data in Excel files.

There are add-ins to do this like PyXLL or XLWings, but they are somewhat commercial products.  I say somewhat since PyXLL has a 30 day demo and XLWings has an open source version that is free if somewhat feature limited.  In the spirit of Randy's SMF Add-In, I am working on a open source and free way of implementing python solutions for Stock Market Functions.

Some ideas that I am testing are creating some python utilities to do some of the work of the SMF Add-In that could work alongside of it.  Use it to get slow or hard to get data, write it to CSV or XLSX files that can be linked to a Stock Dashboard or other Workbook that can utilize that data.

Website broken links #23

 The SMF group (https://groups.google.com/g/smf-addin) helped spot the numerous broken link and missing pages in the new site (https://climbermel.github.io/SMF_Add-in/).

I have fixed a lot of the broken links and I have created pages for the missing information in a number of areas.  There are still some pages that are more or less just placeholders as that was what was in the original site.  But these will at least now provide a page to show that instead of a 404 error for a missing page.

I'm still going through the code and would like to clean out the non usable code such as MSN, OX and Google where the data is no longer available.

So far I have made 155 updates to the code and web pages on GitHub (https://github.com/ClimberMel/SMF_Add-in).

Thanks to the members that have donated in various ways to help keep this add-in working.

Mel

Note on RCHGetYahooQuotes

Function  RCHGetYahooQuotes in module modGetYahooQuotes

This function is considered to be Obsolete 

Calling the function RCHGetYahooQuotes() in a spreadsheet will return no data

The function will return data if a full URL is provided and pItems are blank. 

Note that the function is being kept so that some calling modules such as RCHGetYahooHistory that do provide a URL in pTickers and pItems = "" will continue as they are.

The function could be used to test returning data based on a URL

For example:

=RCHGetYahooQuotes("https://query1.finance.yahoo.com/v7/finance/download/msft?period1=1262304000&period2=1735689600&interval=1d&events=history&includeAdjustedClose=true", "")

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...