32:T639,md`## Prerequisites 1. Set up a dummy **Watch Account** in ***Money***: The article entitled [*Obtain stock and fund quotes after July 2013*](http://moneymvps.org/faq/article/651.aspx) details the steps with a Java-based program. The instructions on initially creating a **Watch Account** in the **Installation** section is critical to the import. The rest of the article is specific to the [Java-based](https://en.wikipedia.org/wiki/Java_(programming_language%29) program and is replaced with the OFX file created by this notebook's [JavaScript](https://en.wikipedia.org/wiki/JavaScript). 2. [Publish ](https://support.google.com/a/users/answer/9308870) a Google Sheet with security symbols and dynamic quotes using the [GOOGLEFINANCE function](https://support.google.com/docs/answer/3093281?hl=en). 3. Use the logic in this notebook that references a Google Sheet's [***spreadsheetId***](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id) to access the spreadsheets content as a CSV file: This notebook uses a [sample Google Sheet](https://docs.google.com/spreadsheets/d/1e53kLtCKlcAKVyEFZOi0N7ABj0XWTScY1SGMqWEb4eA) and loads the data into a [***googleData***](#googleData) variable: csvURL = *https://docs.google.com/spreadsheets/d/spreadsheetId/export?format=csv* Your custom Google Sheet, if it's formatted like this notebook's sample, can be referenced dynamically with the ***Get Google Sheet*** submit button to generate the contents of the OFX file specific to your securities' data. `33:T5cc,md`## Sample Google Sheet The following URL references a Google Sheet with three securities; 1. [stock](https://en.wikipedia.org/wiki/Stock) 2. [stock ADR](https://en.wikipedia.org/wiki/ADR) 3. [mutual fund](https://en.wikipedia.org/wiki/Mutual_fund). It utilizes the [GOOGLEFINANCE function](https://support.google.com/docs/answer/3093281?hl=en) to: * Dynamically retrieve the security's quote by referencing it's symbol in Column A and assigning it to its respective cell in Column B. * It then determines if the security is categorized as a stock or mutual fund; placing the results in its respective cell in Column C. |Column | Content (n=row number) | |-|-| |A|Security Ticker Symbol| |B|=GOOGLEFINANCE(A***n***)| |C|=If(IFNA(GOOGLEFINANCE(A***n***,"expenseratio"),false),"mutual fund","stock")| Select an indvidual cell to see its symbol or formula. **Update**: As [this Bogleheads.org forum details](https://www.bogleheads.org/forum/viewtopic.php?t=323220), the ["exspenseratio"](https://support.google.com/docs/answer/3093281?hl=en) attribute for the GOOGLEFINANCE function is not reliable. The workaround is to manually enter your security's type ("stock"/"mutual fund") in the spreadsheet rather than using the function. The type should match the exact designation of the security when it was intially registered/setup within Money. [ETFs](https://en.wikipedia.org/wiki/ETF_Securities) should be classified as "mutual fund". `34:T477,md `Once the file has been generated, it can be downloaded to a local file and given the name ***quotes.ofx*** on your PC. The [mimetype](https://developer.mozilla.org/en-US/docs/Web/API/Blob/type) associated with download has been assigned to [application/x-msmoney](https://slick.pl/kb/htaccess/complete-list-mime-types/). If your browser ([Chrome](https://www.techjunkie.com/automatically-open-downloads-chrome/), [Edge](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_win10-msoversion_other/always-open-files-of-this-type/68910598-cc0c-4603-81ea-acb1902109f1), [FireFox](https://support.mozilla.org/en-US/kb/change-firefox-behavior-when-open-file)) is properly configured for opening an OFX file, the ***Money*** application will automatically initiate and import the file with the [Microsoft Money Import Handler](http://moneymvps.org/faq/article/407.aspx). Alternatively, you can copy/paste/save the contents of the OFX file with an editor like [***Notepad***](https://en.wikipedia.org/wiki/Microsoft_Notepad); then [import](http://moneymvps.org/faq/article/453.aspx) the newly created file into ***Money***. `35:T7b2,function ofxFile() { // Open File Exchange specifications: https://www.ofx.net/downloads/OFX%202.2.pdf // Date format: page 89, 3.2.8.2 Date and Datetime YYYYMMDDHHMMSS GMT const strDate = new Date().toISOString().substring(0,19).replace(/T/g,"").replace(/-/g,"").replace(/:/g,"") ; // Why DTASOF is set to tomorrow: // https://github.com/hleofxquotes/hleofxquotes/tree/master/dist/misc/quotes/timezone const tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1); const strTomorrow = tomorrow.toISOString().substring(0,10).replace(/T/g,"").replace(/-/g,""); let ofx = startXML(strDate, strTomorrow); googleData.forEach(security => ofx += security[2] == "stock" ? posstock(security[0], security[1], strDate) : posmf(security[0], security[1], strDate) ); ofx += startInfo(); googleData.forEach(security => ofx += security[2] == "stock" ? stockinfo(security[0], security[1]) : mfinfo(security[0], security[1]) ); ofx += footer(); ofx = header() + formatXml(ofx); const copyXMLButton = html`` copyXMLButton.onclick = (e) => { e.preventDefault(); document.getElementById("ofxText").select(); document.execCommand("copy"); } // https://observablehq.com/@observablehq/standard-library const downloadXMLButton = DOM.download( // https://www.lifewire.com/file-extensions-and-mime-types-3469109 new Blob([ofx], {type: "application/x-msmoney"}), "quotes.ofx", "Download OFX file" ) return html`
${downloadXMLButton}${copyXMLButton}`; } 36:T403,function startXML(strDate, strTomorrow){ const xml = ` 0 INFO Successful Sign On ${strDate} ENG 20010918083000 broker.com ${createUUID()} 0 INFO 4 ${strTomorrow} USD dummybroker.com 0123456789 `; return xml; // https://www.arungudelli.com/tutorial/javascript/how-to-create-uuid-guid-in-javascript-with-examples/ function createUUID() { return ([1e7]+-1e3+-4e3+-8e3+-1e11).replace(/[018]/g, c => (c ^ crypto.getRandomValues(new Uint8Array(1))[0] & 15 >> c / 4).toString(16) ) } } 37:[{"id":"74ca750b29a20912573f461135ab1e9476c3fa7c807a7d237144417be4b1c116f3c45b2b9f0e0b21d92baae78bebfadaca81cc5b260e4bbfa61ae79f5be66e75","href":"https://static.observableusercontent.com/files/74ca750b29a20912573f461135ab1e9476c3fa7c807a7d237144417be4b1c116f3c45b2b9f0e0b21d92baae78bebfadaca81cc5b260e4bbfa61ae79f5be66e75","name":"MSMoneyPortfolio@1.jpg","type":"image/jpeg","lastModified":1608352217616.667,"size":158581}] 30:{"title":"Microsoft Money Sunset Edition Open Financial Exchange (OFX) file for Updating Portfolio Security Prices","cells":[{"id":0,"value":"md`# [Microsoft Money Sunset Edition](https://download.cnet.com/Microsoft-Money-Plus-Sunset-Deluxe/3000-2057_4-77545178.html) [Open Financial Exchange (OFX)](https://www.ofx.net/) [file](http://moneymvps.org/faq/article/8.aspx) for Updating Portfolio Security Prices\n![](${await FileAttachment(\"MSMoneyPortfolio@1.jpg\").url()})\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":12,"value":"toc()","pinned":false,"hidden":false,"mode":"ojs"},{"id":16,"value":"md `## JavaScript Solution Using Google Sheets\n* When Microsoft [sunsetted](https://www.microsoft.com/en-us/download/details.aspx?id=20738) the [***Money***](https://en.wikipedia.org/wiki/Microsoft_Money) personal finance application, [several alternative techniques](http://moneymvps.org/faq/article/27.aspx) were developed to keep the portfolio data updated with [OFX formatted files](http://moneymvps.org/faq/article/8.aspx) [(specification)](https://www.ofx.net/downloads/OFX%202.2.pdf). \n\n Each technique involved gathering current security price quotes from a list maintained externally to the ***Money*** application, which then updated a [***Watch Account***](http://moneymvps.org/faq/article/651.aspx) when the OFX file was imported.\n* This solution uses the [GOOGLEFINANCE function](https://support.google.com/docs/answer/3093281?hl=en) hosted in a [Google Sheet](https://www.google.com/sheets/about/) to data-wrangle financial security prices to create an OFX file suitable for import into ***Money***.\n\n\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":360,"value":"md `### Google Apps Script\n***Note***: As an alternative, the logic in this notebook is also available in a [Google Apps Script](https://github.com/MarioDelgadoSr/Google_Sheet_Script_to_OFX) that requires [installation and authorization](https://developers.google.com/apps-script/guides/services/authorization) in the Google Sheet.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":39,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":60,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":394,"value":"viewof googleSpreadSheetURL= textarea({\n title: \"Google Sheet's URL:\", \n value: \"https://docs.google.com/spreadsheets/d/1e53kLtCKlcAKVyEFZOi0N7ABj0XWTScY1SGMqWEb4eA\",\n width: \"100%\",\n rows: 1,\n submit: \"Get Google Sheet\",\n description: \"Reference the URL to your Google Sheet that contains securities data formatted similarly to the sample spreadsheet to generate and OFX file. Then click the 'Get Google Sheet' button.\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"md ``","pinned":false,"hidden":false,"mode":"ojs"},{"id":76,"value":"md `## Spreadsheet Data accessed via CSV file format\nThe CSV URL is determined using the [spreadsheetId](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id):`","pinned":false,"hidden":false,"mode":"ojs"},{"id":107,"value":"// https://regexr.com/\ncsvURL = `https://docs.google.com/spreadsheets/d/${\n googleSpreadSheetURL.match(`/spreadsheets/d/([a-zA-Z0-9-_]+)`)[1]\n}/export?format=csv`","pinned":false,"hidden":false,"mode":"ojs"},{"id":144,"value":"md `D3's [text](https://github.com/d3/d3-fetch/blob/v3.0.1/README.md#text) and [csvParseRows](https://github.com/d3/d3-dsv/blob/v3.0.1/README.md#csvParseRows) methods are used to retrieve the data in the spreadsheet:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":83,"value":"googleData = d3.csvParseRows(await d3.text(csvURL)); ","pinned":false,"hidden":false,"mode":"ojs"},{"id":199,"value":"md `### Use the ***ofxFile()*** Function to Create the OFX file`","pinned":false,"hidden":false,"mode":"ojs"},{"id":188,"value":"ofxFile()","pinned":true,"hidden":false,"mode":"ojs"},{"id":191,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":150,"value":"md `## Functions Used to Generate the OFX file`","pinned":false,"hidden":false,"mode":"ojs"},{"id":182,"value":"$35","pinned":false,"hidden":false,"mode":"ojs"},{"id":179,"value":"function header(){\n \nconst headerXML = \n`OFXHEADER:100\nDATA:OFXSGML\nVERSION:102\nSECURITY:NONE\nENCODING:USASCII\nCHARSET:1252\nCOMPRESSION:NONE\nOLDFILEUID:NONE\nNEWFILEUID:NONE`;\n \nreturn headerXML; \n\n} ","pinned":false,"hidden":false,"mode":"ojs"},{"id":176,"value":"$36","pinned":false,"hidden":false,"mode":"ojs"},{"id":172,"value":"function posstock(strSecurity,price,strDate){\n \n const posstockXML =\n`\n\n\n${strSecurity}\nTICKER\n\nCASH\nLONG\n0\n${price}\n${price}\n${strDate}\n\n`;\n \n return posstockXML; \n \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":168,"value":"function posmf(strSecurity,price,strDate){\n \n const posmfXML = \n`\n\n\n${strSecurity}\nTICKER\n\nCASH\nLONG\n0\n${price}\n${price}\n${strDate}\n\n`;\n \n return posmfXML; \n} ","pinned":false,"hidden":false,"mode":"ojs"},{"id":184,"value":"function startInfo(){\n \n const startInfoXML =\n`\n\n\n\n\n\n`;\n \n return startInfoXML;\n \n} ","pinned":false,"hidden":false,"mode":"ojs"},{"id":165,"value":"function stockinfo(strSecurity, price){\n\nconst stockinfoXML = \n`\n\n\n${strSecurity}\nTICKER\n\n${strSecurity}\n${strSecurity}\n${price}\n\n`;\n\n\treturn stockinfoXML\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":162,"value":"function mfinfo(strSecurity, price){\n\n\tconst mfinfoXML = \n`\n\n\n${strSecurity}\nTICKER\n\n${strSecurity}\n${strSecurity}\n${price}\n\nOPENEND\n`\n\n return mfinfoXML;\n}\t","pinned":false,"hidden":false,"mode":"ojs"},{"id":157,"value":"function footer(){\n \n const footerXML = \n`\n\n`;\n \n return footerXML;\n \n} ","pinned":false,"hidden":false,"mode":"ojs"},{"id":153,"value":"// https://stackoverflow.com/a/49458964\nfunction formatXml(xml, tab) { // tab = optional indent value, default is tab (\\t)\n var formatted = '', indent= '';\n tab = tab || '\\t';\n xml.split(/>\\s*\\r\\n';\n if (node.match( /^]*[^\\/]$/ )) indent += tab; // increase indent\n });\n return formatted.substring(1, formatted.length-3);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":74,"value":"md `## Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":80,"value":"d3 = require(\"d3@5\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"import {toc} from \"@mbostock/toc\"\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":391,"value":"import {textarea} from \"@jashkenas/inputs\"\n","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W37","stdlib":"1","titling":"auto"}