Install the Example Quote Source

From PalOMoney
Jump to navigation Jump to search

Introduction

PalOMoney includes an example external quote source, #QuoteCSV.py (below) that gets quotes from the Yahoo! Finance API and imports them into MS Money. Because the API used in the example does not return column headers to identify the fields in the .csv file, the example also includes an accompanying example schema file, #QuoteCSVSchema.csv below.

QuoteCSV.py is a very simple python script. Python is the name of a high level computer scripting language and an interpreter program that executes the script. You might have a python interpreter already installed to use python solutions for updating MS Money quotes and bank statements, etc. If not, see #Installing Python below.

Testers: This is an advanced topic. This documentation can be improved. Please register on this wiki and fix any simple errors, or leave a note in the discussion tab for more complex issues.

Instructions

  • If necessary, download and install python and required python libraries. See #Installing Python.
  • Locate and copy QuoteCSV.py and QuoteCSVSchema.csv in the Program Files PalOMoney folder to your python folder (or elsewhere). Please try to keep the original files intact and in place should you need them in the future.
  • Launch PalOMoney, and open a Money file. We will install the external quote source into this file's folder for use by all Money files in that folder.
  • Click the Quote Sources button labeled "..." to the right of the Quote Source selector to open the Quote Sources dialog.
  • Click the New button to start a new quote source.
  • Enter the fields according to the settings in #YahooExtQuoteCSV Section below.
  • Click Ok to save the settings and close the dialog. Observe that the new source is displayed in the Quote Source selector.
  • Hit Update Now to launch the method and update the quotes. Observe a few windows flash open and close, and then the updated quotes and Total Value. Notice how fast it is -- only about a second. This method is fast because it batches symbols together and calls the Yahoo API only once per batch, unlike the internal method, which uses the same call for each symbol individually.

Troubleshooting a.k.a. Debugging

Too bad Siri doesn't work, otherwise we would just ask her to Fix it, Siri.

Try figure out what is going wrong. If you can't, that is a support issue that you should report on the Support Forum or Support Ticket System.

  • Did a message dialog appear? PalOMoney has built in sanity checking for some common problems. If you get a dialog message, there's usually a simple problem with what you entered.

External source settings are stored in PalOMoney.ini. PalOMoney saves PalOMoney.ini in the same folder as each Money file you open.

  • To troubleshoot, locate and open PalOMoney.ini and verify that the settings at the end of the file match those in #PalOMoney.ini with Example below.
  • Did any windows appear? If not, PalOMoney didn't launch the quote source. There could be a problem with your command interpreter. Check your Windows configuration.
  • Did the method write its output file (QuoteCSVpy.csv)? Open the folder specified in the Output control, and see if the file exists, and open it with NotePad and look for some quote data.
    • If there is no file, make sure you have permission to write to the python folder.
    • Make sure your internet connection is working.
    • Check and compare PalOMoney.ini #MoneyPal Section below.
    • Try pasting the URL portion of the Last External Launch command line into your browser address bar. You should see, or download, a .csv file with the desired quotes.
    • Try pasting entire Last External Launch command line into a command prompt window in the python folder. It should create the output file.
    • Hit View Logs on the Portfolio View window, and see what if anything has been recently written.
    • Look for common pitfalls. For example, the command interpreter requires that arguments that contain spaces or special characters (e.g., '&') be surrounded by double quotes. The following special characters require quotation marks: & < > [ ] { } ^ = ; ! ' + , ` ~ [white space]
  • Is the QuoteCSVpy.csv output file Ok, but nothing has changed in the Portfolio View window? This might be correct behavior. Keep in mind that PalOMoney does not update quotes that already have the same price. It should not create an new online quote if there is a correct online or manual quote already.
    • Hit View Logs and examine the recently written log files for clues.

PalOMoney.ini with Example

PalOMoney saves PalOMoney.ini in the same folder as each Money file you open. This means that if you are working with several Money files in different folders, you will have to install external quote sources in each folder. If you move your Money file, you can also move your PalOMoney.ini file as well to preserve the PalOMoney settings.

Ini files store configuration in a human readable format. Ini files consist of [sections] enclosed in square brackets. Each section contains one or more Key=Value pairs, stored one per line, with an equals sign separating the Key and its Value. Section and Key names are case insensitive, so "Settings" matches "settings".

Quote sources are stored in the [Quote Sources] section. The internal quote sources are listed first, followed by any installed external quote sources. The Key is the name you have given the quote source. The Value is the name of the section header containing the settings for that source.

