Google Answers Logo
View Question
 
Q: Dynamic HTML - Assign value of excel cell to a jscript variable in a web page. ( Answered 5 out of 5 stars,   1 Comment )
Question  
Subject: Dynamic HTML - Assign value of excel cell to a jscript variable in a web page.
Category: Computers > Internet
Asked by: flyingopossum-ga
List Price: $50.00
Posted: 06 Jun 2005 02:02 PDT
Expires: 06 Jul 2005 02:02 PDT
Question ID: 529831
Requirement:
To build a live data feed that runs continuously, at a configurable
interval and passes a value I need:
from   a fixed location on a particular web page
to     a javascript assignment statement in a js file on my web site.

I have already created an excel web query that retrieves the data item
I need from that web page and places it in a fixed location in my
excel spreadsheet.

Phase 1. Now I need to use that value in my excel cell, such as   
7.51    as the right operand in  a javascript assignment statement,
such that the
result is a javascript assignment statement that reads     vname =
7.51 .    This is a dynamic html application and needs to happen at run time.

Phase 2. Next, I need to automate the process so it runs at a
configurable interval continuously. I do have an always connected
(broadband) connection.

Experts only, please.

jim

Request for Question Clarification by leapinglizard-ga on 06 Jun 2005 03:48 PDT
Phase 2 seems fairly easy to do, since one can write a JavaScript loop
that will perform the update at a specified interval.

I'm more concerned about Phase 1, since this can be more or less
difficult depending on the nature of the web page from which the data
is to be extracted. Could you link to the web page here, pointing out
the target data, or at least post a snippet of the HTML source
surrounding the target data? Once you do that, I can start to write
code for you.

leapinglizard

Clarification of Question by flyingopossum-ga on 06 Jun 2005 04:55 PDT
Can you send me an email at sales@thesilverxchange.com? If I have am
email to reply to, it will be far easier to give you what you need, as
I will be able to send attachemtns. I am not trying to defeat
"answers", only trying to make it easier to reply.

We can close the question using the answers system to make it official.

thanks,

jim

Clarification of Question by flyingopossum-ga on 06 Jun 2005 05:31 PDT
Are you doing more than required?

I already have a web query that gets the data I need and places it in
an excel cell. I would like to use that cell as the right operand in a
js assign statement.

Seems to me that if you use the excel sheet I already have, only a
very few lines of code should be required - maybe less than 5 lines. I
do not want code that I have difficulty understanding. I need this to
be maintainable by me. I would rather use the partial solution I
already have as the starting point.

This is not a demand, but a strong request.

Anyway, Here is the source data.

http://www.kitco.com/market/

The data item is in table named NY Spot Price - Row "Silver" Column "Ask"

The value now is 7.51.

jim

Request for Question Clarification by leapinglizard-ga on 06 Jun 2005 07:51 PDT
The rules of this service forbid Researchers from taking up any
communication with Askers outside the confines of the web site, so I'm
afraid we can't correspond by email.

It might be possible to extract the value directly from your
spreadsheet, but I'd have to see the spreadsheet file to say for sure.
Could you put it on a web page and post the link here? (Free web space
is available from GeoCities, among others.)

leapinglizard

Clarification of Question by flyingopossum-ga on 06 Jun 2005 09:29 PDT
I did send you in my last clarification the url and a description of
the location of the data, so you have that now. As far as the excel
data is concerned, you can use any spreadsheet with a numeric cell for
testing. It does not matter for you to have my spreadsheet. A cell is
a cell. I tried to put the spreadsheet on my website, but it was
inactive, in other words, the web query was not operating.

However, it occurs to me that automating the spreadsheet to run
continuously may be a different effort than automating some code that
you may write, so you should recommend the best approach, as you tried
to do in the first place. I did send you a link to the source web site
and location of the data item concerned 2 clarifys ago.

Please let me know if I have muddied the waters.

All I care about is that I will be able  to modify the code, if ever
needed. I was a programmer for 25 years (largely mainframe) so I do
not anticipate much difficulty.

Do what you feel is best. Let me know if you have everything you need now.

thanks, jim

Clarification of Question by flyingopossum-ga on 06 Jun 2005 09:48 PDT
http://www.thesilverxchange.com/book1.xls

cell f17

Clarification of Question by flyingopossum-ga on 06 Jun 2005 09:49 PDT
not f17   it is f7

