admin/info: stock updating scripts
Feb. 12th, 2011 03:59 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
seperis posting in ![[community profile]](https://www.dreamwidth.org/img/silk/identity/community.png){kind=small}
market_roulettePlease read this; this is mandatory information regarding updates to market_roulette.
March 2011
March will start the first month of transaction fees and the first month of availability of mutual funds and ETFs. Before teh end of the month, both will be covered by short tutorials, but trust me, they're nothing to be worried about.
March will also show a change in how we record transactions and get stock prices to autoupdate, which has been a problem.
Your Individual Spreadsheets
In March, your individual spreadsheets will be changing slightly to pursue autoupdating from the transaction log. I've been doing this in steps to see if it could be done without inconvenience, and as it turns out, it works if I script everything, and so I had to learn how to script first. So I did. Note: motivation is the key to learning something very fast. Shocking, I know. I was very motivated.
The following changes will occur to your spreadsheets:
1.) Transactions recorded in the transaction log will be added to your spreadsheet daily by script. You will have the individual option to run the script manually, but it will be set on a timer for each of you at a different time of day so as not to overload, but I'm going to try and set it as close to your local 8 AM as possible to hit everyone's market openings. So far testing has shown this will work. So far.
At the beginning, the moderators will probably be doing this manually until I'm sure it will work. This is google and it does time out for no reason occasionally or throws weird errors.
2.) Each spreadsheet will be autoupdated to your currency, even if google doesn't have it. Again, a script will run this and will be run probably weekly just in case google does something odd, but that will also have the option to be run manually. This includes currencies google doesn't list.
3.) All conversions will be run automatically by script when your indiviual spreadsheet is updated. Also can be run manually.
4.) I've built three scripts so far to do the equivalent of what google finance does individually for google; google, yahoo, businessweek, and Globe and Mail will now do it en mass for any group of symbols. All will be set to run manually only so as not to hit the sites too often. For this to work, your tickers will need to be formatted correctly until I have time to script a detection script, but just use the ticker as it appears on one of those sites and it will both update the price and convert it to your currency.
5.) The scripts will also do all your math for you. I've only been able to test this in artificial conditions, so I know the math works, but not how well. We'll see how this goes.
6.) All page creation for new months and the formatting of those pages will be done by script automatically, or so testing has concluded. There's an option during creation to automatically set it to your timezone and currency if available; if i'ts not, a custom script will run afterward to alter it.
7.) Your individual spreadsheet will contain the following.
Basically, I'm going to make this as much like a portfolio as possible. Eventually, I'll build an interface for you to use and you won't need to look directly at the spreadsheet at all, but I didn't have time to do much with that other than learn the outline of how it's done from google apps. Eventually, if I'm right, I should be able to move your buying and selling to your personal interface entirely, but I don't know hwo long it will take to get good at making them, so that will have to wait a month or two.
You and the Transaction Log
Since we can move to autoupdating, I'm stripping it down since some of the information was needed to record the transactions, and since it will do that for us, it's not needed. The new log will have fewer pages and require less information to complete, but will require higher accuracy.
The following fields will still be there, but an explanation of what to do with some of them has been added.
The sections for real time adn automated might change, but I"m not sure how much yet until i've run some testing on how it will interact with your spreadsheet. What I want to have happen is when it is automated, a script will run automatically at the correct time to buy, but that's been a little tricky with multiple market times. That probably won't go into effect until at the earliest, April.
You and the Scripts
The scripts will be housed in everyone's spreadsheets and tweaked around when they go into effect. I've been running these on Seperis World conditions, not Real World, and God knows there will be fail points I haven't seen yet, including the fact that all websites are inconsistent and the way I have them pulling information will have fail points I'll have to write workarounds for.
I'm going to ask at first that you don't make any changes to the scripts themselves but create a copy to make changes to if you're inclined to that sort of thing. It's something I do--well, something I do a lot, come to think--and I encourage anyone to tweak them around as much as they like. And ask if you want help tweaking! If I can't figure it out, I can learn how.
If you'd like to learn about them and how they run, I'd love to go over it with you. They're fairly simple and I've gone back to document them, so anything in there that you'd like to change or tweak is fine. Just do it on separate copies. The Data page has some of the outline of where things are that the scripts are running on and are reference points for the scripts themselves.
To get anyone started who is interested, below is the code for the script to pull prices from Globe and Mail. The ones for Yahoo and Businesweek use almost exactly the same format, just tweaked for individual site.
Currently, they pull the following information by ticker; company name, stock price, stock exchange, stock currency, and current date and time. I've added documentation notes here. Note are behind :::: so you can copy and paste this into your spreadsheet if you want to test it using the ticker format on Globe and Mail after removing anything between :::: and ::::
Er, this is one of the ones with the odd variable names.
function getFinanceYahoo() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet26");
::::this is Googledocs required code for working with spreadsheets. "var sheet" sets it as a variable with the name sheet. SpreadsheetApp is the googledocs application being called. getActiveSpreadsheet is a method, or function, of SpreadsheetApp that indicates I want to work with or get the sheet active so it can be worked on.. getSheetName refers to the name of the sheet; this is why sheets are named. The sheet name is the name of teh sheet I've been using for testing.::::
var ticker=sheet.getRange(10,1).getValue();
::::this is Googledocs required code for indicating a location on the page listed above, Sheet26. sheet is the variable that indicates that entire first string of code, getRange refers to the range of cells I want it to check, teh numbers in parenthesis are teh row number first, then column number, of the cell I want it to check and getValue means I want this to get whatever is listed in that cell. In this case, it is the location of the stock ticker I want to get teh price for. It's at row 10, column 1, or A10.::::
myStock = new finance();
::::this is creating a new instance of the object finance. I had to learn how to create objects in javascript, but it makes it easier to make the script more universal. MyStock is now the name of this instance of the object finance. And also because I'm competitive and wanted to make something as close to Google's own finance app as possible.::::
var chick = myStock.person(ticker);
::::teh variable 'chick' is created to refer to myStock and is used to call the object person and invest it with the ticker symbol. I actually don't know why I needed to create a finance object and a person object to do this, but it didn't work with just one object. Forty hours of testing to figure that out. *sighs*::::
var hat = myStock.getname();
::::variable hat is created to tell myStock to run the script getname, which gets the name of the company.::::
sheet.getRange(11,1).setValue(myStock.name);
::::var ticker showed this code to get a value. I'm telling it to use the infromation in teh "sheet" variable to get a new location to put a value; in this case, 11, 1, or A11, just below the ticker. This will be altered to do it horizontally, but i was testing vertically because it was easier to watch.
Repeat the code below to get price, exchange, etc::::
var yes = myStock.getprice();
sheet.getRange(12,1).setValue(myStock.price);
var dance = myStock.getexchange();
sheet.getRange(13,1).setValue(myStock.exchange);
var dance = myStock.getcurrency();
sheet.getRange(14,1).setValue(myStock.currency);
var dance = myStock.getdatetime();
sheet.getRange(15,1).setValue(myStock.datetime);
}
:::this script creates objects named finance and within finance, creates the person object.::::
function finance() {
this.person=person;
}
::::here is where I say, I understand object creation in C++, Java, and Perl. I get weirded out in javascript adn I don't know why. I get what I'm doing. I don't know why I need two objects. Or I do, but I want it more streamlined so I don't have to use two lines of code to call everything in the first script instead of one like Google finance can do. Which is because GoogleFinance is a program on google already, whereas I am aware I'm creating from scratch, but I feel like a lesser individual or something.
I have issues. This lacks elegance.
Anyway, person holds all the relevant information and runs a separate script for each thing you want to call. I'm goign ot eventually streamline that, but right now, I'm still finding inconsistencies between different pages in each site, so each different item, company, name, exchange, has its' own script.
The rest are the individual scripts to grab data from the page. Feel free to play with and tweak if desired.::::
function person(ticker,name,exchange,currency,datetime,price) {
this.ticker=ticker;
this.name=name;
this.exchange=exchange;
this.currency=currency;
this.price=price
this.datetime=datetime;
this.getname=getname;
this.getprice=getprice;
this.getcurrency=getcurrency;
this.getexchange=getexchange;
this.getdatetime=getdatetime;
}
function getname(ticker) {
var xmlstring=getXML();
var match=xmlstring.match(/(.*?)<\/h4>/);
March 2011
March will start the first month of transaction fees and the first month of availability of mutual funds and ETFs. Before teh end of the month, both will be covered by short tutorials, but trust me, they're nothing to be worried about.
March will also show a change in how we record transactions and get stock prices to autoupdate, which has been a problem.
Your Individual Spreadsheets
In March, your individual spreadsheets will be changing slightly to pursue autoupdating from the transaction log. I've been doing this in steps to see if it could be done without inconvenience, and as it turns out, it works if I script everything, and so I had to learn how to script first. So I did. Note: motivation is the key to learning something very fast. Shocking, I know. I was very motivated.
The following changes will occur to your spreadsheets:
1.) Transactions recorded in the transaction log will be added to your spreadsheet daily by script. You will have the individual option to run the script manually, but it will be set on a timer for each of you at a different time of day so as not to overload, but I'm going to try and set it as close to your local 8 AM as possible to hit everyone's market openings. So far testing has shown this will work. So far.
At the beginning, the moderators will probably be doing this manually until I'm sure it will work. This is google and it does time out for no reason occasionally or throws weird errors.
2.) Each spreadsheet will be autoupdated to your currency, even if google doesn't have it. Again, a script will run this and will be run probably weekly just in case google does something odd, but that will also have the option to be run manually. This includes currencies google doesn't list.
3.) All conversions will be run automatically by script when your indiviual spreadsheet is updated. Also can be run manually.
4.) I've built three scripts so far to do the equivalent of what google finance does individually for google; google, yahoo, businessweek, and Globe and Mail will now do it en mass for any group of symbols. All will be set to run manually only so as not to hit the sites too often. For this to work, your tickers will need to be formatted correctly until I have time to script a detection script, but just use the ticker as it appears on one of those sites and it will both update the price and convert it to your currency.
5.) The scripts will also do all your math for you. I've only been able to test this in artificial conditions, so I know the math works, but not how well. We'll see how this goes.
6.) All page creation for new months and the formatting of those pages will be done by script automatically, or so testing has concluded. There's an option during creation to automatically set it to your timezone and currency if available; if i'ts not, a custom script will run afterward to alter it.
7.) Your individual spreadsheet will contain the following.
A.) Summary - a summary page with a summary of all current information.
B.) MonthName - the current month's page You can put put the Month page in front of Summary or behind it, either way. This is to keep consistency for the scripts, since I set them to run by page name specifically.
C.) MonthName - page for each month with summaries of all investment information within that monht. This may actually end up being a single page of tables, but I'm still testing the scripts for that.
D.) Currency - a page with all currency names, codes, and symbols.
E.) Markets - a page with all market information I've documented so far that someone has used or might.
F.) TransactionLog - all your transactions will be recorded here.
G.) WishList - this is where you can list stocks you want to buy eventually or want to watch, and you can see all relevant information about them in a neat table.
G.) Data - that will have data for my scripts for reference and constants and for future changes to formats. If you're a programmer or scripter, it's my reference page for where I put things and for consistent variable usage, since on my latest I was naming variables hat, shoe, nose, and cat because I couldn't remember what I already used. Which I may keep doing; it's kind of fun to read var nose = cat*mouse.
Basically, I'm going to make this as much like a portfolio as possible. Eventually, I'll build an interface for you to use and you won't need to look directly at the spreadsheet at all, but I didn't have time to do much with that other than learn the outline of how it's done from google apps. Eventually, if I'm right, I should be able to move your buying and selling to your personal interface entirely, but I don't know hwo long it will take to get good at making them, so that will have to wait a month or two.
You and the Transaction Log
Since we can move to autoupdating, I'm stripping it down since some of the information was needed to record the transactions, and since it will do that for us, it's not needed. The new log will have fewer pages and require less information to complete, but will require higher accuracy.
The following fields will still be there, but an explanation of what to do with some of them has been added.
Name: Your googledocs username.
Transaction Type: no changes
Website: so I can continue to build scripts for sites you all use.
Trade Type: now mandatory, it will be set to an amount to make the import/export of transactions faster. I haven't had time to build a script to interpret and change words to numbers; I'll get to that later.
International Purchase: not yet using it, but it will stay for later.
Ticker: this must be in the format of the website you use for transaction you are recording so I can assign you the correct script. If you use multiple sites becuse one doesn't have it, that's fine, that's why the website is there and I can work around that.
Company Name: As long as the ticker is accurate, that's fine. The scripts I wrote can correct it if necessary.
Currency: This is complicated.
This should be the currency that the stock price is in on the site. Don't convert it yourself before writing it in; use the amount and currency as listed on teh site, and your spreadsheet will do the conversion for you when updated. The new version of the transaction log may also do this eventually, but I don't know if I'll have time to get it to do everyone's before March.
There is a set of codes for currency that will be in every individual spreadsheet. in general, the website will say what currency it's in using this code; use that if possible. This isn't mandatory or anything, but preferable; the stock update script after it is entered into your individual spreadsheet will automatically give the price both in the currency on the site and a second field will hold the converted amount in your currency.
However, part of the game is knowing what the codes are, even if you need a reference to check what currency they refer to. Starting in April, using currency codes when recording transactions will be mandatory. Currency codes, unlike exchange names and codes, are ISO compliant and pretty much universal on international websites. Knowing your own currency code by sight will become automatic, as well as knowing by sight that of the other players.
Stock Exchange: This is complicated.
Use the symbol or name of the stock exchange that is listed on the site you use, or use the country if it doesn't list a symbol. If you're not sure what I mean, please email or AIM me; this can be confusing, because every site likes to be special about that. Businessweek calls the Singapore Stock Exchange Singapore, while Yahoo uses SES, while the official symbol is SGX as listed by Wikipedia. As long as it's consistent with the webpage you use, it doesn't matter.
Businessweek is very weird about this, too, but it and yahoo are also pretty good about getting a lot of international markets consistently, so you should be fine. Anything you're not sure about, comment here, AIM/YIM, or email; we can answer any questions on this.
The sections for real time adn automated might change, but I"m not sure how much yet until i've run some testing on how it will interact with your spreadsheet. What I want to have happen is when it is automated, a script will run automatically at the correct time to buy, but that's been a little tricky with multiple market times. That probably won't go into effect until at the earliest, April.
You and the Scripts
The scripts will be housed in everyone's spreadsheets and tweaked around when they go into effect. I've been running these on Seperis World conditions, not Real World, and God knows there will be fail points I haven't seen yet, including the fact that all websites are inconsistent and the way I have them pulling information will have fail points I'll have to write workarounds for.
I'm going to ask at first that you don't make any changes to the scripts themselves but create a copy to make changes to if you're inclined to that sort of thing. It's something I do--well, something I do a lot, come to think--and I encourage anyone to tweak them around as much as they like. And ask if you want help tweaking! If I can't figure it out, I can learn how.
If you'd like to learn about them and how they run, I'd love to go over it with you. They're fairly simple and I've gone back to document them, so anything in there that you'd like to change or tweak is fine. Just do it on separate copies. The Data page has some of the outline of where things are that the scripts are running on and are reference points for the scripts themselves.
To get anyone started who is interested, below is the code for the script to pull prices from Globe and Mail. The ones for Yahoo and Businesweek use almost exactly the same format, just tweaked for individual site.
Currently, they pull the following information by ticker; company name, stock price, stock exchange, stock currency, and current date and time. I've added documentation notes here. Note are behind :::: so you can copy and paste this into your spreadsheet if you want to test it using the ticker format on Globe and Mail after removing anything between :::: and ::::
Er, this is one of the ones with the odd variable names.
function getFinanceYahoo() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet26");
::::this is Googledocs required code for working with spreadsheets. "var sheet" sets it as a variable with the name sheet. SpreadsheetApp is the googledocs application being called. getActiveSpreadsheet is a method, or function, of SpreadsheetApp that indicates I want to work with or get the sheet active so it can be worked on.. getSheetName refers to the name of the sheet; this is why sheets are named. The sheet name is the name of teh sheet I've been using for testing.::::
var ticker=sheet.getRange(10,1).getValue();
::::this is Googledocs required code for indicating a location on the page listed above, Sheet26. sheet is the variable that indicates that entire first string of code, getRange refers to the range of cells I want it to check, teh numbers in parenthesis are teh row number first, then column number, of the cell I want it to check and getValue means I want this to get whatever is listed in that cell. In this case, it is the location of the stock ticker I want to get teh price for. It's at row 10, column 1, or A10.::::
myStock = new finance();
::::this is creating a new instance of the object finance. I had to learn how to create objects in javascript, but it makes it easier to make the script more universal. MyStock is now the name of this instance of the object finance. And also because I'm competitive and wanted to make something as close to Google's own finance app as possible.::::
var chick = myStock.person(ticker);
::::teh variable 'chick' is created to refer to myStock and is used to call the object person and invest it with the ticker symbol. I actually don't know why I needed to create a finance object and a person object to do this, but it didn't work with just one object. Forty hours of testing to figure that out. *sighs*::::
var hat = myStock.getname();
::::variable hat is created to tell myStock to run the script getname, which gets the name of the company.::::
sheet.getRange(11,1).setValue(myStock.name);
::::var ticker showed this code to get a value. I'm telling it to use the infromation in teh "sheet" variable to get a new location to put a value; in this case, 11, 1, or A11, just below the ticker. This will be altered to do it horizontally, but i was testing vertically because it was easier to watch.
Repeat the code below to get price, exchange, etc::::
var yes = myStock.getprice();
sheet.getRange(12,1).setValue(myStock.price);
var dance = myStock.getexchange();
sheet.getRange(13,1).setValue(myStock.exchange);
var dance = myStock.getcurrency();
sheet.getRange(14,1).setValue(myStock.currency);
var dance = myStock.getdatetime();
sheet.getRange(15,1).setValue(myStock.datetime);
}
:::this script creates objects named finance and within finance, creates the person object.::::
function finance() {
this.person=person;
}
::::here is where I say, I understand object creation in C++, Java, and Perl. I get weirded out in javascript adn I don't know why. I get what I'm doing. I don't know why I need two objects. Or I do, but I want it more streamlined so I don't have to use two lines of code to call everything in the first script instead of one like Google finance can do. Which is because GoogleFinance is a program on google already, whereas I am aware I'm creating from scratch, but I feel like a lesser individual or something.
I have issues. This lacks elegance.
Anyway, person holds all the relevant information and runs a separate script for each thing you want to call. I'm goign ot eventually streamline that, but right now, I'm still finding inconsistencies between different pages in each site, so each different item, company, name, exchange, has its' own script.
The rest are the individual scripts to grab data from the page. Feel free to play with and tweak if desired.::::
function person(ticker,name,exchange,currency,datetime,price) {
this.ticker=ticker;
this.name=name;
this.exchange=exchange;
this.currency=currency;
this.price=price
this.datetime=datetime;
this.getname=getname;
this.getprice=getprice;
this.getcurrency=getcurrency;
this.getexchange=getexchange;
this.getdatetime=getdatetime;
}
function getname(ticker) {
var xmlstring=getXML();
var match=xmlstring.match(/
(.*?)<\/h4>/);
var company=match[1];
this.name=company;
}
function getprice(ticker) {
var xmlstring=getXML(xmlstring);
var match=xmlstring.match(/(\n\d.*\n)<\/span>/gi);
var two=match[0].split("\n");
var quote=two[1]
this.price=quote;
}
function getexchange(ticker) {
var xmlstring=getXML();
var match=xmlstring.match(/(.*?)<\/li>/g);
var two=match[0].split(">");
var three=two[1].split("<");
var trade=three[0];
this.exchange=trade;
}
function getcurrency(ticker){
var xmlstring=getXML();
var match=xmlstring.match(/(.*)<\/span>/g);
var two=match[0].split(">");
var three=two[1].split("<");
var money=three[0];
this.currency=money;
}
function getdatetime(ticker){
var xmlstring=getXML();
var match=xmlstring.match(/
var two=match[0].split(">");
var three=two[1].split("<");
var trade=three[0];
this.exchange=trade;
}
function getcurrency(ticker){
var xmlstring=getXML();
var match=xmlstring.match(/(.*)<\/span>/g);
var two=match[0].split(">");
var three=two[1].split("<");
var money=three[0];
this.currency=money;
}
function getdatetime(ticker){
var xmlstring=getXML();
var match=xmlstring.match(/