AlphaVantage Replaces Yahoo

Note: As of the beginning of November, 2017, the Yahoo Finance APIs used below stopped working. See AlphaVantage Quote Source. Because of this, PalOMoney was updated to version 1.0.8, and MoneyPal was also updated to support AlphaVantage, and AlphaVantage.CSV has become the new default quote source.

Quote Sources Section
[Quote Sources]
AlphaVantage.CSV=AlphaVantage.CSV Section
Finance.Yahoo.CSV=Finance.Yahoo.CSV Section
Finance.Yahoo.YQL.XML=Finance.Yahoo.YQL.XML Section
File.CSV=File.CSV Section
File.YQL.XML=File.YQL.XML Section
YahooExtQuoteCSV=YahooExtQuoteCSV Section

In this example, we see that the external source named "YahooExtQuoteCSV" stores its settings in the [[#YahooExtQuoteCSV Section].

YahooExtQuoteCSV Section

External quote sources are typically found the end of the PalOMoney.ini file. Here, we see the [YahooExtQuoteCSV Section]. 

[YahooExtQuoteCSV Section]
Name=YahooExtQuoteCSV
AddOn=c:\python27\python
Output File=c:\python27\QuoteCSVpy.csv
Output Format=0
Header Schema=0
Schema=c:\python27\QuoteCSVSchema.csv
URL Template=c:\python27\QuoteCSV.py  "http://finance.yahoo.com/d/quotes.csv?s=%s+&f=sl1d1t1"
Request Tickers=20
Tickers in Output=1
Real Time=1
Historical=0
Login ID=
Password=

The key values closely but not exactly correspond to the labels in the Quote Sources dialog. Put another way, every setting in the dialog is stored in this section.

  • Notice that the AddOn is the python interpreter, not the script. The example script path and name is the first argument given to the python interpreter in the URL Template. Here this argument does not need quotes because it does not contain spaces or special characters.
  • Notice that the second argument is quoted because it contains the '&' special character.
MoneyPal Section

Open PalOMoney.ini in your MS Money folder, and find and compare [MoneyPal] section with the example below. It contains the Last External Launch and Last Launch keys. 

[MoneyPal]
Last Launch="C:\Program Files\PalOMoney.com\PalOMoney\MoneyPal.exe" C:\Finance\k2008s.mny -s2 -fc:\python27\QuoteCSVpy.csv -cc:\python27\QuoteCSVSchema.csv
Last External Launch=c:\python27\python c:\python27\QuoteCSV.py  "http://finance.yahoo.com/d/quotes.csv?s=AAPL+GOOG+PSX+COH&f=sl1d1t1" c:\python27\QuoteCSVpy.csv
Quotes Updated=2013/01/24 11:26
Debug Level=0

You can open a Command Prompt window, copy, and paste these two lines directly at the command prompt, press Enter, and observe what happens. The Command Prompt window will stay open after each command has finished, so you can read whatever messages the commands produce.

QuoteCSV.py

<code>
 import os, urllib2, sys
 argv = sys.argv

 if __name__=="__main__":
    if len(argv) < 3:
        print "Usage:",argv[0], "YahooURLSpec OutputFileName"
        if len(argv) > 0:
            print "1:", argv[1]
        if len(argv) > 1:
            print "2:", argv[2]
        sys.exit()
    #url = "http://finance.yahoo.com/d/quotes.csv?s=%s&f=nl1d1t1" % argv[1]
    url = argv[1]
    csv = urllib2.urlopen(url).read()
    f = file(argv[2],"w")
    f.write(csv)
    f.close()
</code>

QuoteCSVSchema.csv

Of course, it is trivial to modify QuoteCSV.py to include the header line, but then we wouldn't have an example of a schema file to give you. Testers in particular should test using the separate schema file.

Ticker, Close, Date, Time


Installing Python

Windows does not include a python interpreter, but you can download and install one for free. Python comes in a multitude of versions, and the latest version is of course improved, but it does not work with older python scripts. The safest bet is to install version 2.7.3, which you can get at Python.org. Because the command interpreter is frequently used to execute python scripts, it is handy to install python just on your C drive root, say at C:\python27. You will also have to download and install python libraries (e.g., os and urllib is used by the example) to extend python's functionality.

Retrieved from "https://www.palomoney.com/wiki/index.php?title=Install_the_Example_Quote_Source&oldid=356"