Request for Question Clarification by leapinglizard-ga on 06 Jun 2005 10:32 PDT
A cell may be just a cell from the perspective of the spreadsheet, but
the JavaScript code has to know where to find the data. The assumption
here is that you intend to view your custom web page on a computer
that has a file with the price data in it. If this is not the case,
JavaScript won't work, since it's strictly a client-side language. In
other words, it executes on the machine of the person viewing the page
rather than on the machine hosting the page.

If your setup meets this requirement, we must now consider how a piece
of JavaScript can extract the price data from the local file. The
general idea is to write some code that opens the file, reads it, and
uses a text pattern to locate and capture the data. As long as the
file is readable and regularly updated in some manner by a separate
process, the whole rig should work fine.

Unfortunately, an Excel spreadsheet does not provide a good basis for
text extraction, since it is stored in a binary file format that
consists mostly of unreadable characters. I just looked over your file
in a text editor, and I can't locate the price data in it. I propose
two solutions to this problem.

1. Forget about the local file and use JavaScript to hook into a Java
installation, presumed present on the client machine, to download the
Kitco page and extract the price data. This is hard because the
JavaScript/Java interaction is flaky and poorly documented.

2. Run a small Python script -- I can do it in fewer than ten lines,
honest -- to extract the price data from the Kitco page and write it
to a text file, from where the JavaScript can easily retrieve it. This
is much easier than #1 above, requiring only a Python installation on
the client. See http://www.python.org/download/ for installation
packages. This would make the JavaScript nice and short, and the
Python code would also be highly readable.

Let me know what you want to do.

leapinglizard

Clarification of Question by flyingopossum-ga on 06 Jun 2005 15:40 PDT
Can you make this thing entirely server-side? I need this to be a
background process that runs on the server and keeps the price data
updated.

I have one js file that has a variale assignment in it and I need that
assignment statement to be using the latest price data as a constant
in the assignment statement. It looks like this:
spotPrice = 7.51; but the constant, 7.51, will be continuously changing.

Can this be done?

Clarification of Question by flyingopossum-ga on 06 Jun 2005 16:07 PDT
I have approached this poorly. I have given you bits and pieces of the
requirement, rather than the whole thing all at once. I apologize for
that.

Let me give you the overall flow now:
Run a process every 5 minutes (interval should be configurable)

Processing steps in the repeating process:
1. grab the latest price from kitco
2. update a js source file on my local machine with a new price
constant in the assignment statement, such that:
spotPrice = 7.51; then 5 min. later, spotPrice = 7.52; then spotPrice = 7.49;
3. upload the updated js file to my web host ftp site.
The 3 steps above must occur every 5 minutes.

---Completely server side---
That's all folks.

Thanks, jim

Request for Question Clarification by leapinglizard-ga on 06 Jun 2005 18:06 PDT
Ahhh... now I understand what you want to do. In some respects it's
easier than injecting the value into a live piece of JavaScript. The
best way to accomplish your project is with a general-purpose
scripting language such as Python. If you install Python on your
server, or if it's already present, I can write the code for you
without too much difficulty. Let me outline the solution.

1. grab the latest price from kitco:  We grab the page using the
urllib module and scan it with a regular expression to extract the
price.

2. update a js source file on my local machine with a new price
constant:  If there is only one occurrence of the statement "spotPrice
= x.xx" in your source file, then we can replace the existing value
with a new value by running a search-and-replace regular expression.
Otherwise, you make a version of your file that has a special nonsense
statement such as "spotPrice = <UPDATE_ME>", and my script makes the
real source file with a valid JS statement.

3. upload the updated js file to my web host ftp site:  There's a
module called ftplib that lets us do just this sort of thing.

I anticipate that the whole Python script would be 20 lines long,
perhaps less. Python is renowned for its readability, so you shouldn't
have much trouble maintaining and updating the code.

If you agree with my plan, please check to see whether Python is
already present on your server. If it isn't, get the package for your
platform from http://www.python.org/download/ and install it. Then, if
you're on Windows, verify that you can run python.exe from a DOS shell
(type Ctrl-Z to exit the interpreter). If you're using a Unix box, the
executable is named python (and type Ctrl-D to exit).

leapinglizard

Clarification of Question by flyingopossum-ga on 06 Jun 2005 22:35 PDT
Sounds good to me. I have just installed the latest python release on my machine. 
I got the >>> prompt.

