kiloran wrote: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
. 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.