Simple Yahoo Price Scrape update

[kiloran]

DONE!!!!!!!!!!!! HUGE THANKS TO KILORAN FOR HIS HELP AND PATIENCE.

Problem was, I am a total novice, and extracting the files and working on them, instead of archiving and saving in the archive --- what ever that means I still have no idea but as soon as I started working inside 7 zip and archiving and when I got a prompt do you want to save the file in the archive I knew I was close. So just amending extracted files will not make the changes stick even though files look correct, needs updating and amending the archive.

Only took me 24 hours and now I will have a beer even though it is not Friday.

Thanks again, you're a star.

Well done, I think the beer is deserved.
It initially looks very daunting, but having succeeded, I'm sure it now looks very simple

--kiloran

[JMN2]

I wonder how the code would have to be changed in order to have it working in a worksheet called "portfolio" and down from the cell E6 to E51, the ticker in a C column and column D is in the middle unused...I'll just experiment and try to understand the code what determines what...I will revert next week.

[kiloran]

I wonder how the code would have to be changed in order to have it working in a worksheet called "portfolio" and down from the cell E6 to E51, the ticker in a C column and column D is in the middle unused...I'll just experiment and try to understand the code what determines what...I will revert next week.

Since you are not experienced with Python/LibreOffice, the following thoughts may help.

Indentation of python code is critical. You cannot mix tabs and spaces. The file SimpleYahooPriceScrape.py uses 4 spaces per indentation. A single instance of a tab or 5 spaces instead of 4 spaces will prevent the script from operating. This "feature" is guaranteed to bite you and cause you to resort to alcohol to calm your nerves as you try to find the problem.

Don't make too many changes to the code at once, and keep a backup of the last known working code.

Changing the code and having to copy it into the archive at each change is arduous. Here's what I do (on Windows 7, might be slightly different for Windows 10):

1. Locate the folder C:\Users\YourAccount\AppData\Roaming\LibreOffice\4\user\
   
2. Within this folder, create \Scripts\python\ and copy SimpleYahooPriceScrape.py to this folder so you finish up with C:\Users\YourAccount\AppData\Roaming\LibreOffice\4\user\Scripts\python\SimpleYahooPriceScrape.py
   
3. If you now open your .ods file within LibreOffice Calc, then TOOLS/MACROS/ORGANISE MACROS/PYTHON, you should see My Macros, LibreOffice Macros and YourFile.ods. Click on the + symbol next to My Macros and you should see SimpleYahooPriceScrape.py, which you can then run.
   