By the way, I am a generous tipper.

jim

Clarification of Question by flyingopossum-ga on 07 Jun 2005 06:44 PDT
So you can see the result of the value of the jscript variable, view
page www.thesilverxchange.com/bullion02.shtm

The price buttons use the variable as the basis with a markup added to
it to derive my selling price.

My price is always at a fixed markup above the spot price. Now, I am
keeping the spotPrice variable updated manually - a huge
responsibility and a royal pain in the ass.

jim

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 08:29 PDT
Yes, I see what you mean. I have a lunch meeting soon, and I'll get
started on the code after it's over, in two or three hours. You'll
hear from me by this evening.

leapinglizard

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 14:20 PDT
My code is below. To use it, copy the whole shebang into a plain text
file named, say, "price_update.py", then execute

  python.exe price_update.py

in the DOS shell or

  python price_update.py

under Unix. Since Python is a whitespace-sensitive language, make sure
you use a simple ASCII editor -- Notepad in Windows or the cat utility
in Unix -- to copy the file so as to avoid tampering with the
indentation or introducing special characters.

There's a list of constants at the beginning that you'll want to
review and adjust to your needs. Ask for my help if necessary. I've
also added a bunch of comments and some logging output, but the real
work is done by the 18 statements from step 1 to the end of the while
loop. That's 18 or 20 lines, depending on how you want to count the
multi-line regular expression in step 1.

That regular expression is what does the job of extracting the price
from Kitco's website. If the site layout changes, the regex will
probably have to change with it, but I'm always here to help. Note
that I've assumed a setup where you make a template for your
JavaScript source. This template resembles the desired source file,
with the sole difference that it contains a text marker instead of the
real price. My code reads the file, replaces the marker with the
price, and writes out the resulting source to a specified filename.

I've tested the code and it works for me. If anything goes wrong at
your end or if you have any concerns about the code, let me know. Once
you're satisfied that I've completely answered your question, I'll
submit my official answer and wait for your rating.

leapinglizard



#=====begin price_update.py


#--- adjust the following constants as necessary

PRICE_MARKER    = '<SILVER_PRICE>'  # replace this with Kitco price
INPUT_FILENAME  = 'template.js'     # read source from this file
OUTPUT_FILENAME = 'source.js'       # write updated source to this file
FTP_SITE        = 'yourserver.com'  # upload to this FTP site
FTP_USERNAME    = 'user'            # use this FTP account
FTP_PASSWORD    = 'pass'            # password for FTP account
FTP_DIRECTORY   = '.'               # target directory in FTP account
UPDATE_SECONDS  = 10                # rerun main loop at this interval


import sys, os, string, re          # load basic modules
import time, urllib, ftplib         # fancier modules


#--- main loop begins below

while 1:                            # display time for logging purposes
    sys.stderr.write(time.strftime('%H:%M:%S %A %Y.%m.%d')+'\n')


#--- step 1: extract silver price from Kitco website

                                    # this regex matches the price
                                    #   (if the page design is revamped,
                                    #   we'll probably need a new regex)
    pattern = re.compile('<td align="left"><p>[^<]*<a href="/charts/' +
                         'livesilver.*$\s*<td><p>.*$\s*<td><p>.*$\s*' +
                         '<td><p>.*$\s*<td><p>([^<]+)</td>', re.M)

    page = urllib.urlopen('http://www.kitco.com/market/').read()
    match = pattern.search(page)    # download page and run regex
    if not match:                   # print error if no match
        sys.stderr.write('error: can\'t find price\n')
    else:
        price = match.group(1).strip()
        sys.stderr.write('found price: '+price+'\n')


#--- step 2: read in file, replace marker with price, write out file

    source = re.sub(PRICE_MARKER, price, open(INPUT_FILENAME).read())
    fout = open(OUTPUT_FILENAME, 'w')
    fout.write(source)
    fout.close()


#--- step 3: upload to FTP account

    ftp = ftplib.FTP(FTP_SITE, FTP_USERNAME, FTP_PASSWORD)
    ftp.cwd(FTP_DIRECTORY)
    ftp.storbinary('STOR '+OUTPUT_FILENAME, open(OUTPUT_FILENAME))
    ftp.quit()


#--- now we wait

    sys.stderr.write('waiting for '+str(UPDATE_SECONDS)+' seconds\n\n')
    time.sleep(UPDATE_SECONDS)      # wait for specified interval


#--- main loop ends above


#=====end price_update.py

Clarification of Question by flyingopossum-ga on 07 Jun 2005 15:16 PDT
I was not sure of ftp site name syntax. The name is
www.thesilverxchange.com. It is failing to log into ftp.

Also, I want the input and output to be in my website directory,
Python would not read it there; it would only read it if I put it in
the python directory.

My Paramaters:
PRICE_MARKER    = '<SILVER_PRICE>'               # replace this with Kitco price
INPUT_FILENAME  = 'template.js'                  # read source from this file
OUTPUT_FILENAME = '\docs\websites\spotPrice.inc' # write updated source
FTP_SITE        = 'thesilverxchange.com'         # upload to this FTP site
FTP_USERNAME    = 'thesilverxchange2'            # use this FTP account
FTP_PASSWORD    = 'XXXXXXXX'                     # password for FTP account
FTP_DIRECTORY   = 'silverxchange2'               # target directory in FTP 
UPDATE_SECONDS  = 10                             # rerun main loop interval

Output
18:08:26 Tuesday 2005.06.07
found price: 7.43
Traceback (most recent call last):
  File "price_update.py", line 54, in ?
    ftp = ftplib.FTP(FTP_SITE, FTP_USERNAME, FTP_PASSWORD)
  File "C:\Python24\lib\ftplib.py", line 108, in __init__
    if user: self.login(user, passwd, acct)
  File "C:\Python24\lib\ftplib.py", line 362, in login
    if resp[0] == '3': resp = self.sendcmd('PASS ' + passwd)
  File "C:\Python24\lib\ftplib.py", line 241, in sendcmd
    return self.getresp()
  File "C:\Python24\lib\ftplib.py", line 214, in getresp
    raise error_perm, resp
ftplib.error_perm: 530 User thesilverxchange2 cannot log in.

Clarification of Question by flyingopossum-ga on 07 Jun 2005 15:35 PDT
Forget prior message - Major progress

It appears to be working if I put everything in the python directory.
I have not fully verified the update and upload, but it appears to be
working.

I need to get a little tighter control over the data to adequately
compare the before image against the after image.

But it looks good.

I just need to redirect the python script to read and write to other
directories on my machine.

jim

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 15:42 PDT
Are you sure there's an ftp server at that hostname and that it's
currently active? I don't detect an ftp server at either
www.silverxchange.com or silverxchange.com . Once you find a hostname
that lets you ftp from the command line or from a utility such as
putty, that hostname should work with the script.

As for the directory problem, I'm surprised but not dismayed. The
behavior under Unix, which is what I'm using, is to assume that the
current working directory is the one where you execute the script.
It's not the directory where the Python interpreter is located, nor
the directory where the script is located, but the directory where you
are when you run the script. So, for instance, if I have a file called
test.py consisting of the following two lines --

import os    
print os.getcwd()

-- and I put it into a directory called foo, then step into the
directory above and run the script, the result should be the name of
the directory above foo. Please try this experiment and tell me what
the result is. If it's the same as mine, then just step into the
directory with the source file before you execute the script.
Otherwise, I'll adjust the code by adding a constant in which you
specify the full pathname of the directory containing the source file.

leapinglizard

Clarification of Question by flyingopossum-ga on 07 Jun 2005 15:43 PDT
Verified the update and the upload!

Ready to close question as soon as you tell me how to redirect input
and output to other directories.

I want to keep in touch with you.

You have a $25.00 tip coming to you.

jim

Clarification of Question by flyingopossum-ga on 07 Jun 2005 15:49 PDT
One more small thing.

Can I get this to run in the background without a dos shell and
redirect the log to a text file?

I do not like having a dos window open all the time.

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 15:51 PDT
If you're still having trouble with the directories, use the slightly
modified code below. You'll see that there's a new constant called
LOCAL_DIRECTORY for you to set. Let me know if it does what you
wanted.

leapinglizard



#=====begin price_update.py


#--- adjust the following constants as necessary