4. You can then open SimpleYahooPriceScrape.py using Notepad or your preferred text editor (I like NotePad++ https://notepad-plus-plus.org/ ), make changes, save the file (and leave it open) and then immediately run the macro to see the effect of your changes. You can keep Notepad and LibreOffice both open and just hop between the two
   
5. When you are finally happy with your changes, copy SimpleYahooPriceScrape.py into the archive.

I think there are smarter ways of developing Python code for LibreOffice, but whenever I try to be smart it bites my bum, so I've settled on this simple(?) methodology

--kiloran

[JMN2]

People using this or any other with the same code please not it works for currencies too (for instance GBPUSD=X).

[GN100]

Slightly different question - I hope it doesn't require an new topic. What symbols can I use to get the GBP/USD and GBP/EUR rates? Have tried GBPUSD=X but that doesn't work.

[0x3F]

Changing the code and having to copy it into the archive at each change is arduous.

If you use gVim on windows (or Linux) you can edit the the code directly inside the 'ods' archive if you include this in it's vimrc config file:

Code: Select all

   autocmd BufReadCmd *.ods,*.rar,*.jar call zip#Browse(expand("<amatch>"))


This tells it to treat .ods as a zip file. You can then open the ods file with gvim and navigate through the folder structure that is contained to edit the script directly, without the need to unzip/re-zip. Saving the script will save the file contained in the ods 'zip'.

Vim is a command line text editor isn't too user friendly at first, but very powerful and worthwhile learing. Gvim is a graphical version and gives menus, so don;t need to remember the commands.

-0x3F

[JMN2]

Slightly different question - I hope it doesn't require an new topic. What symbols can I use to get the GBP/USD and GBP/EUR rates? Have tried GBPUSD=X but that doesn't work.

It does work for me. In fact, any yahoo ticker I've tried so far has worked fine.

[kiloran]

Changing the code and having to copy it into the archive at each change is arduous.

If you use gVim on windows (or Linux) you can edit the the code directly inside the 'ods' archive if you include this in it's vimrc config file:

Code: Select all

   autocmd BufReadCmd *.ods,*.rar,*.jar call zip#Browse(expand("<amatch>"))


This tells it to treat .ods as a zip file. You can then open the ods file with gvim and navigate through the folder structure that is contained to edit the script directly, without the need to unzip/re-zip. Saving the script will save the file contained in the ods 'zip'.

Vim is a command line text editor isn't too user friendly at first, but very powerful and worthwhile learing. Gvim is a graphical version and gives menus, so don;t need to remember the commands.

-0x3F

Thanks ? (never did like hex ), that sounds interesting, I've made a note to have a look at this when I've got some other tasks out of the way.

The mention of Vim gave me the Heebie-Jeebies, but a graphical version might be OK

--kiloran

[GN100]

Slightly different question - I hope it doesn't require an new topic. What symbols can I use to get the GBP/USD and GBP/EUR rates? Have tried GBPUSD=X but that doesn't work.

It does work for me. In fact, any yahoo ticker I've tried so far has worked fine.

Strange, I have just re tried with GBPUSD=X and it just produces a blank cell.

[kiloran]

Slightly different question - I hope it doesn't require an new topic. What symbols can I use to get the GBP/USD and GBP/EUR rates? Have tried GBPUSD=X but that doesn't work.

It does work for me. In fact, any yahoo ticker I've tried so far has worked fine.

Strange, I have just re tried with GBPUSD=X and it just produces a blank cell.

I just tried Simple_Yahoo_Price_Scrape_1-3a.ods using LibreOffice 5.4.3.2 and Windows 7 and the following all worked OK:

GBPUSD=X
USDGBP=X
GBPEUR=X
EURGBP=X

--kiloran

[0x3F]

Thanks ? (never did like hex

ha! well figured out

I've got a slightly different implementation of a price fetch function. Thought I'd share it in case anyone is interested. I've only had a quick glance at the current implemenation, and mine differs in two respects:

- I declare the tickers required in an ordered dictionary. This also contains the sheet, so I can define prices across multiple sheets. I also use a multiplcation factor that allows me to get the approx Gold price from GLD.

Code: Select all

   myPortfolioSheet = oSheets.getByName("Portfolio")
   myPensionSheet = oSheets.getByName("Pension")

   # Creat Ordered Dictionary of shares we're fetching prices for
   SharesDict = collections.OrderedDict()
   #      Yahoo Ticker   = [SheetWritingTo, CellWritingTo, MultiplicationFactor]      
   SharesDict["RCP.L"]     = [myPortfolioSheet, "O17", NO_CONVERSION_MULT_BY_ONE]
   SharesDict["SLV"]      = [myPortfolioSheet, "O26", SLV_CONV_FACTOR]
   SharesDict["GLD"]       = [myPortfolioSheet, "O28", GLD_CONV_FACTOR]
   SharesDict["IBTS.L"]    = [myPortfolioSheet, "O34", NO_CONVERSION_MULT_BY_ONE]
   SharesDict["PSLV"]      = [myPensionSheet, "O24", NO_CONVERSION_MULT_BY_ONE]


- The yahoo response is now in JSON format, and so it makes sense to use python functions to process that. I first read the values into a list:

Code: Select all

   # Get the url needed to fetch prices
   yahooQuery = ConstructQueryUrlFromTickers(SharesDict)
   
   # Fetch the JSON data and load it into array for processing
   yahooJSONResponse  =  urlopen(yahooQuery)
   charset = yahooJSONResponse.headers.get_content_charset()
   yahooPriceInfo = json.loads(yahooJSONResponse.read().decode(charset))
   pricesList = yahooPriceInfo["quoteResponse"]["result"]   # Prices in same order as in URL


I can then process my (Ordered) dictionary, writing the values into the spreadsheet. The indexes in the dictiuonary, match those in the prices list - so quite straight forward to read the price.

Code: Select all

   # Parse our dictionary, for each share print the price
   for ticker, vals in SharesDict.items():

      # Get the Index of this share in the pricesList.  This works as we used OrderedDict
      index = list(SharesDict.keys()).index(ticker)
      quoteVal = pricesList[index]['regularMarketPrice']

      # Write value into approprite sheet
      WriteValueToSheet(SharesDict[ticker], quoteVal)


In my opinion this is clearer and more succinct than the regex that had to be used previously after scraping html. Should be more maintainable, in case I have to come back to it when they change things again My code probably needs to be made a bit more robust in places. Anyway, Full code here:

Code: Select all

#    Name:         iScrapePrices.py
#
#   Description:   Scrapes prices from Yahoo website.
#               Cells hard coded here into dictionary.
#
#   Version:      v1.0
#

import re
import sys
import math
import time
import collections
import json
from urllib.request import Request, urlopen

from com.sun.star.awt import Rectangle
from com.sun.star.awt import WindowDescriptor
from com.sun.star.awt.WindowClass import MODALTOP
from com.sun.star.awt.VclWindowPeerAttribute import OK, OK_CANCEL, YES_NO, YES_NO_CANCEL, RETRY_CANCEL, DEF_OK, DEF_CANCEL, DEF_RETRY, DEF_YES, DEF_NO
from com.sun.star.awt.FontWeight import BOLD, NORMAL

##################################### GLOBALS ###########################################################

# Indexes of PortShares dictionary
IDX_SHEET = 0
IDX_CELL_LOCATION = 1
IDX_CONV_FACTOR = 2

NO_CONVERSION_MULT_BY_ONE = 1
GLD_CONV_FACTOR = 1.0472
SLV_CONV_FACTOR = 1.052

###################################### FUNCTIONS #######################################################


# Message box test for OO or LO version
# Uses either messageBoxOO4 or messageBoxLO4. Pretty much pot luck which one works, depending
# on which version of OpenOffice or LibreOffice is used
def messageBox(msgText, msgTitle):
   doc = XSCRIPTCONTEXT.getDocument()
   parentwin = doc.CurrentController.Frame.ContainerWindow

   try:
      messageBoxLO4(parentwin, msgText, msgTitle)
   except:
      messageBoxOO4(parentwin, msgText, msgTitle)
   else:
      pass


### following works with OO4 but not LO4 or OO Portable 3.2
def messageBoxOO4(ParentWin, MsgText, MsgTitle):
   #doc = XSCRIPTCONTEXT.getDocument()
   ctx = XSCRIPTCONTEXT.getComponentContext()
   MsgText = MsgText+"      OO"
   
   toolkit = ctx.getServiceManager().createInstanceWithContext("com.sun.star.awt.Toolkit", ctx)
   msgbox = toolkit.createMessageBox(ParentWin, 0, 1, MsgTitle, MsgText)
   
   msgbox.execute()
   #msgbox.dispose()


# Show a message box with the UNO based toolkit. Works with LO4 and OOP3.2
# But.... does not work with LO 4.2.3.3
def messageBoxLO4(ParentWin, MsgText, MsgTitle):
   MsgText = MsgText+"      LO"
   #describe window properties.
   aDescriptor = WindowDescriptor()
   aDescriptor.Type = 1                  # MODALTOP
   aDescriptor.WindowServiceName = "infobox"
   aDescriptor.ParentIndex = -1
   aDescriptor.Parent = ParentWin
   aDescriptor.WindowAttributes = 4194304   #MsgButton = OK
   
   tk = ParentWin.getToolkit()
   msgbox = tk.createWindow(aDescriptor)
   
   msgbox.setMessageText(MsgText)
   msgbox.setCaptionText(MsgTitle)
      
   return msgbox.execute()



def WriteValueToSheet(StockInfo, quoteVal) :
   """
   Write the fetched value to the desired sheet. 
   Correct for diffs in magnitude as sometimes found returned GBP instead of GBX
   """
   
   sheet = StockInfo[IDX_SHEET]
   cell = StockInfo[IDX_CELL_LOCATION]

   # Get the current magnitude in the spreadsheet cell
   CurrentCellValue = sheet.getCellRangeByName(cell).Value
   ExpectedMagnitude = GetMagnitude(float(CurrentCellValue))

   # Get the fetched value and check it's magnitude is as expected (Correct if not)
   fQuoteVal = float(quoteVal)
   fQuoteVal = CheckPoundsPence(fQuoteVal, ExpectedMagnitude)

   # Write the value out to the spreasdhseet, applying conversion factor
   try:
      sheet.getCellRangeByName(cell).Value = float(StockInfo[IDX_CONV_FACTOR]) * float(fQuoteVal)
   except:
      sheet.getCellRangeByName(cell).Formula = '=NA()'
      
   return


def CheckPoundsPence(Price, ExpectedMagnitude):
   """
   Somethimes Yahoo returns price in GBP instead of GBX.  Correct for this.
   Shift price so as it reflects the expected magnitude.
   """

   # Determine magnitude of price ie 9.00=1, 99.0=2, 999.0=3
   PriceMag = GetMagnitude(Price)

   # Handles both Left and Right shifts.  Honest.
   if PriceMag != ExpectedMagnitude:
      Price *= 10**(ExpectedMagnitude - PriceMag)

   return Price


def GetMagnitude(Price):
   return int(math.log10(Price)+1)


def ConstructQueryUrlFromTickers(SharesDict):
   """
   Takes ORDERED dict with keys containing tickers of shares (yahoo format) and
   returns single yahoo query url which can be used to fetch the prices
   """

   # Start with  Base url
   yahooQuery = "http://query1.finance.yahoo.com/v7/finance/quote?symbols="
   
   # Add in tickers for shares in supplied dictionary
   for share, vals in SharesDict.items():
      yahooQuery=yahooQuery + share + ","

   # remove final ','
   return yahooQuery[:-1]




#================================================================================
#================================================================================   

# Define with *args as then it can take 0 or more aguments.
# If called via button on sheet, it passes event to script.
def iScrapePricesPy(*args):   
   
   # This is the main function to start the script
   oDoc = XSCRIPTCONTEXT.getDocument()
   oSheets = oDoc.getSheets()
   myPortfolioSheet = oSheets.getByName("Portfolio")
   myPensionSheet = oSheets.getByName("Pension")
   parentwin = oDoc.CurrentController.Frame.ContainerWindow

   # Creat Ordered Dictionary of shares we're fetching prices for
   SharesDict = collections.OrderedDict()
   #      Yahoo Ticker   = [SheetWritingTo, CellWritingTo, MultiplicationFactor]      
   SharesDict["RCP.L"]     = [myPortfolioSheet, "O17", NO_CONVERSION_MULT_BY_ONE]
   SharesDict["SLV"]      = [myPortfolioSheet, "O26", SLV_CONV_FACTOR]
   SharesDict["GLD"]       = [myPortfolioSheet, "O28", GLD_CONV_FACTOR]
   SharesDict["IBTS.L"]    = [myPortfolioSheet, "O34", NO_CONVERSION_MULT_BY_ONE]
   SharesDict["PSLV"]      = [myPensionSheet, "O24", NO_CONVERSION_MULT_BY_ONE]

   # Get the url needed to fetch prices
   yahooQuery = ConstructQueryUrlFromTickers(SharesDict)
   
   # Fetch the JSON data and load it into array for processing
   yahooJSONResponse  =  urlopen(yahooQuery)
   charset = yahooJSONResponse.headers.get_content_charset()
   yahooPriceInfo = json.loads(yahooJSONResponse.read().decode(charset))
   pricesList = yahooPriceInfo["quoteResponse"]["result"]   # Prices in same order as in URL

   # Parse our dictionary, for each share print the price
   for ticker, vals in SharesDict.items():

      # Get the Index of this share in the pricesList.  This works as we used OrderedDict
      index = list(SharesDict.keys()).index(ticker)
      quoteVal = pricesList[index]['regularMarketPrice']

      # Write value into approprite sheet
      WriteValueToSheet(SharesDict[ticker], quoteVal)

   
   messageBox("Processing finished", "Status")

#=========================================================================
#Define which functions are visible in My Macros
g_exportedScripts = iScrapePricesPy,


-0x3F

[0x3F]

I've just noticed that if xrates are defined in url request, they are always returned first. So I need to define them at the beginning of my dictionary.

I guess at some point they'll break everything by returning stuff in a random order (And I'll wish I had stayed with the regex!)

[desmid]

That's very interesting 0x3F. I particularly like that you use the 'json' standard library.

Back in November/December I built YAPS (Yet Another Price Scraper - maybe I will use that name). It's at:

https://github.com/desmid/demo

so it is downloadable. If people such as yourself, kiloran or anyone else is interested, we could make it a communal project.

If nothing else it might contribute a few ideas to the mix. The following is an extract from that web page:

Functional requirements

- Update a LO/OO spreadsheet containing columns of Yahoo tickers with a button that populates specified columns with market price and currency.
- Data columns will be in register with the ticker column, typically adjacent, but may be anywhere else in the sheet and in any order.
- Recognise a ticker in a spreadsheet cell as a distinct uppercased word of form:

Code: Select all

      Stock            FX pair              Index
      -------------------------------------------
      BP               EURUSD=X             ^FTSE
      BP.              EUR/USD              FTSE
      BP.L             EUR:USD
                       EURUSD


- Ignore other text not matching these types of pattern.
- Web fetches should be robust and produce an informative message on failure.

Design requirements

- Macros must be embedded within the spreadsheet.
- Macros must use human-readable spreadsheet coordinates like A1, B10:B100, C.
- Spreadsheet operations should be abstracted behind a facade.
- Code should be structured and reusable, not a monolithic blob.
- Code should be extendable to other data sources.
- Code should use a logging facility.

I stopped work on it as it currently does what I need, but please join in!

desmid

[0x3F]

Back in November/December I built YAPS (Yet Another Price Scraper - maybe I will use that name).

Thanks, your repo looks pretty comprehensive with use of test harness etc. I'll probably learn a thing or two looking over it . Python isn't my primary language, so always interesting to see how other people go about things. It's good fun to hack about with.

I've got a git account too, I should probably add in my script. I had another script to populate historical prices, though think that has broke too - it downloaded multiple prices covering diff dates. Not sure that yahoo url is still available.

-0x3F

[JMN2]

I don't understand why the actual tickers are hardcoded into the code instead of having them as a list on a spreadsheet itself, now if tickers change one has to change the code...wouldn't it be easier to have the code look at the tickers on a spreadsheet?

[0x3F]

I don't understand why the actual tickers are hardcoded into the code instead of having them as a list on a spreadsheet itself, now if tickers change one has to change the code...wouldn't it be easier to have the code look at the tickers on a spreadsheet?

My solution is really for my own circumstance. As my portfolio won't change, in practice I have no need to search the spreadsheet for tickers and it's easier just to hard code them with their multipication factors. This also has the advantage (for me) of keeping all the logic in one place. For most people, I accept that using the existing method to grab the tickers may to be better.

Additionally, my spreadsheet layout doesn't have a single contiguous column of tickers and so would be messy to parse (not impossible, just easier for me to implement and maintain if I hard code). Others who have similar layouts may find my solution useful.

-0x3F

[GN100]

It does work for me. In fact, any yahoo ticker I've tried so far has worked fine.

Strange, I have just re tried with GBPUSD=X and it just produces a blank cell.

I just tried Simple_Yahoo_Price_Scrape_1-3a.ods using LibreOffice 5.4.3.2 and Windows 7 and the following all worked OK:

GBPUSD=X
USDGBP=X
GBPEUR=X
EURGBP=X

--kiloran

I am still not getting anywhere with my standard set up and trying to scrape any of these currencies. Using Win 10 Pro and office 2007 the currency codes return a blank cell but all the share tickers work OK. I have reinstalled Office and still no change - I have also tried on a second PC, similar set up with no success. However if I install Libre Office and download the appropriate price scrape it does work. Strange.

[kiloran]

Strange, I have just re tried with GBPUSD=X and it just produces a blank cell.

I just tried Simple_Yahoo_Price_Scrape_1-3a.ods using LibreOffice 5.4.3.2 and Windows 7 and the following all worked OK:

GBPUSD=X
USDGBP=X
GBPEUR=X
EURGBP=X

--kiloran

I am still not getting anywhere with my standard set up and trying to scrape any of these currencies. Using Win 10 Pro and office 2007 the currency codes return a blank cell but all the share tickers work OK. I have reinstalled Office and still no change - I have also tried on a second PC, similar set up with no success. However if I install Libre Office and download the appropriate price scrape it does work. Strange.

No, it's not strange. I didn't know that you were using Excel. Detail like this is so important.

The Excel version appends .L to the ticker (so SSE becomes SSE.L). Therefore GBPUSD=X is translated to GBPUSD=X.L and this will not work. The Excel version was a very simplified version of the process used by the HYP Top-Up spreadsheet.

When I developed the LibreOffice version I thought that not appending the .L would add more flexibility.

See http://lemonfoolfinancialsoftware.weebl ... crape.html

--kiloran

[GN100]

Thanks Kiloran for the explanation - and apologies for my not being specific.

GN

[JMN2]

I wonder how the code would have to be changed in order to have it working in a worksheet called "portfolio" and down from the cell E6 to E51, the ticker in a C column and column D is in the middle unused...I'll just experiment and try to understand the code what determines what...I will revert next week.

Since you are not experienced with Python/LibreOffice, the following thoughts may help....

--kiloran

Kiloran, above proved too difficult for me, in the vba macro code it was more obvious and with trial and error doable for a novice. Luckily, I can do without it.

However, looking at the Python code, I can't understand what is the letter code that actually tells that the last price or close price is brought in? IIRC, Yahoo has various letter & number combos that represent previous close, and all kinds of information, for instance last price was l1, p was previous close, change c1, open o, day's low g, etc.

It would be handy to have a separate code to bring in previous close in order to see what has changed during the day but when I look at the code there is nothing that tells me why or what is bringing in the last price. How does yahoo know it sends the last price and not something else?