PRICE_MARKER   =  '<SILVER_PRICE>'  # replace this with Kitco price
LOCAL_DIRECTORY = 'C:/source/here'  # source files in this directory
INPUT_FILENAME  = 'test.in.js'      # read source from this file
OUTPUT_FILENAME = 'test.out.js'     # write updated source to this file
FTP_SITE        = 'yourserver.com'  # upload to this FTP site
FTP_USERNAME    = 'user'            # use this FTP account
FTP_PASSWORD    = 'pass'            # password for FTP account
FTP_DIRECTORY   = '.'               # target directory in FTP account
UPDATE_SECONDS  = 10                # rerun main loop at this interval


import sys, os, string, re          # load basic modules
import time, urllib, ftplib         # fancier modules

os.chdir(LOCAL_DIRECTORY)           # go to directory with source files


#--- main loop begins below

while 1:                            # display time for logging purposes
    sys.stderr.write(time.strftime('%H:%M:%S %A %Y.%m.%d')+'\n')


#--- step 1: extract silver price from Kitco website

                                    # this regex matches the price
                                    #   (if the page design is revamped,
                                    #   we'll probably need a new regex)
    pattern = re.compile('<td align="left"><p>[^<]*<a href="/charts/' +
                         'livesilver.*$\s*<td><p>.*$\s*<td><p>.*$\s*' +
                         '<td><p>.*$\s*<td><p>([^<]+)</td>', re.M)

    page = urllib.urlopen('http://www.kitco.com/market/').read()
    match = pattern.search(page)    # download page and run regex
    if not match:                   # print error if no match
        sys.stderr.write('error: can\'t find price\n')
    else:
        price = match.group(1).strip()
        sys.stderr.write('found price: '+price+'\n')


#--- step 2: read in file, replace marker with price, write out file

    source = re.sub(PRICE_MARKER, price, open(INPUT_FILENAME).read())
    fout = open(OUTPUT_FILENAME, 'w')
    fout.write(source)
    fout.close()


#--- step 3: upload to FTP account

    ftp = ftplib.FTP(FTP_SITE, FTP_USERNAME, FTP_PASSWORD)
    ftp.cwd(FTP_DIRECTORY)
    ftp.storbinary('STOR '+OUTPUT_FILENAME, open(OUTPUT_FILENAME))
    ftp.quit()


#--- now we wait

    sys.stderr.write('waiting for '+str(UPDATE_SECONDS)+' seconds\n\n')
    time.sleep(UPDATE_SECONDS)      # wait for specified interval


#--- main loop ends above


#=====end price_update.py

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 16:00 PDT
I know how to run programs in the background under Unix, but I haven't
used Windows enough to know offhand how it's done in that environment.
I'm just guessing here, but one possibility might be to schedule the
DOS command using the built-in task scheduler -- there is such a
thing, right? -- and once the scheduler has launched the script, it'll
run quietly in the background. You won't get the logging messages in
this case, so you won't know if the script has died for some reason,
but I could modify the script to write the log to a file.

Or I could modify the script so that it just runs the main loop once,
and then you can use the task scheduler to execute it every five
minutes.

leapinglizard

Clarification of Question by flyingopossum-ga on 07 Jun 2005 16:04 PDT
the windows task scheduler sucks. It is useless.

Forget the background request, but please do write the log to a file
IN ADDITION to the console.

I am about to test the directory changes.

Will the source directory also work for the output?

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 16:06 PDT
It seems you can use the Advanced Properties of the Windows XP Task Scheduler.

http://www.iopus.com/guides/winscheduler.htm

If you're using Windows XP, why don't you try that? Configure it to
start the script immediately, and then check to see whether the source
file is created. You can also set the update interval to something
low, such as 5 seconds, and repeatedly delete the source file to see
if it comes back each time.

leapinglizard

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 16:07 PDT
The script should do all its work in the directory you specify. Can't
you schedule the DOS command using the Advanced Properties pane of the
Task Scheduler?

leapinglizard

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 16:15 PDT
Latest version below. This one has a constant named LOG_FILENAME. By
the way, I think you have to use a double backslash in a Python string
for each backslash in a DOS directory name. Maybe you've detected this
problem already.

leapinglizard



#=====begin price_update.py


#--- adjust the following constants as necessary

PRICE_MARKER   =  '<SILVER_PRICE>'  # replace this with Kitco price
LOCAL_DIRECTORY = 'C:\\source\\dir' # source files in this directory
INPUT_FILENAME  = 'test.in.js'      # read source from this file
OUTPUT_FILENAME = 'test.out.js'     # write updated source to this file
LOG_FILENAME    = 'silver.log'      # write log messages to this file
FTP_SITE        = 'ftp.xyz.com'     # upload to this FTP site
FTP_USERNAME    = 'user'            # use this FTP account
FTP_PASSWORD    = 'pass'            # password for FTP account
FTP_DIRECTORY   = '.'               # target directory in FTP account
UPDATE_SECONDS  = 10                # rerun main loop at this interval


import sys, os, string, re          # load basic modules
import time, urllib, ftplib         # fancier modules

os.chdir(LOCAL_DIRECTORY)           # go to directory with source files

def log(s):
    sys.stderr.write(s)
    open(LOG_FILENAME, 'a').write(s)


#--- main loop begins below

while 1:                            # display time for logging purposes
    log(time.strftime('%H:%M:%S %A %Y.%m.%d')+'\n')


#--- step 1: extract silver price from Kitco website

                                    # this regex matches the price
                                    #   (if the page design is revamped,
                                    #   we'll probably need a new regex)
    pattern = re.compile('<td align="left"><p>[^<]*<a href="/charts/' +
                         'livesilver.*$\s*<td><p>.*$\s*<td><p>.*$\s*' +
                         '<td><p>.*$\s*<td><p>([^<]+)</td>', re.M)

    page = urllib.urlopen('http://www.kitco.com/market/').read()
    match = pattern.search(page)    # download page and run regex
    if not match:                   # print error if no match
        log('error: can\'t find price\n')
    else:
        price = match.group(1).strip()
        log('found price: '+price+'\n')


#--- step 2: read in file, replace marker with price, write out file

    source = re.sub(PRICE_MARKER, price, open(INPUT_FILENAME).read())
    fout = open(OUTPUT_FILENAME, 'w')
    fout.write(source)
    fout.close()


#--- step 3: upload to FTP account

    ftp = ftplib.FTP(FTP_SITE, FTP_USERNAME, FTP_PASSWORD)
    ftp.cwd(FTP_DIRECTORY)
    ftp.storbinary('STOR '+OUTPUT_FILENAME, open(OUTPUT_FILENAME))
    ftp.quit()


#--- now we wait

    log('waiting for '+str(UPDATE_SECONDS)+' seconds\n\n')
    time.sleep(UPDATE_SECONDS)      # wait for specified interval


#--- main loop ends above


#=====end price_update.py

Clarification of Question by flyingopossum-ga on 07 Jun 2005 16:35 PDT
Please give mne a few hours to get back to you. I have been working
since 2:00 AM and need to take a Cartoon break.

I will answer tonight hopefully with a conclusive reply.

You have been truly teriffic.

jim

Request for Question Clarification by leapinglizard-ga on 07 Jun 2005 16:48 PDT
I have to go to bed in an hour or two and then I'm busy in the morning
with rowing and squash, but I'll get around to your message
eventually. We'll be in touch by tomorrow at the latest.

leapinglizard

Clarification of Question by flyingopossum-ga on 08 Jun 2005 06:36 PDT
I tested the latest version with new variables. It all works.

I discovered a problem in the script. It aborts unexpectedly and
unpredictably, at entirely random intervals. I have yet to see it run
for more than 30 minutes without aborting.

However, I think I created a very elegant fix for this problem. I did
not debug the script - that seems like it would be very difficult,
especially for someone who does not know python, but, here's my fix:

I cretaed a batch file containing two statements:

Price.bat
---------
rem begin
price_update.py
call price.bat
rem end

That is it. when the script aborts, control passes to the call
statement which simply start itself up again. I have tested it by
cancelling the process and watching the batch file start itself up
again, repeatedly. It seems to be highly reliable if continuous
ececution is the objective, which it is.

Of course I would be interested if you think you could find the cause
of the aborting. That is, if you think it is worth the time to
research it. Maybe my fix makes it unnecessary to find the problem?
What do you think?

What do you think of my fix? If you agree that this fix is correct,
then we can close the question.

I am very pleased

jim

Request for Question Clarification by leapinglizard-ga on 08 Jun 2005 09:22 PDT
That's a reasonable approach. There are ways to solve the problem
within a script, such as by starting a new thread for each iteration
of the task, but in the end they're equivalent to your solution. I'm
still interested in finding the bug, and I'll do my own testing to see
if I can track it down. I'll go ahead now and post an official answer
since you seem to be pleased with the results. I'm still here to
squash any crippling bugs that may arise in future.

leapinglizard
Answer  
Subject: Dynamic HTML - Assign value of excel cell to a jscript variable in a web page
Answered By: leapinglizard-ga on 08 Jun 2005 09:25 PDT
Rated:5 out of 5 stars
 
I have enjoyed the challenge of working on this question. If you have
similar scripting problems in future that you'd like me to help with,
you can mention my name in the subject line so that I'll find the
question easily.

leapinglizard

Clarification of Answer by leapinglizard-ga on 08 Jun 2005 09:44 PDT
Here's a more robust version of the script that should crash less
often, if at all. Please check the log after a few hours and report
the error messages to me -- just search for lines that contain the
string "failed:". This will help me trace the cause of the irregular
crashing you've observed.

leapinglizard


#=====begin price_update.py


#--- adjust the following constants as necessary

PRICE_MARKER   =  '<SILVER_PRICE>'  # replace this with Kitco price
LOCAL_DIRECTORY = '.'   # source files in this directory
INPUT_FILENAME  = 'test.in.js'      # read source from this file
OUTPUT_FILENAME = 'test.out.js'     # write updated source to this file
LOG_FILENAME    = 'silver.log'      # write log messages to this file
FTP_SITE        = 'yoursite.com'    # upload to this FTP site
FTP_USERNAME    = 'user'            # use this FTP account
FTP_PASSWORD    = 'pass'            # password for FTP account
FTP_DIRECTORY   = '.'               # target directory in FTP account
UPDATE_SECONDS  = 15                # rerun main loop at this interval


import sys, os, string, re          # load basic modules
import time, urllib, ftplib         # fancier modules

os.chdir(LOCAL_DIRECTORY)           # go to directory with source files

def log(s):
    try:
        sys.stderr.write(s)
        open(LOG_FILENAME, 'a').write(s)
    except Exception, exc:
        log('logging failed: '+str(exc)+'\n')


#--- main loop begins below

while 1:                            # display time for logging purposes
    log(time.strftime('%H:%M:%S %A %Y.%m.%d')+'\n')


#--- step 1: extract silver price from Kitco website

                                    # this regex matches the price
                                    #   (if the page design is revamped,
                                    #   we'll probably need a new regex)
    try:
        pattern = re.compile('<td align="left"><p>[^<]*<a href="/charts/' +
                             'livesilver.*$\s*<td><p>.*$\s*<td><p>.*$\s*' +
                             '<td><p>.*$\s*<td><p>([^<]+)</td>', re.M)

        page = urllib.urlopen('http://www.kitco.com/market/').read()
        match = pattern.search(page)    # download page and run regex
        if not match:                   # print error if no match
            log('error: can\'t find price\n')
        else:
            price = match.group(1).strip()
            log('found price: '+price+'\n')
    except Exception, exc:
        log('step 1 failed: '+str(exc)+'\n')


    #--- step 2: read in file, replace marker with price, write out file

    try:
        source = re.sub(PRICE_MARKER, price, open(INPUT_FILENAME).read())
        fout = open(OUTPUT_FILENAME, 'w')
        fout.write(source)
        fout.close()
    except Exception, exc:
        log('step 2 failed: '+str(exc)+'\n')


    #--- step 3: upload to FTP account

    try:
        ftp = ftplib.FTP(FTP_SITE, FTP_USERNAME, FTP_PASSWORD)
        ftp.cwd(FTP_DIRECTORY)
        ftp.storbinary('STOR '+OUTPUT_FILENAME, open(OUTPUT_FILENAME))
        ftp.quit()
    except Exception, exc:
        log('step 3 failed: '+str(exc)+'\n')


#--- now we wait

    log('waiting for '+str(UPDATE_SECONDS)+' seconds\n\n')
    time.sleep(UPDATE_SECONDS)      # wait for specified interval


#--- main loop ends above


#=====end price_update.py
flyingopossum-ga rated this answer:5 out of 5 stars and gave an additional tip of: $25.00
Superior researcher. Competence beyond reproach. Friendly, helpful and
professional attitude - willing to go the extra mile to satisfy. Could
not be more pleased!

Comments  
Subject: Re: Dynamic HTML - Assign value of excel cell to a jscript variable in a web pag
From: leapinglizard-ga on 08 Jun 2005 09:58 PDT
 
Thank you for the rating, the tip, and the kind words.

leapinglizard

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy